How to create timestamp on multiple columns in GSheet

Question

Am havong some trouble creating a script to automate timestamp on GSheet. I have 2 columns - AD & AE, that I want it to have the date timestamp for when column L and AA are updated. Have tried the following script but doesn't seem to work.

function onEdit(e) {

  addTimestamp(e);

}

function addTimestamp(e){

  var startRow = 3;
  var targetColumn = 12;
  var targetColumn2 = 27;
  var ws = "Brand List";

  var row = e.range.getRow();
  var col = e.range.getColumn();
  
  if (col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws) {

    var currentDate = new Date();
    e.source.getActiveSheet().getRange(row,30).setValue(currentDate)

  }

  if (col === targetColumn2 && row >= startRow && e.source.getActiveSheet().getName() === ws) {

    var currentDate = new Date();
    e.source.getActiveSheet().getRange(row,31).setValue(currentDate)

  }
}

mshcruz · Accepted Answer · 2021-07-12T07:24:39.043

1

Your script is working fine, but it seems that you're getting an error saying TypeError: Cannot read property 'range' of undefined.

This happens because the script is using a trigger to execute the function onEdit() whenever the spreadsheet is edited. When executed, this trigger will pass an event object (e) as an argument to the function, which includes data such as the cell that was edited.

However, this event object is only passed if the sheet is edited, which doesn't happen if you run the function from the script editor. So, to make it work, you need to edit the sheet as you'd do when using the script.

If you actually want to test it separately, you'd need to explicitly create this event object and pass it to the onEdit() function: How can I test a trigger function in GAS?

edited Jul 12 '21 at 07:24

answered Jul 12 '21 at 04:56

mshcruz

1,967
2
12
12

Thanks but what i'm trying to do is column AD to have the timestamp when column L is filled, and column AE to have the timestamp column AA is filled, are you able to help with that too? – Qc20 Jul 12 '21 at 06:41
I tested the script you posted and it is doing as you say. Are you getting any errors from it? – mshcruz Jul 12 '21 at 06:51
Yup it gave an error message "Error, TypeError: Cannot read property 'range' of undefined" – Qc20 Jul 12 '21 at 07:02
That happens if you try to run a script from the editor, because the trigger didn't fire, so you don't have the event `e`. For scripts like this, you need to execute them from the spreadsheet directly. – mshcruz Jul 12 '21 at 07:05
Pretty new at this, do you mind explaining what do you mean by execute from spreadsheet directly? Even after saving the script, when I update column L or AA, nothing happens. – Qc20 Jul 12 '21 at 07:10
Sure, I changed my answer to include that explanation. – mshcruz Jul 12 '21 at 07:24
Great thanks! Managed to get it to work now with some minor edits. – Qc20 Jul 12 '21 at 08:57

score 0 · Answer 2 · answered Jul 12 '21 at 08:58

For those interested in the script used.

   function onEdit(e) {

  addTimestamp(e);
  addTimestamp2(e);

}

function addTimestamp(e){

  var startRow = 3;
  var targetColumn = 12;
  var targetColumn2 = 27;
  var ws = "Brand List";

  var row = e.range.getRow();
  var col = e.range.getColumn();

if (col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws) {

    var currentDate = new Date();
    e.source.getActiveSheet().getRange(row,30).setValue(currentDate)
}
}

function addTimestamp2(e){

  var startRow = 3;
  var targetColumn = 12;
  var targetColumn2 = 27;
  var ws = "Brand List";

  var row = e.range.getRow();
  var col = e.range.getColumn();

if (col === targetColumn2 && row >= startRow && e.source.getActiveSheet().getName() === ws) {

    var currentDate = new Date();
    e.source.getActiveSheet().getRange(row,31).setValue(currentDate)
}
}

How to create timestamp on multiple columns in GSheet

2 Answers2