Sheet.getRowHeight() and Sheet.getColumnWidth() are taking between 5-10 seconds to return values per row/column. How can I speed this up?

Question

In a Google App Script I am summing the heights of rows in a Google Sheets spreadsheet, but .getRowHeight() is so slow it leaves no time for the rest of the script to complete before the six minute execution limit is reached. You can see from the log below each call to .getRowHeight() is taking a ridiculous amount of time per call. .getColumnWidth() has the same problem.

const sheet = SpreadsheetApp.getActive().getSheetByName(sheet_name);
for(let row = 10; row < 30; ++row) {
    Logger.log('Getting row height for row ' + row + '...');
    const row_height = sheet.getRowHeight(row);
    Logger.log('... Row '+ row + ' height: ' + row_height);
  }


8:41:08 PM Info Getting row height for row 10...
8:41:17 PM Info ... Row 10 height: 28
8:41:17 PM Info Getting row height for row 11...
8:41:25 PM Info ... Row 11 height: 20
8:41:25 PM Info Getting row height for row 12...
8:41:36 PM Info ... Row 12 height: 14
8:41:36 PM Info Getting row height for row 13...
8:41:44 PM Info ... Row 13 height: 60
8:41:44 PM Info Getting row height for row 14...
8:41:51 PM Info ... Row 14 height: 20

I tried this on a new spreadsheet and it was fast enough, 100 rows in 10 seconds, but on a big spreadsheet (30+ sheets with hundreds of rows and columns with data) .getRowHeight() and .getColumnWidth are so slow they are unusable. What's wrong? Surely the size of the spreadsheet shouldn't effect the call to .getRowHeight(), right?

How can I speed this up? Can I get the combined height/width for multiple rows/columns in a single API call?

(Note: This is not some X/Y problem where the answer is to work around the six minute execution limit! I already have a job system for pausing and resuming long running scripts, but this particular script is so simple it should be able to execute in a few seconds.)

Answer 1

When the number of cells in a Spreadsheet is large, the process cost of Spreadsheet service (SpreadsheetApp) becomes high. This has also already been mentioned in your question. In this case, when Sheets API is used, the process cost can be reduced. Ref1 (Author: me) and Ref2 (Author: me) So, in your situation, I would like to propose to use Sheets API. When Sheets API is used, your showing script is as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  const sheet_name = "Sheet1"; // Please set your sheet name.

  const spreadsheetId = SpreadsheetApp.getActive().getId();
  const { rowMetadata } = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [sheet_name], fields: "sheets(data(rowMetadata))" }).sheets[0].data[0];
  for (let row = 10; row < 30; ++row) {
    Logger.log('Getting row height for row ' + row + '...');
    const row_height = rowMetadata[row - 1].pixelSize;
    Logger.log('... Row ' + row + ' height: ' + row_height);
  }
}

• When this script is run, the same result with your showing script is obtained.

Reference:

• Method: spreadsheets.get