Search code examples
google-sheetsgoogle-apps-scripttriggers

Google Sheets script to insert a timestamp on a row inserted from a form? How to detect the inserted row using onChange?

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

Solution

  • 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: