Question 252435 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Apps Script Automatic timestamp when a tab gets updated

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?

Tags:

2

Answers


  1. 0

    Your script has a couple of inefficiencies and unnecessary lines of code, but let’s first address the main issues:

    Variable Scope: You are using ss as the active sheet but also trying to get the sheet by name via e.source.getSheetByName(...), which is redundant.

    Event Object: You’re not using the event object e effectively, which provides you with all the details related to the edit event.

    Here is a different (and hopefully more optimized version) of the script:

    function onEdit(e) {
  var sheet = e.source.getSheetByName(e.range.getSheet().getName());
  var sheetName = sheet.getName();
  
  // Use a switch statement to handle different sheet names
  switch(sheetName) {
    case 'Sheet 1':
      sheet.getRange('F3').setValue(new Date());
      break;
    case 'Sheet 2':
      sheet.getRange('L3').setValue(new Date());
      break;
    case 'Sheet 3':
      sheet.getRange('K4').setValue(new Date());
      break;
    case 'Sheet 4':
      sheet.getRange('K4').setValue(new Date());
      break;
  }
}
    Login or Signup to reply.
  2. 0

    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.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search