Downloads data from a Google Sheets worksheet and stores in a local JSON file -- command-line JavaScript/node

fetch-user-records-from-google-sheets.js

/**
 * 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-sheets-keys.js

// 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';

users.csv