gistfile1.txt

#!/usr/bin/env node

require('./colorTheme.js');


function stringify(obj) {
    return JSON.stringify(obj, undefined, 4);
}


const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');
const OAuth2Client = google.auth.OAuth2;
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const TOKEN_PATH = 'credentials.json';

var authContent;

// Load client secrets from a local file.
fs.readFile('client_secret.json', (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
    // Authorize a client with credentials, then call the Google Drive API.
    authContent = JSON.parse(content);
    authorize(authContent, getRUSResources);
    // authorize(JSON.parse(content), getRUSResources);
});

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

    // Check if we have previously stored a token.
    fs.readFile(TOKEN_PATH, (err, token) => {
        if (err) return getAccessToken(oAuth2Client, callback);
        oAuth2Client.setCredentials(JSON.parse(token));
        callback(oAuth2Client);
    });
}

/**
 * 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.
 * @param {getEventsCallback} callback The callback for the authorized client.
 */
function getAccessToken(oAuth2Client, callback) {
    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,
    });
    rl.question('Enter the code from that page here: ', (code) => {
        rl.close();
        oAuth2Client.getToken(code, (err, token) => {
            if (err) return callback(err);
            oAuth2Client.setCredentials(token);
            // Store the token to disk for later program executions
            fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
                if (err) console.error(err);
                console.log('Token stored to', TOKEN_PATH);
            });
            callback(oAuth2Client);
        });
    });
}


function buildKeyedObject(data, indexOffset) {
    var sortedData = {},
        keys = data.shift();

    data.forEach(function(obj, rowIndex) {
        var nameKey = `${obj[1].trim()},${obj[2].trim()}`;

        sortedData[nameKey] = {
            data : obj,
            // keys : keys,
            rowIndex : rowIndex + indexOffset
        }
    });


    return sortedData;
}

const rusSheetId = '1AC_vDe3Zbqp42BOpZ5OSBOnmTI-LBxCnor4zhkFrYzY',
      wbSheetId = '1nBsgFkmKfPmVKxZOB1e4W5kqCeQA3ddCzyOg5gSOGbA'; // COPY / dev
      // wbSheetId  = '1GE5kdLYf-dBRFBxDi7NjkD31GoZ33gaJPceampE3nAA'; // PROD / live


var rusRecordObjects;


async function getRUSResources(auth) {
    var sheets = google.sheets('v4');

    sheets.spreadsheets.values.batchGet(
        {
            auth          : auth,
            spreadsheetId : rusSheetId,
            ranges        :[
                "'Resource Summary'!A1:AA300"
            ]
        }, 
        function(err, response) {
            if (err) {
                console.log('The API returned an error: ' + err);
                return;
            }

            // debugger;
            rusRecordObjects = buildKeyedObject(response.data.valueRanges[0].values, 1);
            // console.log(stringify(rusRecordObjects))
            // console.log('done getRUSResources()');

            authorize(authContent, getWBResources);
        }
    );
}


/**

A1 notation
Some API methods require a range in A1 notation. This is a string like Sheet1!A1:B2, that refers to a group of cells in the spreadsheet, and is typically used in formulas. For example, valid ranges are:

Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.
Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.


sheets.spreadsheets.values.append({
    auth: auth,
    spreadsheetId: 'spreadshiiitID',
    range: 'id_clients!A1:B2',
    valueInputOption: "USER_ENTERED",
    resource: {
      values: [
        ["Void", "Canvas", "Website"],
        ["Paul", "Shan", "Human"]
      ]
    }
  }, function(err, response) {
    if (err)


Batch update:
https://developers.google.com/sheets/api/guides/batchupdate

*/


var wbRecordObjects,
    wbRawValues;

