How do i get data from the last row of a given column in a google spreadsheet after the form is submitted?

Question

I'd like to send an email to a specific person depending on the data in the last row, similar to the vlookup function. If the data entered in the form is x then send mail to joe, if data is y send mail to jack etc. I'm confused on how range selection works when there is no active range like when you have in the spreadsheet view.

function sendEmails() 
{   
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = sheet.getLastRow();
  var data = sheet.getRange(row, 5).getValue();


 if (data == "xxx")
 {
   sendmail //i got this
 }

Serge insas Serge insas · Accepted Answer · 2012-10-08T10:35:09

In such situations (reacting to a form submit) when the spreadsheet is not necessarily open, you should preferably use these methods to access a sheet :

  var ss = SpreadsheetApp.openById('the ID of the spreadsheet');
  var sh = ss.getSheets()[0];// access first sheet (0 indexed)
  var row = sh.getLastRow();
  var data = sh.getRange(row, 5).getValue();

How do i get data from the last row of a given column in a google spreadsheet after the form is submitted?

2 Answers