AppsScriptPulse

SheetQuery: An ORM-Like Query Builder for Google Sheets and Google Apps Script

Image credit: Vance Lucas

A simple and small Google Apps Script library for quickly and easily finding and updating records in Google Sheets with a familiar ORM-like syntax

Following our previous post on Converting Google Sheets cell values to an object array, Vance Lucas (@vlucas) got in touch to highlight the SheetQuery library he has created which as well as being able to get Google Sheet data as an object array has some additional nice features for updating cell values. As Vance highlights:

sometimes working with spreadsheets to find and update specific rows of data can be awkward and tedious. There is no direct built-in way to search for specific values in rows by headings. To do this with the Google-supplied APIs, you have to keep track of row index numbers, column index numbers, and arrays of row data while planning your updates. It’s not fun code to write, and it’s relatively error-prone, especially if you are deleting rows, which causes the row index numbers to shift dynamically.

The library includes a .where method which can be used to apply a filtering function to select the rows of a spreadsheet to be read and/or updated. If you are regularly developing scripts that interact with Google Sheets data this can be a great library to keep in mind.

Source: SheetQuery: An ORM-Like Query Builder for Google Sheets and Google Apps Script