function getWBResources(auth) {
    var sheets = google.sheets('v4'),
        wbRange   = "'PSA Resources - Contacts'!A1:AM300";

    sheets.spreadsheets.values.batchGet(
        {
            auth          : auth,
            spreadsheetId : wbSheetId,
            ranges        :[
                wbRange
            ]
            
        }, 
        function(err, response) {
            if (err) {
                console.log('The API returned an error: ' + err);
                return;
            }

            var updates = [],
                appends = [];

            var wbValues = response.data.valueRanges[0].values;
            wbRawValues = wbValues;

            var keys     = wbValues.shift();



            wbRecordObjects = buildKeyedObject(wbValues, 2);
            // console.log(stringify(wbRecordObjects));

            var wbKeys = Object.keys(wbRecordObjects),
                rusKeys = Object.keys(rusRecordObjects);

            // Check to see if names in workbook match all names in Resource Utilization
            wbKeys.forEach(function(wbKey) {
                if (rusKeys.indexOf(wbKey) == -1) {
                    console.log(`${wbKey} not found in RUS!`);
                }
            });

            // Check to see if names in Resource Utilization match the workbook
            rusKeys.forEach(function(rusKey) {
                var rusObject = rusRecordObjects[rusKey],
                    rusObjectStatus = rusObject.data[3];

                var wbObject = wbRecordObjects[rusKey];

                // Add records to appends queue if in RUS but not in Workbook
                if (wbKeys.indexOf(rusKey) == -1) { // NOT found in workbook
                    // Check to see if folks are active.
                    if (rusObjectStatus != 'MCUS Alumni' && rusObjectStatus != 'MCRO Alumni') {
                        console.log(`${rusKey}`.r, `not found in workbook!`);
                        // console.log('RUS Record'.bb);
                        // console.log(stringify(rusObject));
                        // console.log('WB Record'.rb);
                        // console.log(stringify(wbRecordObjects))
                        appends.push(rusObject);
                    }
                }

                if (wbObject) {
                    if (rusObjectStatus == 'MCUS Almuni' || rusObjectStatus == 'MCRO Alumni') {
                        wbObject.data[28] = 'Y';
                    }
                    wbObject.data[38] = rusObject[24];
                }

            });

            doWorkbookBatchUpdate();
            /**
            // TODO: 
            2) If resource is NOT active on RUS (Relationship column: marked under MCUS Alumni) but active on PSA
            PSA: column: Exclude from Resource Planner column should be be Y 

            3) If resource is active on RUS but not found on PSA
            Resource needs to be added to PSA
            Harvest project
            **/

            if (appends.length > 0) {
                appendRecordsToWBSheet(authContent, appends);
            }
        }
    );
}


function doWorkbookBatchUpdate() {
    var sheets = google.sheets('v4'),
        wbRange   = "'PSA Resources - Contacts'!A2:AM" + (wbRawValues.length - 1);

    authorize(authContent, (authClient) => {

        var request = {
            auth: authClient,

            spreadsheetId: wbSheetId, 

            // The A1 notation of a range to search for a logical table of data.
            // Values will be appended after the last row of the table.
            range: `'PSA Resources - Contacts'`,

            // How the input data should be interpreted.
            valueInputOption: 'USER_ENTERED', 
            data : wbRawValues
        }

        sheets.spreadsheets.values.batchUpdate(
            request, 
            function(err, response) {
                if (err) {
                    console.log('The API returned an error: ' + err);
                    return;
                }

                console.log('Done doing batch updates!')
            }
        );
    });
} 

