sheetquery
    TypeScript icon, indicating that this package has built-in type declarations

    1.2.1 • Public • Published

    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.

    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.

    Install

    npm i sheetquery

    DownloadsWeekly Downloads

    4

    Version

    1.2.1

    License

    BSD-3-Clause

    Unpacked Size

    29 kB

    Total Files

    10

    Last publish

    Collaborators

    • avatar