How do I get cells to automatically static timestamp on one sheet when a drop down is changed on dif-CodePudding

I am building a client tracker w/ several functionalities. One sheet ('Current Clients') has all the clients listed by row with columns to capture different data about each client(i.e. point person, origin of the contact, where they are in the client pipeline or "stage" and "status").

On 'Current Clients' Column C is Client Name and Column F is a dependent drop-down with options for each status (aka sub-stage or phase) of the client pipeline.

In sheet 'Client Timeline' sheet I am trying to create a dynamic, visual tracker that logs static timestamps for every time the drop-downs in 'Current Clients'!F2:F (aka Status in client pipeline) are changed.

These timestamps would then trigger cells on the dynamic visual calendar (columns U-BJ on 'Client Timeline' sheet) so that the end result is a gantt-like display of the dates for the beginning and end of each status in the client pipeline.

So far, I have 'Client Timeline'C:C set to filter the names of the clients (from 'Current Clients'C:C) and I have columns F-T on 'Client Timeline' labeled and corresponding to the statuses from the drop-down in 'Current Clients' column F that I need to record timestamps for.

I am feeling reasonably confident in making the dynamic visual calendar section function as I've used this template for gantt charts before.

I need help with writing GAS (or a function) to auto-record static timestamps in the corresponding row and column on 'Client Timeline' sheet every time a clients status is changed in the Status column (F) on the 'Current Clients' sheets.

I have watched several videos and looked at scripts that record static timestamps in a cell when another cell is changes on the same sheet. But I need it to happen between two sheets and I also need it to apply to every cell in the range F2:T34 on the 'Client Timeline' sheet.

I know this is complicated, but I'm hoping someone can help me out!

Here is my template, please make a copy of your own in order to make edit. Please also disregard hidden and protected parts of the spreadsheet, they pertain to other functionalities that I do not need help with at this time. https://docs.google.com/spreadsheets/d/1kf-fVJ3OIir2Hm1nGuTt84fp99G_F0KdTSnuZFM7BJg/edit?usp=sharing

Thank you in advance!

CodePudding user response：

Time Stamp on one sheet for change in another

function onEdit(e) {
  const sh = e.range.getSheet();
  const validatedSelectionColumn = 1;
  if(sh.getName() == 'editted sheet name' && e.range.columnStart == validatedSelectionColumn) {
    e.source.getSheetByName(" timestamp sheet name").getRange('range in a1notation for timestamp').setValue(new Date());
  }
}

I just did it this way and it works:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == 'Sheet0' && e.range.columnStart == 1) {
    e.source.getSheetByName("Sheet1").getRange('A1').setValue(new Date());
  }
}

You could also do it this way, so that you now you also no what cell was changed

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == 'Sheet0') {
    e.source.getSheetByName("Sheet1").getRange(e.range.rowStart,e.range.columnStart).setValue(new Date());
  }
}

You could also put the edits in the same range by storing them in the notes:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == 'Sheet0') {
    let msg = `Value: ${e.value}\nRange: ${e.range.getA1Notation()}\nTimeStamp: ${new Date()}`; 
    sh.getRange(e.range.rowStart,e.range.columnStart).setNote(msg);
  }
}

This builds a log in notes by appending each edit to the notes and it also provides a toast to let you know that it's completed.

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == 'Sheet0') {
    let old = e.range.getNote();
    let msg = `${old}\nValue: ${e.value}\nRange: ${e.range.getA1Notation()}\nTimeStamp: ${new Date()}` ; 
    e.range.setNote(msg);
    e.source.toast('Note Set');
  }
}

CodePudding user response：

Workflow:

Get information on which cell was edited via event objects (which column, row, sheet, etc.).
Check that the edited cell is in Current Clients, in column F, and not in the first row.
Get the edited cell value to get the edited status, and the corresponding client in column C of the edited row.
Check the row and column indexes of the corresponding status and client in the target sheet.
Set the timestamp on the corresponding cell (see Date).

Code sample:

const SOURCE_SHEET_NAME = "Current Clients";
const TARGET_SHEET_NAME = "Client Timeline";
const SOURCE_STATUS_COL = 6; // Column F
const SOURCE_CLIENT_COL = 3; // Column C
const TARGET_STATUS_ROW = 6;
const TARGET_CLIENT_COL = 3; // Column C

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const colIndex = range.getColumn();
  const rowIndex = range.getRow();
  if (sheet.getName() === SOURCE_SHEET_NAME && colIndex === SOURCE_STATUS_COL && rowIndex > 1) {
    const sourceStatus = range.getValue();
    const sourceClient = sheet.getRange(rowIndex, SOURCE_CLIENT_COL).getValue(); // Column C
    const ss = e.source;
    const targetSheet = ss.getSheetByName(TARGET_SHEET_NAME);
    const targetSheetValues = targetSheet.getDataRange().getValues();
    const targetRow = targetSheetValues.findIndex(row => row[TARGET_CLIENT_COL-1] === sourceClient)   1;
    const targetCol = targetSheetValues[TARGET_STATUS_ROW-1].indexOf(sourceStatus)   1;
    const now = new Date();
    targetSheet.getRange(targetRow, targetCol).setValue(now);
  }
}

Note:

If you have another onEdit function in your script, you'll have to integrate both into a single function (for example, rename this sample's function to onEdit1 and call it inside your main onEdit function).
Consider removing the formulas you have set in the target range so that they don't mess with the script trying to write the timestamps.