function appendRecordsToWBSheet(authContent, appends) {

    /*  Reference Only
    var wbKeys = {
        "0": "Contact Record Type",
        "1": "First Name",
        "2": "Last Name",
        "3": "Salesforce User",
        "4": "Contact Resource Role",
        "5": "Permission Controls?",
        "6": "Staffing Permission",
        "7": "Timecard Entry Permission",
        "8": "Billing Permission",
        "9": "Currency",
        "10": "Region",
        "11": "Practice",
        "12": "Group",
        "13": "Work Calendar",
        "14": "Start Date",
        "15": "Is Resource",
        "16": "Is Resource Active",
        "17": "Salesforce License Type",
        "18": "Permission Set(s) to be assigned",
        "19": "Account Name",
        "20": "Account Currency",
        "21": "External Resource",
        "22": "Utilization Target",
        "23": "Allow Timecards Without Assignment",
        "24": "Default Cost Rate",
        "25": "Default Cost Rate is Daily Rate",
        "26": "Exclude From Missing Timecards",
        "27": "Exclude From Time Calculations",
        "28": "Exclude from Resource Planner",
        "29": "Address",
        "30": "Mailing City",
        "31": "Mailing State/Province",
        "32": "Mailing Zip/Postal Code",
        "33": "Mailing Country",
        "34": "GMT",
        "35": "Time Zone ",
        "36": "Start Date ",
        "37": "Contract End",
        "38": "ACTION ITEMS / NOTES \n(please add any information that may be useful for all users of this sheet each week; especially for contractors whose contract end dates are within 30 days)"
    }

    var rusKeys = {
        "0": "Skills",
        "1": "First Name",
        "2": "Last Name",
        "3": "Relationship",
        "4": "Type",
        "5": "Alumni Distribution List",
        "6": "Role",
        "7": "Dept",
        "8": "Job Title",
        "9": "Location",
        "10": "Time Zone",
        "11": "Engaged",
        "12": "Advanced Skills - Mastery",
        "13": "Mid Level Skills - Can add immediate value",
        "14": "Could Work Out... aka, Effective w/ Brushing up",
        "15": "Training",
        "16": "Start Date",
        "17": "Contract End",
        "18": "On the Bench",
        "19": "Possible Projects",
        "20": "Current Project/s",
        "21": "Current Project End Date \n(estimate)",
        "22": "Cost Rate",
        "23": "Weekly Cost Rate",
        "24": "ACTION ITEMS / NOTES \n(please add any information that may be useful for all users of this sheet each week; especially for contractors whose contract end dates are within 30 days)",
        "25": "",
        "26": "Info up-to-date"
    };
    */

    var values = [],
        employeeType = '';

    var groupMap = {
        'MCUS FTE'        : 'Employee',
        'MCUS PTE'        : 'Employee',
        'MCUS PE'         : 'Project Employee',
        'MCSRL FTE'       : 'RO Employee',
        'MCUS Contractor' : 'Contractor'
    }



    appends.forEach((obj) => {
        obj = obj.data;
        // console.dir(stringify(obj))
        values.push([
           "",// "0": "Contact Record Type",
           obj[1],//  "1": "First Name",
           obj[2], //  "2": "Last Name",
           "",//  "3": "Salesforce User",
           "",//  "4": "Contact Resource Role",
           "",//  "5": "Permission Controls?",
           "",//  "6": "Staffing Permission",
           "",//  "7": "Timecard Entry Permission",
           "",//  "8": "Billing Permission",
           "USD",//  "9": "Currency",
           "",//  "10": "Region",
           "", //  "11": "Practice",
           groupMap[obj[3]], //  "12": "Group",
           "", //  "13": "Work Calendar",
           obj[16], //  "14": "Start Date",
           "Y", //  "15": "Is Resource",
           "Y", //  "16": "Is Resource Active",
           "", //  "17": "Salesforce License Type",
           "", //  "18": "Permission Set(s) to be assigned",
           "Modus Create, Inc.", //  "19": "Account Name",
           "USD", //  "20": "Account Currency",
           "Y", //  "21": "External Resource",
           "100%", //  "22": "Utilization Target",
           "", //  "23": "Allow Timecards Without Assignment",
           obj[22], //  "24": "Default Cost Rate",
           "", //  "25": "Default Cost Rate is Daily Rate",
           "",//  "26": "Exclude From Missing Timecards",
           "", //  "27": "Exclude From Time Calculations",
           "N", //  "28": "Exclude from Resource Planner",
           "PLEASE ENTER!", //  "29": "Address",
           "PLEASE ENTER!", //  "30": "Mailing City",
           "PLEASE ENTER!", //  "31": "Mailing State/Province",
           "PLEASE ENTER!", //  "32": "Mailing Zip/Postal Code",
           "PLEASE ENTER!", //  "33": "Mailing Country",
           "PLEASE ENTER!", //  "34": "GMT",
           "PLEASE ENTER!", //  "35": "Time Zone ",
           obj[16], //  "36": "Start Date ",
           obj[17], //  "37": "Contract End",
           obj[24]  //  "38": "ACTION ITEMS / NOTES \n(ple
        ]);
    })

    console.log('Inserting...', appends.length, 'records');

    authorize(authContent, (authClient) => {
        var sheets = google.sheets('v4');

        var request = {
            auth: authClient,

            spreadsheetId: wbSheetId, 

            // The A1 notation of a range to search for a logical table of data.
            // Values will be appended after the last row of the table.
            range: `'PSA Resources - Contacts'`,

            // How the input data should be interpreted.
            valueInputOption: 'USER_ENTERED', 

            // How the input data should be inserted.
            insertDataOption: 'INSERT_ROWS', 

            resource: {
                values : values
            }
        };

        
        sheets.spreadsheets.values.append(
            request,
            function(err, response) {
                if (err) {
                    console.log('The API returned an error: ' + err);
                    return;
                }

                console.log('Done doing inserts!');

            }
        )
    });

}