Write many rows at once
Apps Script recipes › Sheets as a database
Build a 2-D array and write it with one setValues call instead of one call per cell.
File Code.gs
/** Replaces everything under the header of `sheetName` with `rows` (arrays of values). */
function writeRows_(sheetName, rows) {
const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(sheetName);
const width = sheet.getLastColumn();
if (sheet.getLastRow() > 1) sheet.getRange(2, 1, sheet.getLastRow() - 1, width).clearContent();
if (!rows.length) return 0;
const fixed = rows.map(r => r.slice(0, width).concat(Array(Math.max(0, width - r.length)).fill('')));
sheet.getRange(2, 1, fixed.length, width).setValues(fixed);
return fixed.length;
}
Good to know
- Each getValue/setValue call talks to Google separately; 1,000 of them can take minutes, one setValues of 1,000 rows takes a second.
- Every row must have the same number of columns as the range.
Let the AI put it in your appGAS AI Builder knows every recipe here. Describe your app and it uses this code where it fits, in a Google Apps Script project in your own Drive.
Use it in a projectMore in Sheets as a database
Add a row safely when many people save at onceLockService keeps two saves from writing the same row; the row gets an Id and the time it was made.A dropdown list from another sheetKeeps the choices in their own tab (Lists) and puts a dropdown on a column that follows that list.Make a sheet with its header oncesetup() creates the tabs the app needs with a bold, frozen header, and adds missing columns to tabs that exist.Read a sheet as a list of objectsOne getValues() call, the first row as field names, dates turned into text so google.script.run can return them.Update or delete a row by its IdFinds the row by the Id column with TextFinder and writes the changed fields in one setValues call.