cuba-sheet
v0.2.1
Published
Google Sheets + SQL = JSON
Downloads
11
Maintainers
Readme
cuba-sheet
This is a clone of https://github.com/yuanqing/cuba with some latest fixes
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
To start, enable link-sharing on your spreadsheet:
- Click the
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
Copy link
button, ensure that access rights is set toAnyone 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:
- 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
Create credentials
drop-down box, and selectService account key
. - Click the
Select…
drop-down box, and selectNew service account
. - Enter a
Service account name
. ForRole
, selectProject › Viewer
. ForKey type
, selectJSON
. - Click the
Create
button. This will generate a JSON file with the Service Account credentials. Note theclient_email
andprivate_key
values in this JSON file.
- Navigate to your spreadsheet.
- Click the
Share
button on the top-right corner of the page. - In the
Enter names or email addresses…
text box, enter theclient_email
of the Service Account, then click theSend
button.
- With the API, pass in a
serviceAccountCredentials
object, specifying theclientEmail
andprivateKey
. - 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 ifsheetName
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]