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

Google Apps Script is a pretty awesome tool for working with GSuite programatically. It's the foundation of all the behind the scenes magic that powers BudgetSheet, a Google Sheets extension I wrote and published for pulling real bank transactions into your spreadsheet.

But as great as Google Apps Script is, 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.

A few weeks ago, I announced via Twitter that I was writing a Google Apps Script library to address this. The syntax looks like this:

Querying for records:

The getRows method will return an array of objects that are your spreadsheet rows mapped to column hedings.

Updating records:

The updateRows method will update all matched rows in place. No row or column indices to worry about or track!

Deleting records:

The deleteRows method will delete the matching rows in your spreadsheet. Careful with this one!

Insert records:

The insertRows method can append new rows to your spreadhseet by column heading name. No more keeping track of array index positions!

SheetQuery will lookup the column headings, match them with the object keys provided, and insert a new row with an array of values mapped to the correct index positions of the spreadsheet headings. Any heading/column values not provided will be left blank. It couldn't be easier!

Install and Use SheetQuery

SheetQuery is available as a Google Apps Script Library.
Script ID: 1pbpOJxDDHVeVr6WQmR5TZKSqTsW4qwdIlcVKIM6UKYvswkivwHpPnHaO

If you build your scripts, you can install SheetQuery via NPM.

The full TypeScript source is available on the SheetQuery GitHub Page. If you are familiar with Google Apps Script, I would love your contributions.