Last active
August 6, 2020 06:06
-
-
Save kination/42e82921522a14dee5c61d963587e875 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
const fs = require('fs') | |
const readline = require('readline') | |
const { google } = require('googleapis') | |
// 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' | |
const SAMPLE_SPREADSHEET_ID = '1lvtD-DPzNiy8iih9qgCcQoyyLBoCmbFgJ6DRdNR2hMk' | |
const SAMPLE_RANGE_NAME = 'sheet-1!A2:E5' | |
// Load client secrets from a local file. | |
fs.readFile('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), listMajors) | |
}) | |
/** | |
* 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) | |
}) | |
} | |
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: SAMPLE_SPREADSHEET_ID, | |
range: SAMPLE_RANGE_NAME, | |
}, (err, res) => { | |
if (err) { | |
return console.log('The API returned an error: ' + err) | |
} | |
const rows = res.data.values | |
if (rows.length) { | |
// Print columns A and E, which correspond to indices 0 and 4. | |
const ja = {} | |
const en = {} | |
rows.map((row) => { | |
ja[row[0]] = row[2] | |
en[row[0]] = row[3] | |
}) | |
console.log(ja) | |
fs.writeFile("ja.json", JSON.stringify(ja, null, '\t'), (err, result) => { }); | |
fs.writeFile("en.json", JSON.stringify(en, null, '\t'), (err, result) => { }); | |
} else { | |
console.log('No data found.') | |
} | |
}) | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment