Apps Script Automatic timestamp when a tab gets updated

Question

So I'm trying to create a script that can apply to several tabs in my spreadsheet file and will automatically update the timestamp in each individual tab when something in that tab is updated/changed. The cell that I want the timestamp in is different in each tab. I thought I had it working but after not touching the file for two days and returning to it, I found that it's no longer updating. Can anyone take a look and see where the issue is? Also, any idea why it would have been working for two days and then stopped working without any edits to the script? I'm new to java so appreciate any help. Here's the current script I'm using:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  if(ss.getName()=='Sheet 1') {e.source.getSheetByName("Sheet 1")
    ss.getRange("F3").setValue(new Date())}
  if(ss.getName()=='Sheet 2') {e.source.getSheetByName("Sheet 2")
    ss.getRange("L3").setValue(new Date())}
  if(ss.getName()=='Sheet 3') {e.source.getSheetByName("Sheet 3")
    ss.getRange("K4").setValue(new Date())}
  if(ss.getName()=='Sheet 4') {e.source.getSheetByName("Sheet 4")
    ss.getRange("K4").setValue(new Date())}
}

Is there a different way I should be approaching the script for this?

Answer 1

I suspect you are looking for Sheet 1 etc. with a space in it, when the default Sheets naming convention is Sheet1 without a space.

Try:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  if (ss.getName() == "Sheet1") {
    ss.getRange("F3").setValue(new Date());
  }
  if (ss.getName() == "Sheet2") {
    ss.getRange("L3").setValue(new Date());
  }
  if (ss.getName() == "Sheet3") {
    ss.getRange("K4").setValue(new Date());
  }
  if (ss.getName() == "Sheet4") {
    ss.getRange("K4").setValue(new Date());
  }
}

Additionally, there are some code-style improvements you can make:

• e.source.getSheetByName("Sheet X") is not being used, you can remove that entirely.
• You can set date to a variable, e.g. const date = new Date()
• Use switch/case to improve readability
• Don't use var unless you absolutely have to; use let for mutable variables, or in this case, const because they do not mutate.