Getting the "lastRow" of the active Google Sheet

#1

I have two categories of Quizzes:

  • Countries and Capitals - 196 countries
  • Wonders of the World - 7 Wonders

Alexa starts with Country=1 (Andorra) (Set RowNumber=2) and keeps going until the last entry Zimbabwe = 196. At this point Alexa says - You have reached the end of Countries Quiz, Let us start with: Wonders of the World Quiz"

Everything works as expected, but currently I am hard coding the last row - :"If RowNumber=197 ., speak “End of Countries Quiz”,

To avoid hard coding the row number, I would like to get the last row of my sheet.

I tried the code block with:

var lastRow = sheet.getLastRow()
based on the Google Sheet method “getLastRow”.

But it returns an error. “Reference Error: .getLastRow is undefined:”

Any idea, how to get the last row of a particular sheet

Thanks

#2

Actually, code block seems it can use only very pure JavaScript (I guess Node.js), so you can’t use Google Sheets’ method there.

I think 3 ways to do it.

  • Get the whole response from Google Sheets using custom API, put into a variable, and count how many objects there are. That numberUsing code block to do this.
  • Get last row number by using Google sheets’ functions, put it in other sheet, and read it by integration block.
  • you can do the same with google app script on Google sheets.
#3

Update: I used option 3 - Google App Script. And was able to get the last Row and call this in VF.

Here is the Google App Script code:

function onEdit(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var out = []
var lastcol = sheet.getLastColumn();
var lastrow = sheet.getLastRow();
out.push(lastrow, lastcol);
return(out);
}

1 Like