Skip to content

Instantly share code, notes, and snippets.

@cognitom
Last active October 29, 2020 23:09
Show Gist options
  • Star 13 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save cognitom/2300754df04df3477261ff12ee36285c to your computer and use it in GitHub Desktop.
Save cognitom/2300754df04df3477261ff12ee36285c to your computer and use it in GitHub Desktop.
Unspaghetti version of Google Spreadsheet API example code
import {readFile, writeFile, mkdir} from 'mz/fs'
import readline from 'mz/readline'
import promisify from 'es6-promisify'
import google from 'googleapis'
import googleAuth from 'google-auth-library'
import clientSecret from './client_secret.json'
// If modifying these scopes, delete your previously saved credentials
// at ~/.credentials/sheets.googleapis.com-nodejs-quickstart.json
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
const HOME = process.env.HOME || process.env.HOMEPATH || process.env.USERPROFILE
const TOKEN_DIR = `${HOME}/.credentials/`
const TOKEN_PATH = `${TOKEN_DIR}sheets.googleapis.com-nodejs-quickstart.json`
main()
async function main () {
const oauth2Client = await authorize(clientSecret)
listMajors(oauth2Client)
}
/**
* Create an OAuth2 client with the given credentials, and then execute the
* given callback function.
*
* @param {Object} credentials The authorization client credentials.
*/
async function authorize (credentials) {
const clientSecret = credentials.installed.client_secret
const clientId = credentials.installed.client_id
const redirectUrl = credentials.installed.redirect_uris[0]
const auth = new googleAuth()
const oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl)
const getToken = promisify(oauth2Client.getToken, oauth2Client)
let token = {}
// Check if we have previously stored a token.
try {
token = JSON.parse(await readFile(TOKEN_PATH))
} catch (err) {
const authUrl = oauth2Client.generateAuthUrl({
access_type: 'offline',
scope: SCOPES
})
const code = await getNewCode(authUrl)
try {
token = await getToken(code)
await storeToken(token)
} catch (err) {
console.log('Error while trying to retrieve access token')
throw err
}
}
oauth2Client.credentials = token
return oauth2Client
}
/**
* Get and store new code after prompting for user authorization
*
* @param {string} authUrl The Auth URL generated by oauth2Client.generateAuthUrl()
*/
async function getNewCode (authUrl) {
console.log(`Authorize this app by visiting this url: ${authUrl}`)
const rl = readline.createInterface({
input: process.stdin,
output: process.stdout
})
const code = await rl.question('Enter the code from that page here: ')
rl.close()
return code
}
/**
* Store token to disk be used in later program executions.
*
* @param {Object} token The token to store to disk.
*/
async function storeToken (token) {
try {
await mkdir(TOKEN_DIR)
} catch (err) {
if (err.code != 'EEXIST') throw err
}
await writeFile(TOKEN_PATH, JSON.stringify(token))
console.log(`Token stored to ${TOKEN_PATH}`)
}
/**
* Print the names and majors of students in a sample spreadsheet:
* https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
*/
async function listMajors (auth) {
try {
const sheets = google.sheets('v4')
const getValues = promisify(sheets.spreadsheets.values.get)
const spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
const range = 'Class Data!A2:E'
const response = await getValues({auth, spreadsheetId, range})
const rows = response.values
if (rows.length == 0) {
console.log('No data found.')
return
}
console.log('Name, Major:')
for (let i = 0; i < rows.length; i++) {
const row = rows[i]
// Print columns A and E, which correspond to indices 0 and 4.
console.log(`${row[0]}, ${row[4]}`)
}
} catch (err) {
console.log(`The API returned an error: ${err}`)
return
}
}
@cognitom
Copy link
Author

cognitom commented Apr 5, 2017

The original code is here:
https://developers.google.com/sheets/api/quickstart/nodejs

$ npm i -S google-auth-library googleapis mz es6-promisify
$ npm i -D async-to-gen reify

Then, save the code avobe as quickstart.js and run this command:

$ node --require reify --require async-to-gen/register quickstart.js

@syncai
Copy link

syncai commented Sep 6, 2018

THANKYOU!

@tishma
Copy link

tishma commented Sep 14, 2020

I get compile error at line 101 (https://gist.github.com/cognitom/2300754df04df3477261ff12ee36285c#file-quickstart-js-L101). It looks like an array instead of key-value object in curly braces.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment