Search code examples
google-sheetsgoogle-apps-script

Google Sheet - need time stamp for the cell row if certain columns are updated


Question 1 I am currently using below app script for time stamp in column 32

function onEdit(e) {
  const sheetNames = ["Acads Progress"]; 
  // Please set your sheet name, In this script, if you want to use the script with multiple sheets you want, please modify const sheetNames = ["Sheet1"]; like const sheetNames = ["Sheet1", "Sheet3",,,];..

  const { range } = e;
  const sheet = range.getSheet();
  if (!sheetNames.includes(sheet.getSheetName()) || range.columnStart == 32) return;
  sheet.getRange(range.rowStart, 32, range.rowEnd - range.rowStart + 1).setValue(new Date());
}

what i want is timestamps only if (f j l m s t u w) columns cell are updated

question 2 also i want is when column U - cell is updated to "drop-out" there should be final timestamp until changed again to any other

question 2 is not that important right now i can find an alternative method to it question 1 is important


Solution

  • function onEdit(e) {
      const sh = e.range.getSheet();
      const nidx = ["Acads Progress"].indexOf(sh.getName());//adjust desired sheet names as required
      const cidx = [6,10,12,13,20,21,22].indexOf(e.range.columnStart);//adjust columns as required
      if (~nidx && ~cidx) {
        sh.getRange(e.range.rowStart, 32, e.range.rowEnd - e.range.rowStart + 1).setValue(new Date());
      }
    }