Retrieve number of rows in Google Sheets

user_2444591 0
Viewed 27665

I need a JavaScript method for my webpage to count how many rows are in a Google sheet (it's used as a response sheet for a form). I've been scouring the web for easy tutorials on how to make Google Sheets into a database.

Is there a simpler way to do it?

I don't have much experience in Google scripts, but all I need is a way I can have read access to the spreadsheet using preferably Ajax or some similar JavaScript method.

google-sheets row javascript
2 Answers

To retrieve the number of rows in your Google Spreadsheet use the following:

var ss = SpreadsheetApp.openById("1qNCf0wKl................");
var sheet = ss.getSheetByName("sheet_name");
var number = sheet.getMaxRows().toString();
var number = number.replace(".0","");
Logger.log(number);

In order to have read access to a Spreadsheet the guidance provided in the following link is good enough:

Query Google Spreadsheet with URL Parameters

For example, if you want to get all the cells with the word «budget» in your Spreadsheet, use the following script:

var id = "1qNCf0wKlx1RF......";
var column = "A";
var query = "budget";
var url = "https://docs.google.com/spreadsheets/d/"+id+"/gviz/tq?tq=SELECT%20*%20where%20"+column+"%20contains%20%22"+query+"%22";
var text = UrlFetchApp.fetch(url).getContentText().toString();
Logger.log(text);

For this, the Spreadsheet has to be published previously. After retrieving the «budget» rows the text has to be formated, but that's another issue.

Admin1
Related