Why is my Google Apps Script so slow, and how can I optimize `getValues` and `setValues` usage?

Long Processing Time Due to Excessive Use of getValues and setValues

Issue:

The code suffers from excessive usage of the getValues and setValues methods, which can significantly impact processing time, especially for larger data sets.

Optimization Techniques:

Minimize Service Calls:

• Avoid unnecessary calls to Google Apps Script services. Instead, cache data locally and operate on it within the script.

Look-Ahead Caching:

• Google Apps Script employs built-in look-ahead caching to retrieve data that is likely to be needed.

Minimize Read/Write Operations:

• Batch read and write operations by retrieving and manipulating data in arrays. This reduces the number of calls to the server.

Avoid Alternating Read/Writes:

• Alternating read and write commands disrupts look-ahead caching. Plan operations to minimize this alternating pattern.

Use Arrays:

• Use arrays to store data efficiently. Retrieve all data into an array with a single getValues call, perform operations on it, and then write it back with a single setValues call.

Example:

Consider the following code snippet:

// Slow version: Alternating read and write operations
const sourceRange = SpreadsheetApp.getActiveSheet().getRange("A1:D2");
const targetRange = sourceRange.offset(2, 0);
for (let i = 0; i < sourceRange.getNumRows(); i++) {
  for (let j = 0; j < sourceRange.getNumColumns(); j++) {
    const value = sourceRange.getCell(i + 1, j + 1).getValue();
    targetRange.getCell(i + 1, j + 1).setValue(value);
  }
}

// Fast version: Batch read and write operations
const sourceValues = sourceRange.getValues();
targetRange.setValues(sourceValues);

By batching read and write operations, the second code snippet significantly reduces the number of calls to services and improves processing time.

The above is the detailed content of Why is my Google Apps Script so slow, and how can I optimize `getValues` and `setValues` usage?. For more information, please follow other related articles on the PHP Chinese website!