CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
Jérémy Guyenot
Jérémy Guyenot

Reputation: 31

Google app script - Getting last sheet row updated

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

Upvotes: 1

Views: 1643

Answers (2)

Alessandro
Alessandro

Reputation: 2998

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

Upvotes: 1

Cooper
Cooper

Reputation: 64110

If you have a script that is triggered by a form submission then take the event object lets call it e. Then e.range is the range that was edited in the linked sheet. So e.range.getRow() is the row and e.range.getSheet().getName() is the name of the linked sheet.

form submit object for spreadsheet

Upvotes: 0

Related Questions