@laboratoria/fetch-gsheets

fetch-gsheets is a command line tool used to retrieve data from Google Spreadsheets.

Installation

Global install:

npm install --global @laboratoria/fetch-gsheets

# the same thing but using shortcuts ;-)
npm i -g @laboratoria/fetch-gsheets

As project devDependency:

npm install --save-dev @laboratoria/fetch-gsheets

# the same thing but using shortcuts ;-)
npm i -D @laboratoria/fetch-gsheets

Usage

Usage: fetch-gsheets [options] <selector-1> [...<selector-N>]

Command expects one or more "selectors" as arguments.

Each selector is a string with the following format:

'<spreadSheetId>!<sheetId>!<range>'

For example:

fetch-gsheets '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!SCL!A1:I'

In this example

* spreadSheetId: '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB'
* sheetId: 'SCL'
* rangeL 'A1:I'

Options:

-c, --credentials Path to OAuth Client ID JSON file. Default: credentials.json
-h, --help        Show this help.
-v, --version     Show fetch-gsheets version.

For more info please check https://github.com/Laboratoria/fetch-gsheets

Authentication

Before you use the fetch-gsheets command you will need to create a project in the Google Cloud Console, enable access to the Google Sheets API for that project and create an OAuth Client ID. After creating a project in Google Cloud and enabling access to the Google Sheets API, to get an OAuth Client ID (the credentials for fetch-gsheets), follow these steps:

1. Go to https://console.cloud.google.com/apis/credentials.

   

2. Select Create credentials, then OAuth Client ID.

   

3. Pick other in the application type radio selector, give a name to the client ID (something for you to remember what this client id is for) and click on the Create button.

   

4. Dismiss the confirmation dialog after clicking

   

5. Finally click on the download button next to the newly generated OAuth 2.0 client ID in the list.

   

This file is expected to be an OAuth 2.0 Client ID. Something like:

{
  "installed": {
    "client_id":"557161231987-cjdfhbhatdov4idv3irt6js4jkv9248a.apps.googleusercontent.com",
    "project_id":"your-amazing-project",
    "auth_uri":"https://accounts.google.com/o/oauth2/auth",
    "token_uri":"https://www.googleapis.com/oauth2/v3/token",
    "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
    "client_secret":"asd7123-abcbdyasd123ertg",
    "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]
  }
}

By default, fetch-gsheets will look for a file called credentials.json in the current working directory (that is the directory from where fetch-gsheets was invoked).

You can also specify a different path to the credentials file using the -c (short version) or --credentials (long version) options.

fetch-gsheets \
  -c ./path/to/oauth-client-id.json \
  '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!SCL!A1:I'

fetch-gsheets \
  --credentials ./path/to/oauth-client-id.json \
  '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!SCL!A1:I'

Sign in

When fetch-gsheets runs, it checks if an auth token already exists (in the same dir as the credentials file - that's the OAuth Client ID JSON file). If it does not exist, you will be prompted to authorize the app (the fetch-gsheets command) as follows:

$ fetch-gsheets \
  -c ./Downloads/client_secret_1234567890-abcdeovs2hetkgmpm4mui70283pth3a2.apps.googleusercontent.com.json \
  '1Tviny8HzskBKP0HDKXoSClqyHsvQTO0XsWnyKWZGvJA!General!A1:H5'

Authorize this app by visiting this url: https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets.readonly&response_type=code&client_id=897165371071-hgj5qovs2hetkgmpm4mui70283pth3a2.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob
Enter the code from that page here:

You should open the link in a web browser and follow the steps on the screen:

Finally, enter the token back in the console:

Enter the code from that page here: xxxxx
Token stored to /home/lupo/Downloads/token.json
[
  // data goes here
]

Subsequent calls to fetch-gsheets will not prompt for authentication and will run directly.

$ fetch-gsheets \
  -c ./Downloads/client_secret_1234567890-abcdeovs2hetkgmpm4mui70283pth3a2.apps.googleusercontent.com.json \
  '1Tviny8HzskBKP0HDKXoSClqyHsvQTO0XsWnyKWZGvJA!General!A1:H5'
[
  // data goes here
]

Examples

NOTE: In the examples below we assume there is a credentials.json file with a service account key in the directory where we are invoking fetch-gsheets. This allows for no -c or --credentials options and thus simpler examples.

Fetch a single range from a single spreadsheet:

fetch-gsheets '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!SCL!A1:I'

Fetch multiple ranges from a single spreadsheet:

fetch-gsheets \
  '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!SCL!A1:I' \
  '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!LIM!A1:I' \
  '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!CDMX!A1:I'

Fetch multiple ranges from multiple spreadsheets:

fetch-gsheets \
  '1xH90agOPuieIAAxSaP1IYx99-G64OP937GhHJs19q2O!SCL!B4:H60' \
  '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!Sheet1!A1:I' \
  '2vG81bkFMfroZFNmCbD9SQcUo-Wed08goNrJB9Yyl9AB!Sheet3!A1:X'