:cuba: Google Sheets + SQL = JSON
Google Sheets + SQL = JSON
To start, enable link-sharing on your spreadsheet:
Share
button on the top-right corner of the Google Sheets spreadsheet page.Get shareable link
on the top-right corner of the modal.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:
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.
$ yarn add cuba
Feature | Supported in Node? | Supported in the browser? |
---|---|---|
Array interface | Yes | Yes |
Stream interface | Yes | No |
Querying private spreadsheets | Yes | No |
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]