GetSheetDone
Helper library to pull data from google spreadsheets.
Live demo: https://giladaya.github.io/get-sheet-done/
Caveats
This is a simple library intended to provide a quick-and-dirty way to only read data from a Google spreadsheet.
The document you read from must be publicly published for this to work and the entire sheet data is fetched.
If you need something more sophisticated, take a look at the Google Sheets API
Usage
Preparation
- In the spreadsheet,
file -> publish to web
- Note the document key in the URL
In browser
See /example/src
for a simple browser-based demo.
Note that this module relies on Promise
and the fetch
API to be available, so make sure to use polyfills if needed.
With a package loader
Install from npm:
npm install --save get-sheet-done
Use in code:
; GetSheetDone
On the server
Just require('node-fetch')
in your project. Example:
globalfetch = ;const GetSheetDone = ; GetSheetDone ;
Api
There are three functions that return a promise and fetch the data from a published spreadsheet.
Each function gets as arguments the document key (from the document url) and optinally the sheet index (first sheet has index 1).
The functions differ in the way they parse and return the data:
Raw
GetSheetDone
Get the data as a raw array of arrays (rows). Suitable for spreadsheets that only contain values.
Example
This sheet:
A | B | C | D | |
1 | 22 | 24 | 26 | 20 |
2 | 31 | 32 | 37 | 36 |
3 | 11 | 14 | 19 | 12 |
Will result in this data:
title: "Sheet1" updated: "2017-07-30T07:11:40.056Z" data: "22" "24" "26" "20" "31" "32" "37" "36" "11" "14" "19" "12"
Labeled Columns
GetSheetDone
Get the data as an array of objects, each representing a row. The keys are taken from the first row in the spreadsheet which is assumed to contain column headers.
Note that all labels are converted to lower case and the only non-alphanumeric characters that are kept are periods and hyphens, so if the original label was Lorem-ipsum dolor.sit:amet_blah
, the result labels would be: lorem-ipsumdolor.sitametblah
.
Example
This sheet:
A | B | C | D | |
1 | Q1 | Q2 | Q3 | Q4 |
2 | 22 | 24 | 26 | 20 |
3 | 31 | 32 | 37 | 36 |
4 | 11 | 14 | 19 | 12 |
Will result in this data:
title: "Sheet1" updated: "2017-07-30T07:11:40.056Z" data: q1: "22" q2: "24" q3: "26" q4: "20" q1: "31" q2: "32" q3: "37" q4: "36" q1: "11" q2: "14" q3: "19" q4: "12"
Labeled Columns and rows
GetSheetDone
Get the data as an object of objects, each representing a row. The first column in the spreadsheet is assumed to contain row headers, and the first row in the spreadsheet is assumed to contain column headers.
Note that all column labels are converted to lower case and the only non-alphanumeric characters that are kept are periods and hyphens, so if the original label was Lorem-ipsum dolor.sit:amet_blah
, the result labels would be: lorem-ipsumdolor.sitametblah
.
Row labels remain unchanged.
Example
This sheet:
A | B | C | D | E | |
1 | Q1 | Q2 | Q3 | Q4 | |
2 | UK | 22 | 24 | 26 | 20 |
3 | US | 31 | 32 | 37 | 36 |
4 | AU | 11 | 14 | 19 | 12 |
Will result in this data:
title: "Sheet1" updated: "2017-07-30T07:11:40.056Z" data: UK: q1: "22" q2: "24" q3: "26" q4: "20" US: q1: "31" q2: "32" q3: "37" q4: "36" AU: q1: "11" q2: "14" q3: "19" q4: "12"
Development
- Clone this repo
npm install
npm start
- Go to
localhost:3210
- See
package.json
for other available scripts