npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

cuba-sheet

v0.2.1

Published

Google Sheets + SQL = JSON

Downloads

11

Readme

cuba-sheet npm Version Build Status Bundle Size

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

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

License

MIT