CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
gchristo234
gchristo234

Reputation: 158

Google Sheets Script getLastRow() not returning all values

I have email addresses in first column. When I try to get the last row of this column, the function only returns the first two values. Here is a link to the sheet: https://docs.google.com/spreadsheets/d/1piGCiWMcUKUWagIJSZ_0SxxuNG3tcEcrx-fKnMDOIlQ/edit?usp=sharing

Here is the script function:

function myFunction() {
  var rangeLastRow = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(2, 1).getLastRow(); 
  var emails = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(2, 1,rangeLastRow).getValues();
  Logger.log(emails);
}

Shouldn't it be returning all of the email addresses in the first column? It is only returning the first two email addresses.

Upvotes: 0

Views: 6609

Answers (2)

Tanaike
Tanaike

Reputation: 201713

How about this modification?

Modification points :

  • getRange(2, 1).getLastRow() means that it is to retrieve the last row for the range of "a2". So the result is 2.
    • In order to retrieve the last　row of the sheet, please use SpreadsheetApp.getActive().getSheetByName('Sheet1').getLastRow()
    • The 3rd parameter of getRange(row, column, numRows) is the numRows. When you want to retrieve data of "a2:a6", numRows is rangeLastRow - 1.
      • By above reason, getRange(2, 1, 2).getValues() retrieves only 2 cells.
  • SpreadsheetApp.getActive().getSheetByName('Sheet1') can be used by importing a variable.

The modified script which reflected above is as follows.

Modified script :

var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var rangeLastRow = sheet.getLastRow(); 
var emails = sheet.getRange(2, 1, rangeLastRow - 1).getValues();
Logger.log(emails);

References :

Upvotes: 2

Dean Ransevycz
Dean Ransevycz

Reputation: 953

No, it's working correctly. range.getLastRow() returns the absolute index of the last row of the range. Your line 

var rangeLastRow = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(2, 1).getLastRow();

returns the row index of cell A2 that you specified in getRange(2, 1), so your call to range.getValues() is getting the values of the range A2:A3. i.e. Your call to range.getLastRow() returns 2 because your range is (row 2, col 1), so your call to range.getValues() is looking at the range (row 2, col 1, 2 rows)

You want the last row of data in the sheet, which you get with sheet.getLastRow(). In fact, why not do away with var rangelastRow altogether? The following give you the result you're after:

function myFunction() {
  var ss = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var emails = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(2, 1,ss.getLastRow() - 1).getValues();
  Logger.log(emails);
}

Note that you actually want to use getLastRow() - 1 at set the number of rows in the range because your range starts at row 2, not row 1.

Upvotes: 1

Related Questions