CODEWITHSUNDEEP
google-sheetsgoogle-apps-scripttriggers
aguadamuz
aguadamuz

Reputation: 421

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

Upvotes: 3

Views: 569

Answers (3)

ale13
ale13

Reputation: 6052

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

Upvotes: 0

TheMaster
TheMaster

Reputation: 50416

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:

Upvotes: 4

bhumin
bhumin

Reputation: 196

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);
 } 
}

Upvotes: 1

Related Questions