Last active
June 4, 2024 04:16
-
-
Save cognitom/2300754df04df3477261ff12ee36285c to your computer and use it in GitHub Desktop.
Unspaghetti version of Google Spreadsheet API example code
This file contains hidden or 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
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 | |
} | |
} |
THANKYOU!
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
The original code is here:
https://developers.google.com/sheets/api/quickstart/nodejs
Then, save the code avobe as
quickstart.js
and run this command: