Google app script - Getting last sheet row updated

Question

I have a script associated with a google sheet that it's call after a Google form is submitted.

Actually I can get all rows or the last but in case one form answer is updated I need to find this row updated to make my treatment.

Is there a way to do that?

This is my two case:

// Getting last row data
function getSpreadSheetLastRowData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var row = sheet.getLastRow();
  return sheet.getRange('A'+row+':BG'+row).getValues();
}

// Getting all rows data
function getSpreadSheetAllData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var row = sheet.getLastRow();
  return sheet.getRange('A2:BG'+row).getValues();
}

Hello Jérémy, do you want to get the last updated row automatically and right after the form submission? — Alessandro
On google form submit, it triggered a google app script and i want to get the updated row (in case of edited one answer) or the lastest row (in case of new answer). — Jérémy Guyenot

Alessandro Alessandro · Accepted Answer · 2020-03-30T13:08:11

You will need to write an event handler for the onFormSubmit event object and attach it to your spreadsheet.

From the Spreadsheet linked to your Form go to Tools > Script Editor. Here write a function to install the trigger:

function installTriggerToSpreadsheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('sumbissionHandler')
  .forSpreadsheet(ss)
  .onFormSubmit()
  .create();
}

Now your trigger will handle the form submission calling the submissionHandler function inside the Spreadsheet context. This means you can read the affected row by using a function like this:

function submissionHandler(e) {
 var affectedRow = e.range.getRow();

 // ... Apply your treatment.
}

References:

Triggers

Event Objects

Google app script - Getting last sheet row updated

2 Answers

References: