/**
@overview
if there is a change in firebase offer letters data, then
1) read the offer letter data from firebase
2) read all the google sheet data
3) update the google sheet from the offer letter data
4) write the sheet back out to google
*/
const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');
/*----------------------------------------------------------------------------------*/
var public_sheet = true;
/*
Summer 2019 Assignments
https://docs.google.com/spreadsheets/d/1BmZW1VFAnKXjb2F3KJu975VOtvuKrQ2dkRzfGItVX7Y/edit?
Summer 2019 Courses
https://docs.google.com/spreadsheets/d/13vauktl1WIQZWRz3aMiFzmmHnyxa-C-T8Hk4NS9ot-w/edit?usp=sharing
curl "https://docs.google.com/spreadsheets/d/1BmZW1VFAnKXjb2F3KJu975VOtvuKrQ2dkRzfGItVX7Y/export?exportFormat=csv" >offers.csv
curl "https://docs.google.com/spreadsheets/d/13vauktl1WIQZWRz3aMiFzmmHnyxa-C-T8Hk4NS9ot-w/export?exportFormat=csv" >courses.csv
FAll 2019:
assignements:
18s81D_VvfvDtJsvupZCUDzRiOe2f8b9VCPju_T9dIO0
1BZsipd6fqPoajaXlyTHXJ_MkkicxyOGi9WzikdKWdhc
courses:
srping 2020
courses: 1e-7OHDslvqujIa-bSE1q3j6C73OJZvNyo4yPatkRNsI
offers: 1cnElHgPc0mdlRLR3PyWwaCPHw0ku9ZNtsjEKxZUjIo8
*/
// TODO: read the sheet id from jekyll/_data/google/google_data.yaml and services_config
const offersSheetId = '1B7axewdXbPBwtBMtnXAqz_dH9PpNMxqb_6UIOHWDvPE';
var term_key = '2019_fall';
/*
// API key from the Developer Console
// We don't neet client id for a public sheet
//client id for http://localhost:4000
// Web client 1
var CLIENT_ID = '1012501297519-jjjfov08r9ua1pmsvbeq6jcgg6le4u2i.apps.googleusercontent.com';
var CLIENT_SECRET = 'hjrvIiGONB3gPN_P6m6J_EH9';
var API_KEY = 'AIzaSyCSnlRc_ZrIjeAQMfbPAEdnEOQjQq-ldAU';
// Array of API discovery doc URLs for APIs used by the quickstart
var DISCOVERY_DOCS = ["https://sheets.googleapis.com/$discovery/rest?version=v4"];
// Authorization scopes required by the API; multiple scopes can be
// included, separated by spaces.
// var SCOPES = "https://www.googleapis.com/auth/spreadsheets.readonly";
var SCOPES = "https://www.googleapis.com/auth/spreadsheets";
*/
const TOKEN_PATH = 'token.json';
/*----------------------------------------------------------------------------------*/
// Load client secrets from a local file.
fs.readFile('google_api_client_credentials.json', (err, content) => {
if (err) return console.log('Error loading client secret file:', err);
// Authorize a client with credentials, then call the Google Sheets API.
authorize(JSON.parse(content), getOffersSheetData);
});
/**
* 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 google.auth.OAuth2(
client_id, client_secret, redirect_uris[0]);
// Check if we have previously stored a token.
fs.readFile(TOKEN_PATH, (err, token) => {
if (err) return getNewToken(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 getNewToken(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 console.error('Error while trying to retrieve access token', err);
oAuth2Client.setCredentials(token);
// Store the token to disk for later program executions
fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
if (err) return console.error(err);
console.log('Token stored to', TOKEN_PATH);
});
callback(oAuth2Client);
});
});
}
/**
* 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.
*/
function listMajors(auth) {
const sheets = google.sheets({version: 'v4', auth});
sheets.spreadsheets.values.get({
spreadsheetId: '1B7axewdXbPBwtBMtnXAqz_dH9PpNMxqb_6UIOHWDvPE',
range: 'Sheet1',
}, (err, res) => {
if (err) return console.log('The API returned an error: ' + err);
const rows = res.data.values;
if (rows.length) {
console.log('Name, Major:');
// Print columns A and E, which correspond to indices 0 and 4.
rows.map((row) => {
console.log(`${row[0]}, ${row[4]}`);
});
} else {
console.log('No data found.');
}
});
}
/*----------------------------------------------------------------------------------*/
function sheetDataToRowObjects(raw_sheet_data, sheet_rows) {
// take data as array of rows of values
// turn into an array of row hashmap objects, mapped by header values in first row
var sheet_rows = [];
var sheet_headers = null;
if (raw_sheet_data.values.length > 1) {
sheet_headers = raw_sheet_data.values[0];
for (i = 1; i < raw_sheet_data.values.length; i++) {
var row = raw_sheet_data.values[i];
var row_object = {};
for (j = 0; j < row.length; j++) {
row_object[sheet_headers[j]] = row[j];
} // next column
sheet_rows.push(row_object);
} // next row
return {
"headers": sheet_headers,
"rows": sheet_rows
};
}
else {
console.log('No data found.');
return null;
}
}
/*----------------------------------------------------------------------------------*/
// major dimension is ROWS by default
// https://developers.google.com/sheets/api/guides/values
function getGoogleData() {
getOffersSheetData(); // this will call getAssignmentSheetData for offers too
}
/*----------------------------------------------------------------------------------*/
function getOffersSheetData(auth) {
const sheets = google.sheets({version: 'v4', auth});
sheets.spreadsheets.values.get({
"spreadsheetId": offersSheetId,
"range": 'Sheet1',
},function(err,response) {
// console.log("got sheet response: " + err+ " " + JSON.stringify(response,null,"\t"));
// turn into an array of row objects
var sheet_results = sheetDataToRowObjects(response.data);
// console.log("got sheet results: "+ JSON.stringify(sheet_results,null,"\t"));
if (null != sheet_results) {
offers_headers = sheet_results.headers;
offers_rows = sheet_results.rows;
// console.log(JSON.stringify(offers_rows,null,4));
console.log(offers_headers);
console.log(offers_headers.length);
dataLoaded();
}
});
} // function getAssignmentSheetData()
/*----------------------------------------------------------------------------------*/
function putSheetData() {
var raw_sheet_data = [];
raw_sheet_data.push(offers_headers);
console.log(offers_headers);
console.log(offers_headers.length);
for (var i = 0; i < offers_rows.length; i++) {
var row_data = JSON.parse(JSON.stringify(offers_rows[i]));
// console.log(row_data.fsuid);
var row = [];
for (h = 0; h < offers_headers.length; h++) {
if ("course" == offers_headers[h] && "TA" == row_data.appointment) {
var fsuid = row_data.fsuid;
var old_course = row_data.course;
var course = teachAssts[fsuid].course;
if (course == no_course_id) {
row_data.course = "";
}
else {
row_data.course = course.split(".")[0];
}
}
row.push(row_data[offers_headers[h]]);
}
raw_sheet_data.push(row);
}
// console.log(raw_sheet_data);
// major dimension is ROWS by default
var body = {
"values": raw_sheet_data
};
// var init_params = {
// apiKey: API_KEY,
// discoveryDocs: DISCOVERY_DOCS,
// scope : SCOPES
// };
//
// // clientId and scope are optional if auth is not required.
//
// if (!public_sheet) {
// init_params.clientId = CLIENT_ID;
// init_params.scope = SCOPES;
// }
//
//
// gapi.client.init(init_params).then(function () {
// // Listen for sign-in state changes.
// // ignore authentication stuff for public sheet
// if (!public_sheet) {
// gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);
//
// // Handle the initial sign-in state.
// updateSigninStatus(gapi.auth2.getAuthInstance().isSignedIn.get());
// authorizeButton.onclick = handleAuthClick;
// signoutButton.onclick = handleSignoutClick;
// }
// console.log("gapi client inited");
function convertToNumberingScheme(number) {
// https://codereview.stackexchange.com/questions/16124/implement-numbering-scheme-like-a-b-c-aa-ab-aaa-similar-to-converting
var baseChar = ("A").charCodeAt(0),
letters = "";
do {
number -= 1;
letters = String.fromCharCode(baseChar + (number % 26)) + letters;
number = (number / 26) >> 0; // quick `floor`
} while (number > 0);
return letters;
}
var max_column_index = offers_headers.length;
var rows_length = raw_sheet_data.length;
var range_string = "A1:" + convertToNumberingScheme(max_column_index) + rows_length;
console.log("saved range_string: " + range_string);
gapi.client.sheets.spreadsheets.values.update({
"spreadsheetId": offersSheetId,
"range": range_string,
"valueInputOption": "RAW",
"resource": body
})
.then(function(response) {
console.log("put sheet response");
console.log(response);
},
function(error) {
console.log("error: " + JSON.stringify(error, null, 2));
});
// }, function(error) {
// console.log("error: " + JSON.stringify(error, null, 2));
// });
}
/*----------------------------------------------------------------------------------*/