google apps script - How to get a data range of the Sheet

Question

I need to get a data range of the Sheet. I must call APIs twice.

GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/Sheet1?majorDimension=ROWS
// Height is result.length

GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/Sheet1?majorDimension=COLUMNS
// Width is result.length

But I do not need data, I only need the size of the data range. Is there a way to get that?

Answer 1

Unfortunately, I couldn't find the method for directly retrieving "the size of the data range" using Sheets APIs. So as a workaround, I would like to propose to use Web Apps. In your case, it seems that you already have an access token. So this proposal accesses to Web Apps using the access token. The flow of this proposal is as follows.

1. Copy and paste this sample script

function doGet(e) {
  var id = e.parameter.id;
  var sheetName = e.parameter.sheetname;
  var result = {};
  try {
    var ss = SpreadsheetApp.openById(id).getSheetByName(sheetName);
    result.LastRow = ss.getLastRow();
    result.LastColumn = ss.getLastColumn();
  } catch(er) {
    result.Error = er;
  }
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

After copied and pasted this, please directly run doGet(). By this, the authorization can be done. Please ignore the error of script.

2. Deploy Web Apps :

The condition for deploying Web Apps is as follows.

• On script editor on the project with doGet().
  • Publish -> Deploy as web app
  • For "Execute the app as:", set "Me".
  • For "Who has access to the app:", set "Only myself".
  • Click Deploy.
  • Copy URL of Web Apps.

At above condition, you can access to Web Apps only when the access token is used. When the access token is not used, the error of Unauthorized returns. In this case, please include https://www.googleapis.com/auth/drive to the scopes.

3. Curl sample :

This is a sample curl. Using this, you can retrieve LastRow and LastColumn of the spreadsheet. When you use this, please replace URL to your Web Apps's URL, and input spreadsheet ID and sheet name.

curl -GL 
  -H "Authorization: Bearer ### your access token ###" 
  -d "id=### spreadsheet ID ###" 
  -d "sheetname=### sheet name ###" 
  "https://script.google.com/macros/s/#####/exec"

Sample result :

{"LastRow":10,"LastColumn":10}

If this was not useful for you, I'm sorry.