gsheets-connector.js

import { readFile, writeFile, mkdir } from "mz/fs";
import readline from "mz/readline";
import { promisify } from "util";
import { google } from "googleapis";

// If modifying these scopes, delete token.json.
const SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"];
const TOKEN_PATH = "token.json";

export class GsheetsConnector {
  async ensureAuthorized() {
    // Load client secrets from a local file.
    const credentials = JSON.parse(await readFile("credentials.json"));
    // Authorize a client with credentials, then call the Google Sheets API.
    this.auth = await this.authorize(credentials);
  }

  async runExample() {
    await this.ensureAuthorized();
    return await this.listMajorsExample();
  }

  /**
   * Create an OAuth2 client with the given credentials, and then execute the
   * given callback function.
   *
   * @param {Object} credentials The authorization client credentials.
   */
  async authorize(credentials) {
    const { client_id, client_secret, redirect_uris } = credentials.installed;
    const oAuth2Client = new google.auth.OAuth2(
      client_id,
      client_secret,
      redirect_uris[0],
    );

    let token = {};

    // Check if we have previously stored a token.
    try {
      token = JSON.parse(await readFile(TOKEN_PATH));
    } catch (err) {
      const authUrl = oAuth2Client.generateAuthUrl({
        access_type: "offline",
        scope: SCOPES,
      });
      const code = await this.getNewCode(authUrl);
      try {
        const res = await oAuth2Client.getToken(code);
        token = res.tokens;
        await this.storeToken(token);
      } catch (err) {
        console.log("Error while trying to retrieve access token");
        throw err;
      }
    }

    oAuth2Client.credentials = token;
    return oAuth2Client;
  }

  /**
   * Get and store new code after prompting for user authorization
   *
   * @param {string} authUrl The Auth URL generated by oAuth2Client.generateAuthUrl()
   */
  async getNewCode(authUrl) {
    console.log(`Authorize this app by visiting this url: ${authUrl}`);
    const rl = readline.createInterface({
      input: process.stdin,
      output: process.stdout,
    });
    const code = await rl.question("Enter the code from that page here: ");
    rl.close();
    return code;
  }

  /**
   * Store token to disk be used in later program executions.
   *
   * @param {Object} token The token to store to disk.
   */
  async storeToken(token) {
    /*
    try {
      await mkdir(TOKEN_DIR);
    } catch (err) {
      if (err.code != "EEXIST") throw err;
    }
    */
    await writeFile(TOKEN_PATH, JSON.stringify(token));
    console.log(`Token stored to ${TOKEN_PATH}`);
  }

  async getValues(spreadsheetId, range) {
    const auth = this.auth;
    try {
      const sheets = google.sheets("v4");
      const getValues = promisify(sheets.spreadsheets.values.get);
      const valueRenderOption = "UNFORMATTED_VALUE";
      const res = await getValues({
        auth,
        spreadsheetId,
        range,
        valueRenderOption,
      });
      return res.data.values;
    } catch (err) {
      console.log(`The API returned an error: ${err}`);
      throw err;
    }
  }

  /**
   * Prints the names and majors of students in a sample spreadsheet:
   * @see https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
   * @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
   */
  async listMajorsExample() {
    const spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
    const range = "Class Data!A2:E";
    const rows = await this.getValues(spreadsheetId, range);
    console.log("rows", rows);
    if (rows.length === 0) {
      console.log("No data found.");
      return;
    }
    console.log("Name, Major:");
    for (let i = 0; i < rows.length; i++) {
      const row = rows[i];
      // Print columns A and E, which correspond to indices 0 and 4.
      console.log(`${row[0]}, ${row[4]}`);
    }
  }
}