Account Look Up Local Script

index.js

/* eslint-disable guard-for-in */
const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');

const ACCOUNT_IMAGE_URL = 'https://www.gstatic.com/images/icons/material/system_gm/1x/account_circle_black_18dp.png';
const ACCOUNTS_SHEET_URL = 'https://docs.google.com/spreadsheets/d/16BDMJisBWYtUwHu_aTAsXgvnuMVCNr3vTMQvKeUIBsk/edit?ts=5e79672f#gid=1366792614';
const ACCOUNTS_SHEET_ID = '16BDMJisBWYtUwHu_aTAsXgvnuMVCNr3vTMQvKeUIBsk';

const CLUSTER_RANGES = {
  'CLUSTER1': 'Sheet1!C1:D27',
  'CLUSTER2': 'Sheet1!E1:F27',
  'CLUSTER3': 'Sheet1!G1:H27',
  'CLUSTER4': 'Sheet1!I1:J27',
};

const SKILL_ROWS = {
  'Infra': 7,
  'Hybrid': 8,
  'Data&Analytics': 9,
  'Data Management': 10,
  'Security': 11,
  'Networking': 12,
  'AI / ML': 13,
  'G Suite': 14,
  'SAP': 15,
};

const ACCOUNT_MANAGER = 'ACCOUNT MANAGER';
const ENGINEER = 'ENGINEER';
// If modifying these scopes, delete token.json.
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
const TOKEN_PATH = 'token.json';

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

  try {
    const token = fs.readFileSync(TOKEN_PATH);
    oAuth2Client.setCredentials(JSON.parse(token));
    return oAuth2Client;
  } catch (err) {
    const authorizedClient = await getNewToken(oAuth2Client);
    return authorizedClient;
  }
}

/**
 * Get and store new token after prompting for user authorization, and then
 * execute the given callback with the authorized OAuth2 client.
 * @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
 * @return {google.auth.OAuth2} The authorized client.
 */
async function getNewToken(oAuth2Client) {
  const authUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES,
  });
  console.log('Authorize this app by visiting this url:', authUrl);
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  });

  const code = await rl.questionAsync('Enter the code form that page here: ');
  rl.close();
  const {tokens} = await oAuth2Client.getToken(code);
  oAuth2Client.setCredentials(tokens);
  fs.writeFileSync(TOKEN_PATH, JSON.stringify(tokens));
  console.log('Token stored to', TOKEN_PATH);
  return oAuth2Client;
}

/**
 * Looks up the appropriate contact name.
 * @param {*} sheets The sheets service.
 * @param {String} account The company name.
 * @param {String} skill The specialization.
 * @param {String} role Must be either 'Account Manager' or 'Engineer'.
 */
async function lookupContact(sheets, account, skill, role) {
  // TODO: Refactor to return cluster values to save a 2nd read
  // in getContactName.
  const cluster = await getCluster(sheets, account);
  console.log(`Cluster: ${cluster}`);
  if (cluster === '') {
    // Error messages should be displayed in error card.
    console.error(`Cluster not found.`);
    return;
  }
  const skillIndex = getSkillRow(skill);
  if (skillIndex == -1) {
    console.error(`Skill not found.`);
    return;
  }
  return getContactName(sheets, cluster, role, skillIndex);
}

/**
 * Looks up which cluster the company belongs to
 * @param {sheets_v4.Sheets} sheets The sheets service.
 * @param {String} account The company name.
 * @return {String} The cluster name.
 */
async function getCluster(sheets, account) {
  // eslint-disable-next-line guard-for-in
  for (clusterKey in CLUSTER_RANGES) {
    const range = CLUSTER_RANGES[clusterKey];
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: ACCOUNTS_SHEET_ID,
      range,
    });
    const values = response.data.values;
    // TODO: change to map & filter
    for (let i = 0; i < values.length; i++) {
      const row = values[i];
      if (row[0] == account || row[1] == account) {
        return clusterKey;
      }
    }
    console.log(`cluster values: ${JSON.stringify(values)}`);
  }
  return '';
}

/**
 * Returns the row index for the given specialization.
 * This maps to Column A in the accounts Sheet.
 * @param {String} skill The specialization
 * @return {Integer} the index for the specialization or -1 if not found.
 */
function getSkillRow(skill) {
  for (const key in SKILL_ROWS) {
    if (skill == key) {
      return SKILL_ROWS[skill];
    }
  }
  return -1;
}

/**
 * Looks up the appropriate contact name in the cluster
 * @param {*} sheets The Sheets service.
 * @param {String} clusterKey The cluster key name.
 * @param {String} role Must be either 'Account Manager' or 'Engineer'
 * @param {Integer} skillIndex the index for the specialization.
 */
async function getContactName(sheets, clusterKey, role, skillIndex) {
  const range = CLUSTER_RANGES[clusterKey];
  const response = await sheets.spreadsheets.values.get({
    spreadsheetId: ACCOUNTS_SHEET_ID,
    range,
  });
  const values = response.data.values;
  const roleIndex = (role == ACCOUNT_MANAGER) ? 0 : 1;
  // Skills are indexed at 1.
  return values[skillIndex - 1][roleIndex];
}

const main = async () => {
  const credentials = fs.readFileSync('credentials.json');
  const auth = await authorize(JSON.parse(credentials));
  const sheets = google.sheets({version: 'v4', auth});

  const account = 'YouTube';
  const role = 'Engineer';
  const skill = 'Networking';
  console.log(await lookupContact(sheets, account, skill, role));
};

main();