Uses the google-spreadsheet library to fetch data.
The final JSON is based on sheets names and column titles and finally looks like this :
This can be useful when you want people edit spreadsheets and need to work with the data.
npm i --save spreadsheet-to-json
extractSheets can use node callback pattern or async/await.
const extractSheets = ;// optional custom format cell functionconst formatCell = value;;
credentials key can either be a API_KEY
string or a service account
You can create an API key here : https://console.developers.google.com/apis/credentials
Be sure to restrict it to Google Drive API
Google service account
Create a credentials.json file for your app here : https://console.developers.google.com/
- create a new project
- enable the Drive API
- in credentials, select create new credentials then service account and save the generated JSON. (privately)
- then give the JSON contents to the
credentialsparameter in the
Share the target google spreadsheet with the
client_email from the credentials.json.
extractSheet should produce correct data ✓ sheet should have 5 rows ✓ row should have 4 properties ✓ row should have correct properties ✓ name should be Johnny ✓ id should be 1 formatCell ✓ names should not be equal ✓ name should be uppercased ✓ id should be 1 extractSheets should produce correct data ✓ data should have 3 sheets ✓ sheets should have correct names ✓ Private sheet should not be exported ✓ Customers should have 5 rows ✓ Customers properties names should equal id,name,location,Exotic_ Col-NAME ✓ Invoices should have 9 rows ✓ Invoices properties names should equal ref,amount ✓ Leads should have 9 rows ✓ Leads properties names should equal id,estimate,ref open invalid spreadsheet should return empty data ✓ invalid should have no rows columns with exotic names should be handled correctly ✓ Exotic column name should exist in output ✓ Exotic column name should be renamed in data ✓ Exotic column name should be handled correctly total: 21 passing: 21 duration: 1.9s