Automating Workspace with Apps Script
Performant Sheet Batching
From Slow to Pro with Batching
When you start writing scripts for Google Sheets, it's natural to read and write data one cell at a time. You might use getValue() to fetch a cell's contents and setValue() to update it. This works fine for small tasks, but it hits a wall quickly as your data grows. Why? Each of these calls is a network request. It’s like sending a courier to an office building to retrieve one piece of paper, then sending them back for another, and another.
This one-by-one approach is slow and inefficient. It can cause your script to run for a long time, potentially hitting Google's execution time limits. The professional way to handle data is through batch operations.
Most scripts designed for Google Sheets manipulate arrays to interact with the cells, rows, and columns in a spreadsheet.
Instead of many small trips, we make one big trip. We grab all the data we need from the sheet in one go, work with it locally within the script's memory, and then write all our changes back at once. This drastically reduces the number of network calls and makes scripts incredibly fast.
Working with Data in Bulk
The core of batch processing involves two key methods: getValues() and setValues(). Notice the 's' at the end. These methods work with a 2D array of data, which is essentially an array of arrays. Each inner array represents a row of cells.
Let’s say we want to read a range of data, convert all product names to uppercase, and write them back. Instead of looping through each cell individually, we read the whole block.
// Get the active sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// 1. Fetch all the data in one call
const range = sheet.getRange("A1:C10");
const values = range.getValues(); // values is now a 2D array
// 2. Manipulate the data in memory (much faster!)
for (let i = 0; i < values.length; i++) {
// Assuming product names are in the first column (index 0)
values[i][0] = values[i][0].toUpperCase();
}
// 3. Write all the data back in one call
range.setValues(values);
The key is to minimize interaction with the spreadsheet itself. Do as much work as possible on the array of data within your script.
Dynamic Ranges and Transformations
Hardcoding a range like "A1:C10" isn't very flexible. What if your data grows or shrinks? The getDataRange() method solves this. It automatically selects the entire range in a sheet that contains data.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const dataRange = sheet.getDataRange();
const allData = dataRange.getValues(); // Gets everything!
Once you have your data as an array, you can use modern JavaScript methods to transform it efficiently. Instead of traditional for loops, methods like .map() and .filter() are often cleaner and more powerful.
For example, let's create a new array containing only the sales figures from column B (index 1) that are greater than 100. The .map() method transforms each row, and .filter() removes the ones we don't want.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const values = sheet.getDataRange().getValues();
// Skip the header row with .slice(1)
const salesData = values.slice(1);
// Use map to create a new array with just the data we need.
// Here, we're making an array of objects for clarity.
const processedSales = salesData.map(row => {
return {
product: row[0], // Column A
amount: row[1] // Column B
};
});
// Now, filter this new array for sales over a certain amount.
const highValueSales = processedSales.filter(sale => sale.amount > 100);
// highValueSales now contains only the sales we're interested in.
console.log(highValueSales);
By combining getDataRange() with array methods like .map() and .filter(), you can build powerful, scalable, and highly performant scripts that handle data like a professional.
Time to check your understanding.
Why is repeatedly calling getValue() and setValue() in a loop considered inefficient for large datasets in Google Apps Script?
To read a block of data from a sheet into a 2D array and then write a modified 2D array back in a single operation, which pair of methods should you use?
This batching pattern is the single most important technique for creating scripts that can handle real-world data without timing out or running slowly.