cuba

Google Sheets + SQL = JSON

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

Usage

Editable demo (CodePen)

To start, enable link-sharing on your spreadsheet:

1. Click the Share button on the top-right corner of the Google Sheets spreadsheet page.
2. Click Get shareable link on the top-right corner of the modal.
3. To the left of the 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()

Here, 1InLekepCq4XgInfMueA2E2bqDqICVHHTXd_QZab0AOU is the ID of our example spreadsheet; it is the value between /d/ and /edit in the spreadsheet URL.

Querying private spreadsheets

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

1. Navigate to the Google API Console
2. Select a project from the drop-down box in the top bar.
3. Click Credentials (the Key icon) on the left navigation bar.
4. Click the Create credentials drop-down box, and select Service account key.
5. Click the Select… drop-down box, and select New service account.
6. Enter a Service account name. For Role, select Project › Viewer. For Key type, select JSON.
7. Click the 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.

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

• 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 | No Querying private spreadsheets | Yes | No

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

cuba [query]

Run the given query on a Google Sheets spreadsheet

Positionals:
  query  The Google Visualization API Query Language query to run on the Google
         Sheets spreadsheet                       [string] [default: "select *"]

Options:
  --help             Show help                                         [boolean]
  --version          Show version number                               [boolean]
  --credentials, -c  Path to the Service Account credentials JSON file; to run
                     queries on private spreadsheets that do not have
                     link-sharing enabled                               [string]
  --id, -i           The Google Sheets spreadsheet ID; the value between `/d/`
                     and `/edit` in the spreadsheet URL      [string] [required]
  --sheetId, -s      ID of the sheet to run the query on; the value after
                     `#gid=` in the spreadsheet URL      [string] [default: "0"]
  --sheetName, -n    Name of the sheet to run the query on              [string]

Prior art

• Tabletop
• Sheetrock
• google-spreadsheet

License

MIT