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 <p>By batching read and write operations, the second code snippet significantly reduces the number of calls to services and improves processing time.</p>

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!