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

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.

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

$ yarn add cuba

const cuba = require('cuba')

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

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

const cubaStream = require('cuba').stream

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.

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.

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]

• Tabletop
• Sheetrock
• google-spreadsheet

MIT