Report: Process Cost of Google Apps Script During Large Calculations by Formulas on Google Spreadsheet

report / GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

Today, I found a question ( https://stackoverflow.com/q/73540735 ) in Stackoverflow by Max Makhrov. When I saw this question, I thought that this is a good raising question. In this question, it has reported that when the Spreadsheet includes the formulas of the large calculation, when a Google Apps Script is run during the calculation of the formulas is running, the processing time of the script becomes long.

I have also experienced this before. And, in that time, I noticed that there are differences in process costs for the methods of Google Apps Script under this condition.

Replicating situation

A simple method for replicating this situation is as follows. Please copy and paste the following script to the script editor of Google Spreadsheet.

// This function is used as a custom function.
function CUSTOM() {
  Utilities.sleep(20000);
  return "ok";
}

// This sample retrieves the sheet name of active sheet.
function sample1() {
  console.time("label1");
  const sheet = SpreadsheetApp.getActiveSheet();
  console.timeEnd("label1");

  console.time("label2");
  const res = sheet.getSheetName(); // or sheet.getName();
  console.timeEnd("label2");

  console.time("label3");
  console.log(res);
  console.timeEnd("label3");
}

// This sample retrieves the sheet ID of active sheet.
function sample2() {
  console.time("label1");
  const sheet = SpreadsheetApp.getActiveSheet();
  console.timeEnd("label1");

  console.time("label2");
  const res = sheet.getSheetId();
  console.timeEnd("label2");

  console.time("label3");
  console.log(res);
  console.timeEnd("label3");
}

Test 1

Fig. 1: Without calcutation of formulas, process costs of each method of label1, label2, and label3 in sample1 and sample2.

Figure 1 shows the process costs of each method of label1, label2, and label3 in sample1 and sample2.

In this test, please run sample1 and sample2 with the script editor in order.

  • When sample1 is run with the script editor, the times of label1, label2, and label3 are about 8 ms, 150 ms, and 4 ms, respectively. The result of getSheetName() is almost the same with getName().
  • When sample2 is run with the script editor, the times of label1, label2, and label3 are about 8 ms, 2 ms, and 4 ms, respectively.

From this result, it is found that the process cost of getSheetName() is higher than that of getSheetId().

Test 2

Fig. 2: During calculation of formulas, process costs of each method of label1, label2, and label3 in sample1 and sample2.

Figure 2 shows the process costs of each method of label1, label2, and label3 in sample1 and sample2.

In this test, please please run sample1 just after putting a custom function of =CUSTOM() to a cell. And also, please remove the custom function from the cell. And, please please run sample2 just after putting a custom function of =CUSTOM() to a cell.

  • When sample1 is run with the script editor just after =CUSTOM() was put to a cell, the times of label1, label2, and label3 are about 8 ms, 17000 ms, 4 ms, respectively. The result of getSheetName() is almost the same with getName().
  • When sample2 is run with the script editor just after =CUSTOM() was put to a cell, the times of label1, label2, and label3 are about 8 ms, 2 ms, 4 ms, respectively.

From this result, it was found that when sample1 is run just after =CUSTOM() was put to a cell, the process cost of getSheetName() becomes very high. On the other hand, it was found that under this condition, the process cost of getSheetId() was almost no change. And, from both results of “Test 1” and “Test 2”, when getSheetName() is run during a custom function is run, the process cost of getSheetName() depends on the cost of the custom function (From Max Makhrov’s question, it has already been found that the same situation occurs with the built-in function.). And, it is found that when Google Apps Script is run under the cost of formula is high, it affects the process cost of Google Apps Script.

Workaround

In order to use Google Apps Script without affecting the large calculations by the formulas on Google Spreadsheet, here, I proposed to use Sheets API. From my experience, it was found that when I retrieved the metadata (sheet names, sheet IDs, and so on) from Spreadsheet including the large calculations by the formulas using Sheets API, this process was not affected by the calculation of formulas. And also, it was found that when the cell values were tried to be retrieved, even when Sheets API was used, the process was affected by the calculation of formulas.

From this result, it is considered that when the cell values are retrieved during the large calculation of formulas, the methods might wait for trying to finish the calculation of formulas. But, when the metadata except for the cell values is retrieved during the calculation of formulas, the methods are run, immediately.

As a sample script, when the following sample is used just after the above custom function of =CUSTOM() is put to a cell, the processing time was about 350 ms. It is considered that this result indicates that there is no effect of formulas.

function sample3() {
  console.time("label1");
  const fields =
    "sheets(conditionalFormats,developerMetadata,protectedRanges,charts,properties,bandedRanges,merges,slicers,rowGroups,filterViews,columnGroups,basicFilter,data(startRow,columnMetadata,startColumn,rowMetadata))";
  const res = Sheets.Spreadsheets.get("### Spreadsheet ID ###", { fields });
  console.log(res);
  console.timeEnd("label1");
}

In this sample script, the metadata except for rowData is retrieved. This value of fields doesn’t include the cell values. As the result, it is considered that the methods without affecting the calculation of formulas are as follows.

  • When the metadata is retrieved from Spreadsheet, “Method: spreadsheets.get” of Sheets API and fields except for rowData is not affected by the calculation of formulas.

  • When the metadata is retrieved from Spreadsheet, there are several methods that are not affected by the calculation of formulas. For example, getActiveSheet() and getSheetId(). I think that besides these methods might be existing.

  • When the cell values are retrieved, even when Sheets API and Spreadsheet service (SpreadsheetApp) are used, the process is affected by the calculation of formulas.

  • On the other hand, when the cell values are put to the cells using Sheets API, it was found that the process is not affected by the calculation of formulas. And also, it was found that when the cell values are put using the Spreadsheet service (SpreadsheetApp), the process is affected by the calculation of formulas.

I thought that this information is useful for developing applications using Google Apps Script and Sheets API.

Reference

 Share!