Apps script to update timestamp in one cell on each tab in a google sheet upon any edit of that tab-CodePudding

I have a third party updating a google sheet for me and would like a simple script showing a timestamp in cell B1 of each tab of the script upon any edit of that tab. So if Sheet1 was edited in any way, cell B1 of Sheet1 would update the timestamp. Same with Sheet2. I've got a script that works for Sheet2, but not for Sheet1. If I delete the second function, it works for Sheet1. I'm sure I'm doing this wrong and would appreciate any help. Here's what I have now:

function onEdit() {
  SpreadsheetApp.getActive().getSheetByName("Sheet1").getRange("B1").setValue(new Date());
}

function onEdit() {
  SpreadsheetApp.getActive().getSheetByName("Sheet2").getRange("B1").setValue(new Date());
}

CodePudding user response：

Are you aware of the fact that an onEdit will always require a manual edit in the spreadsheet? Updates to the sheet by some third party app will not be considered as an edit, hence the timestamp will not be updated.

If you are manually editing the spreadsheet (sheets) you can try

function onEdit(e) {
e.source.getActiveSheet().getRange('B1').setValue(new Date());
}

CodePudding user response：

A single Google Apps Script project can't have to onEdit functions due to how JavaScript works.

The following code example use if statements and the on edit event object to add the timestamp only to the sheet that was edited

function onEdit(e) {
  const name = e.range.getSheet().getName();
  if(name === 'Sheet1') e.source.getSheetByName("Sheet1").getRange("B1").setValue(new Date());
  if(name === 'Sheet2') e.source.getSheetByName("Sheet2").getRange("B1").setValue(new Date());
}

CodePudding user response：

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  const shts = ["Sheet1","Sheet2"];//Add the sheet names you wish it to work on
  if(~shts.indexOf(sh.getName())) {
    sh.getRange("B1").setValue(new Date());
  }
}