SheetQuery
Query builder/ORM for easily manipulating spreadsheets with Google Apps Script for Google Sheets (SpreadsheetApp).
This library was created for BudgetSheet and I thought it might be useful for others as well.
Install as a Google Apps Script Library
SheetQuery is available as a Google Apps Script Library.
Script ID: 1pbpOJxDDHVeVr6WQmR5TZKSqTsW4qwdIlcVKIM6UKYvswkivwHpPnHaO
This is probably the easiest way to use SheetQuery and ensure you are always using the latest version.
NOTE: If you use it as a library, you must use the library name first, as all libraries are automatically namespaced:
SheetQuery.sheetQuery() // Using it as a library
sheetQuery() // Using it as a local file
Manual Install Into Your Own Google Apps Script
If you don't want to use a Library file and need the script to be local within your own project, Create a new file named
SheetQuery.gs
in your Google Apps Script project. Copy the contents of dist/index.js
into that file.
Installation for Built Projects via NPM
To use sheetquery
via NPM, install it in your project as a dependency:
npm i sheetquery
Now you are ready to get started using sheetquery in your project via import or require().
Requirements
SheetQuery requires a Google Sheet with a heading row (typically the first row where the columns are named). SheetQuery will use the heading row for all other operations, and for returning row data in key/value objects.
Usage
SheetQuery operates on a single Sheet at a time. You can start a new query with sheetQuery().from('SheetName')
.
Query For Data
Data is queried based on the spreadsheet name and column headings:
const query = sheetQuery()
.from('Transactions')
.where((row) => row.Category === 'Shops');
// query.getRows() => [{ Amount: 95, Category: 'Shops', Business: 'Walmart'}]
If your headings are on a different row than the first row, specify it as the second argument to from
:
const query = sheetQuery()
.from('Transactions', 2) // For headings on row 2
.where((row) => row.Category === 'Shops');
// query.getRows() => [{ Amount: 95, Category: 'Shops', Business: 'Walmart'}]
Update Rows
Query for the rows you want to update, and then update them:
sheetQuery()
.from('Transactions')
.where((row) => row.Business.toLowerCase().includes('starbucks'))
.updateRows((row) => {
row.Category = 'Coffee Shops';
});
The updateRows
method can either return nothing, or can return a row object with updated properties that will be saved
back to the spreadsheet row. If the updater function returns nothing/undefined, the row object that was passed in will
be used (along with any changed values that will be updated by reference).
Delete Rows
Query for the rows you want to delete, and then delete them:
sheetQuery()
.from('Transactions')
.where((row) => row.Category === 'DELETEME')
.deleteRows();
Note: Be careful with this one, and always make sure to use it with a where
filter to limit the number of rows that
will be deleted!
Insert Rows
Rows can be inserted with SheetQuery by column heading name. No more keeping track of array index positions!
sheetQuery()
.from('Transactions')
.insertRows([
{
Amount: -554.23,
Name: 'BigBox, inc.',
},
{
Amount: -29.74,
Name: 'Fast-n-greasy Food Spot',
},
]);
This can be a great way to insert rows into specific column headings without worrying about whether or not a user has edited the spreadsheet to add their own columns, etc. that would otherwise cause inserting new rows to be painful.
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.