Read a sheet as a list of objects
Apps Script recipes › Sheets as a database
One getValues() call, the first row as field names, dates turned into text so google.script.run can return them.
File Code.gs
/** Every row of a sheet as { header: value }. */
function readRows_(sheetName) {
const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(sheetName);
if (!sheet || sheet.getLastRow() < 2) return [];
const [header, ...rows] = sheet.getDataRange().getValues();
const tz = Session.getScriptTimeZone();
return rows
.filter(r => r.some(v => v !== ''))
.map(r => Object.fromEntries(header.map((h, i) => {
const v = r[i];
return [h, v instanceof Date ? Utilities.formatDate(v, tz, 'yyyy-MM-dd') : v];
})));
}
/** Called from the page: google.script.run.withSuccessHandler(show).getItems() */
function getItems() {
return readRows_('Items');
}
Good to know
- A Date anywhere in what a function returns to the page makes the whole result null in the browser, without an error. This recipe turns dates into 'yyyy-MM-dd'.
- Empty rows at the bottom are skipped.
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.Write many rows at onceBuild a 2-D array and write it with one setValues call instead of one call per cell.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.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.