Skip to content

Instantly share code, notes, and snippets.

@kination
Last active August 6, 2020 06:06
Show Gist options
  • Save kination/42e82921522a14dee5c61d963587e875 to your computer and use it in GitHub Desktop.
Save kination/42e82921522a14dee5c61d963587e875 to your computer and use it in GitHub Desktop.
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