cuba

Google Sheets + SQL = JSON

• Run SQL-esque queries against your Google Sheets spreadsheet, get results as JSON
• Works in both Node and the browser
• Perfect for prototyping, or leveraging Google Sheets as a collaborative datastore for your app
• 1.54 KB gzipped

Usage  ·  Installation  ·  Configuration  ·  API  ·  CLI  ·  Prior art  ·  License

Usage

Editable demo (CodePen)

To start, enable link-sharing on your spreadsheet:

• Click the blue Share button on the top-right corner of the Google Sheets spreadsheet page.
• Click Get shareable link on the top-right corner of the modal.
• To the left of the grey Copy link button, ensure that access rights is set to Anyone with the link can view.

Then:

const cuba = require('cuba')

async function main () {
  const query = cuba('1InLekepCq4XgInfMueA2E2bqDqICVHHTXd_QZab0AOU')
  const array = await query('select *')
  console.log(array)
  //=> [
  //=>   { id: 1, name: 'foo' },
  //=>   { id: 2, name: 'bar' },
  //=>   { id: 3, name: 'baz' }
  //=> ]
}
main()

Querying private spreadsheets

In Node, we can also run queries on private spreadsheets that do not have link-sharing enabled:

1. Create a Service Account on the Google API Console.

• Navigate to the Google API Console
• Select a project from the drop-down box in the top bar.
• Click Credentials (the Key icon) on the left navigation bar.
• Click the blue Create credentials drop-down box, and select Service account key.
• Click the Select… drop-down box, and select New service account.
• Enter a Service account name. For Role, select Project › Viewer. For Key type, select JSON.
• Click the blue Create button. This will generate a JSON file with the Service Account credentials. Note the client_email and private_key values in this JSON file.

2. Give view access to the Service Account.

• Navigate to your spreadsheet.
• Click the blue Share button on the top-right corner of the page.
• In the Enter names or email addresses… text box, enter the client_email of the Service Account, then click the blue Send button.

3. Pass in the Service Account credentials when querying the spreadsheet with Cuba.

• With the API, pass in a serviceAccountCredentials object, specifying the clientEmail and privateKey.
• With the CLI, use the --credentials (or -c) flag to specify the path to the Service Account credentials JSON file.

Installation

$ yarn add cuba

API

Feature	Supported in Node?	Supported in the Browser?
Array interface	Yes	Yes
Stream interface	Yes
Querying private spreadsheets	Yes

➥ Array interface

const cuba = require('cuba')

const querySpreadsheet = cuba(spreadsheetId [, serviceAccountCredentials])

cuba returns a function for running queries on the spreadsheet with the given spreadsheetId.

• spreadsheetId is a string representing the Google Sheets spreadsheet to be queried. This is the value between /d/ and /edit in the spreadsheet URL.

• (Node only) serviceAccountCredentials is an optional object literal. This is to run queries on private spreadsheets that do not have link-sharing enabled.

  Key	Description	Default
  clientEmail	Email address of the Service Account that has view access to the spreadsheet being queried.	undefined
  privateKey	Private key of the Service Account.	undefined

const array = await querySpreadsheet([query, options])

querySpreadsheet returns a Promise for an Array containing the results of running the query on the spreadsheet.

• query is a Google Visualization API Query Language query. Defaults to 'select *'.

• options is an optional object literal.

  Key	Description	Default
  sheetId	ID of the sheet to run the query on. This is the value after #gid= in the spreadsheet URL. Ignored if sheetName is specified.	0
  sheetName	Name of the sheet to run the query on.	undefined
  transform	A function for transforming each item in the result.	The identity function

➥ Stream interface

const cubaStream = require('cuba').stream

const querySpreadsheet = cubaStream(spreadsheetId [, serviceAccountCredentials])

cubaStream returns a function for running queries on the spreadsheet with the given spreadsheetId. The function signature is identical to the corresponding function in the Array interface.

const stream = await querySpreadsheet([query, options])

querySpreadsheet returns a Promise for a Readable Stream containing the results of running the query on the spreadsheet. The function signature is identical to the corresponding function in the Array interface.

CLI

Usage: cuba [query] [options]

Query:
  The Google Visualization API Query Language query to run on the
  Google Sheets spreadsheet. Defaults to 'select *'.

Options:
  -c, --credentials <path>  Path to the Service Account credentials
                            JSON file. This is to run queries on
                            private spreadsheets that do not have
                            link-sharing enabled.
  -h, --help  Print this message.
  -i, --id <spreadsheetId>  The Google Sheets spreadsheet ID. This is
                            the value between `/d/` and `/edit` in
                            the spreadsheet URL.
  -s, --sheetId <sheetId>  ID of the sheet to run the query on. This
                           is the value after `#gid=` in the
                           spreadsheet URL. Defaults to '0'.
  -n, --sheetName <sheetName>  Name of the sheet to run the
                               query on.
  -v, --version  Print the version number.

Prior art

• Tabletop
• Sheetrock
• google-spreadsheet

License

MIT