How to retrieve values from the last row

Question

I created a Form for requesting new student Google account. I want a Sheets Script to email the person who submits the Form the new account information, which is created via a formula on a "Results" sheet.

function Notification() {
  // Send email notice for accounts
  var lastRow = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Results").getLastRow();
  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Results").getRange("A" + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  var AccountName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Results").getRange("H" + lastRow);
  var Password = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Results").getRange("I" + lastRow);
  var PW = Password.getValue();
  var Account = AccountName.getValue(); 
  // Fetch the email address
  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Results").getRange("G" + lastRow);
  var emailAddress = emailRange.getValues();
// Send Alert Email.
   var message = 'Account Request - Account Name: ' + Account + ' Password: ' + PW; // Second column
   var subject = 'Google Account Request';
MailApp.sendEmail(emailAddress, subject, message);
     }

This script is triggered on a new Form Submit. Attempting to pull the values from the last created row queried to a "Results" sheet, using lastRow to find the latest entries row, from select columns. Script runs without error, but no email is sent, telling me that it's not getting the values, or returning null values. This is the sheet its pulling data from

Please edit to ask a specific questions. Right now, no one is sure what your asking. Your question should focus on a problem that you are seeing and ask for a solutio to a specific problem. Please take some time to read the help page, especially the sections named What to ask and What not to ask. — Gardener
Use the event object, or a Sheet method that gives you the bottommost row number in which there is some data, and then get specific row-column intersections that you care about. — tehhowch
I have searched and read literally 100s of resources, replies to posts like these, and forums, and have yet to find one that answers this question. Telling me to "google it" doesn't help at all. In fact the time you took to rudely assume I didn't look first is less helpful than not replying at all. A link to what you are referring to would have been more helpful. — Chris Coon
Chris, you are welcome to StackOverflow but, with all due respect, you overstepped the mark. There are very experienced people (more so than me) who are only too willing to help you but we expect you to have researched the problem. In this case, "google sheets script values in last row" yields an extraordinary number of on-topic responses which is why responses to you were so brief. If you could find no relevant research, then its reasonable to expect that you should explain why your situation differs from those that you found. — Tedinoz

Tedinoz Tedinoz · Accepted Answer · 2019-04-12T00:20:45

You are only sending row 2 because you are retrieving only one value (Password.getValue() and AccountName.getValue()). Consider the difference between getValue versus getValues.

On the other hand, by declaring a range such as getRange("H2:H") ("Account Name") you are including every row in the spreadsheet, including rows with and without content.

How would I make it pull the values from the last created row?

There are two options:

getlastRow() (the "Sheet" versus "Range" version) "returns the position of the last row that has content" doc ref here. In fact, that item of documentation has a good example of how to declare a Range using the command.
there is another (very useful) utility described on StackOverflow that is a favourite of mine. This describes the number of contiguous rows, in a column, with content.

I suggest that you declare the range using getRange(row, column, numRows, numColumns).
"row" having been determined using one of the methods described above, "numRows"=1. Then getValues for that range will include only the values in that row, and your email will be sent only in relation to that row.

How to retrieve values from the last row

1 Answers