go>> cuba>> 返回
项目作者: yuanqing

项目描述 :
:cuba: Google Sheets + SQL = JSON
高级语言: JavaScript
项目地址: git://github.com/yuanqing/cuba.git
创建时间: 2018-04-06T18:00:26Z
项目社区:https://github.com/yuanqing/cuba

开源协议:MIT License

下载


cuba npm Version Build Status Bundle Size

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:

  1. const cuba = require('cuba')
  2. async function main () {
  3. const query = cuba('1InLekepCq4XgInfMueA2E2bqDqICVHHTXd_QZab0AOU')
  4. const array = await query('select *')
  5. console.log(array)
  6. //=> [
  7. //=> { id: 1, name: 'foo' },
  8. //=> { id: 2, name: 'bar' },
  9. //=> { id: 3, name: 'baz' }
  10. //=> ]
  11. }
  12. 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. Create a Service Account on the Google API Console.



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.



2. Give view access to the Service Account.



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.



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

  1. $ 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

  1. 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

  1. 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

  1. cuba [query]
  2. Run the given query on a Google Sheets spreadsheet
  3. Positionals:
  4. query The Google Visualization API Query Language query to run on the Google
  5. Sheets spreadsheet [string] [default: "select *"]
  6. Options:
  7. --help Show help [boolean]
  8. --version Show version number [boolean]
  9. --credentials, -c Path to the Service Account credentials JSON file; to run
  10. queries on private spreadsheets that do not have
  11. link-sharing enabled [string]
  12. --id, -i The Google Sheets spreadsheet ID; the value between `/d/`
  13. and `/edit` in the spreadsheet URL [string] [required]
  14. --sheetId, -s ID of the sheet to run the query on; the value after
  15. `#gid=` in the spreadsheet URL [string] [default: "0"]
  16. --sheetName, -n Name of the sheet to run the query on [string]

Prior art

License

MIT