Google Sheet - Add Timestamp When Any Cell on Specific Sheet is Modified

Question

I have a Google Sheet file that has many tabs (sheets) in it. What I am looking to do is place a timestamp when any cell on a specific page is updated or modified.

Sheet name = "Data-Dashboard"
Cell to place timestamp on "Data-Dashboard" is B1.

I wonder if having the timestamp on the sheet that is updating the timestamp on edit if it will create a loop so then I can exclude row 1 and just monitor the remaining rows and columns.

Here is my starting point:

function onEdit(e) {
  var col = e.range.getColumn();
   if(col == 1, 2, 3, 4, 5, 6) {
    e.source.getActiveSheet().getRange(1,2).setValue(new Date())

That puts the timestamp in the correct place and works when I modify anything in rows A-F. But it applies to all sheets, I need to limit it to a single sheet. Any help would be appreciated!

Answer 1

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  var col = e.range.getColumn();
  if (sh.getName() == "Your Sheet Name" && e.range.columnStart < 7 && e.range.rowStart > 1) {
    sh.getRange(1, 2).setValue(new Date());
  }
}

Answer 2

From the question

What I am looking to do is place a timestamp when any cell on a specific page is updated or modified.

Sheet name = "Data-Dashboard"
Cell to place timestamp on "Data-Dashboard" is B1.

The onEdit trigger is triggered when any edit is done through the Google Sheets UI, to limit the writing of the timestamp to only do it when the certain sheet is edited, then you should add a conditional statement, i.e. an if statement.

function onEdit(e){
  /* Using a conditional operator to assign the active sheet 
     when this function is ran without providing an event object, i.e. 
     when running from the script editor */
  const sheet = e ? e.range.getSheet() : SpreadsheetApp.getActiveSheet(); 
  if(sheet.getName() === 'Data-Dashboard'){
    sheet.getRange('B1').setValue(new Date();
  }
}

Regarding your starting point and that it's working when columns A-F are edited, it's worthy to note while all the parts are evaluated, only the last part is considered as the condition of the if statement, in this case 6, which is a truthy value, meaning that always will be true. The following version includes the condition to log the timestamp only when columns A to F from Data-Dashboard sheets are edited.

function onEdit(e){
  /* Using a conditional operator to assign the active sheet 
     when this function is ran without providing an event object, i.e. 
     when running from the script editor */
  const sheet = e ? e.range.getSheet() : SpreadsheetApp.getActiveSheet(); 
  if(sheet.getName() === 'Data-Dashboard' && e.range.columnStart <= 6){
    sheet.getRange('B1').setValue(new Date();
  }
}

References

• comma operator

Related

• How do I restrict a trigger to a specific sheet/tab?

Answer 3

You can actually do this using just google sheets...I got it off a youtube video.

Basically =ifs(A1<>true,"",G1<>"",G1,1*1,now()) Now everytime you check the check box in A1 it will time stamp the change in G1

It prompts a circular dependency error so just turn that on (files-->settings-->calculation and set to 1. It's awesome...I use it for time stamping my bills/todo list.

Screenshot