3

I am attempting to write a script that will insert a timestamp on rows (in the 23rd column) inserted into a Google sheet from a form (not a google form; it is some other vendor that sends the data to the sheet and does not pass a timestamp).

I have been trying to script something from examples, but I cannot seem to get it to work. The script I have so far is as follows:

function setUpTrigger() {
  ScriptApp.newTrigger('timestamp')
  .forSpreadsheet('spreadsheet_id_goes_here')
  .onChange()
  .create();
}

function timestamp(e){

  if (e.changeType == 'INSERT_ROW'){
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(e.range.getRow(), 23).setValue(new Date());
  } 
}

Any help is greatly appreciated

TheMaster
  • 45,448
  • 6
  • 62
  • 85
aguadamuz
  • 321
  • 1
  • 3
  • 12
  • 1
    The problem is that your script is using `e.range` but the change event object doesn't include a `range` property. – Rubén Sep 11 '20 at 01:04
  • @Rubén Thank you. I am such a noob to this that I am still not sure how to correct the issue. How can I include a range property in the event object? – aguadamuz Sep 11 '20 at 01:09
  • Have you already read the linked question? – Rubén Sep 11 '20 at 01:20
  • Try using `getActiveRange()` Related: [Unable to detect Row inserts in Google sheets through google apps scripts?](https://stackoverflow.com/q/46377365/1595451) – Rubén Sep 11 '20 at 01:23
  • @Rubén This question doesn't state anything about "deleting" events. I'm inclined to reopen it. The linked question states it's impossible, whereas this is possible. Although the issue is `e.range` as you've suggested, I don't think it answers the question. – TheMaster Sep 11 '20 at 04:22
  • @aguadamuz Related: https://stackoverflow.com/a/55416583/ – TheMaster Sep 11 '20 at 04:27
  • I went ahead and reopened this. And there are interesting answers as well. – TheMaster Sep 11 '20 at 06:43

3 Answers3

3

Issue:

The onChange Event object e does not contain the key range. It does not provide information about the range directly.

Solution:

In most cases of onChange and specifically in yours, where change type is INSERT_ROW, the activeRange represents the current row that was inserted.

function timestamp(e){
  if (e.changeType == 'INSERT_ROW'){
    const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    sh.getRange(sh.getActiveRange().getRow(), 23).setValue(new Date());
  } 
}

Related Answers:

TheMaster
  • 45,448
  • 6
  • 62
  • 85
2

You see e.range.getRow() method. so you have check row number comes is valid or not. otherwise, you have used sh.getLastRow() method to add a new record.

function timestamp(e){
  if(e.changeType == 'INSERT_ROW'){
    var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var lastRow = sh.getLastRow()+1;
    sh.getRange(lastRow ,23).setValue(new Date);
 } 
}
bhumin
  • 188
  • 2
  • 9
0

According to the documentation for the onChange event object, e can contain information about the following:

  • authMode - which is a value from the ScriptApp.AuthMode enum.;

  • changeType - which represents the type of change (EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER).;

  • triggerUid - which is the ID of trigger that produced this event.;

  • user - which represents a User object, representing the active user, if available.

Therefore, in order to insert the timestamp needed you will have to make use of one of the above options.

Reference

ale13
  • 5,679
  • 3
  • 10
  • 25