Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
JAVASCRIPT & GOOGLE SHEETS: a node/javascript command-line script that downloads data from a worksheet in Google Sheets and stores in a local JSON file
/**
* A script that:
* 1. loads Google Sheets API keys and spreadsheet IDs (see GOOGLE_API_KEY)
* 2. connects to the identifed Google Sheet
* 3. grabs the data from a named Worksheet (see SHEET_TO_GET)
* 4. iterates over each row (see processUserRows())
* 5. puts them into a JSON structure
* 6. and writes that out to a file (see FILE_NAME)
*
* To run this script, I have this in my package.json:
*
* "scripts": {
* "get-users-from-sheets": "node -r esm fetch-user-records-from-google-sheets.js",
* ...
* }
* then simply run this command from within your project directory: `npm run get-users-from-sheets`
*
* The format of the "my-users" worksheet is in the data in the file 'users.csv' later in this gist.
* Note: this text file uses COMMA (,) characters to separate the field values.
*
* Copy the data from 'users.csv' into a Google Sheet and **RENAME** THE SHEET
* (worksheet tab at bottom of the screen) TO BE 'my-users'
*
* The format of 'google-sheets-keys.js' is also in a file later in this gist.
*
*
*/
const fs = require('fs');
const { extractSheets } = require('spreadsheet-to-json');
const { CONFIG_SHEET_ID, GOOGLE_API_KEY } = require('./google-sheets-keys');
const SHEET_TO_GET = 'my-users';
const FILE_NAME = './output/users.json';
const processUserRows = (data) => {
let fields = [];
let i = 0;
let records = Object.values(data)[0];
do {
let curRecord = records[i]; // grab a row from the spreadsheet
if (
!curRecord['email'] ||
!curRecord['displayName'] ||
(!curRecord['firstName'] && !curRecord['lastName'])
) {
console.log(
`ERROR: row #${i + 2} :: missing email(${curRecord['email']}) ` +
`or displayName(${curRecord['displayName']}) or ` +
`first/last name((${curRecord['firstName']})(${curRecord['lastName']}))`
);
process.exit(1);
}
fields.push({
firstName: curRecord['firstName'],
lastName: curRecord['lastName'],
displayName:
curRecord['displayName'] ||
`${curRecord['firstName']} ${curRecord['lastName']}`,
email: curRecord['email'].toLowerCase(),
role: curRecord['role'],
});
} while (++i < records.length);
return fields;
};
const handleSheet = async (sheetName) => {
console.log('** Get sheet: ', sheetName);
let results = await extractSheets(
{
spreadsheetKey: CONFIG_SHEET_ID,
credentials: GOOGLE_API_KEY,
sheetsToExtract: [sheetName],
},
);
results = processUserRows(results);
return { users: results };
};
handleSheet(SHEET_TO_GET)
.then((sheetData) => {
fs.writeFileSync(FILE_NAME, JSON.stringify(sheetData, null, 2), (error) => {
if (error) throw error;
});
console.log('DONE - ${sheetData["users"].length} records output to: ', fname);
})
.catch((ex) => console.error('EXCEPTION with handleSheet()', ex.message));
// Google API key created inside the associated GCP project >> Google Sheets API
export const GOOGLE_API_KEY = 'AIxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxC';
// ID of the Google Sheets document we want to read from.
// Get this ID from the browser when you have the sheet open. The URL will look like:
// https://docs.google.com/spreadsheets/d/18xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx0/edit#gid=0
export const CONFIG_SHEET_ID = '18xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx0';
firstName lastName displayName email role
Al Albertson Al Albertson aa@test.ca Manager
Bruce Bixby Bruce Bixby bb@test.ca Manager
Cindy Cement Cindy Cement-Wilson ccw@test.ca Staff
Darma Derby Darma Sue Derby dsd@test.ca Manager
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment