Last active
May 30, 2021 16:03
-
-
Save iaincollins/43302ea047d4a77e6605350598d160c1 to your computer and use it in GitHub Desktop.
Example Node.js code to append to a Google Spreadsheet every hour
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
/** | |
* Append data to a Google Spreadsheet | |
* | |
* You will need a file called '.env' with the following values: | |
* | |
* - GOOGLE_ID (Google oAuth Client ID) | |
* - GOOGLE_SECRET (Google oAuth Client Secret) | |
* - GOOGLE_REFRESH_TOKEN (Google oAuth Refresh Token) | |
* - GOOGLE_SPREADSHEET_ID (Google Spreadsheet ID) | |
* | |
* Requires Google Sheet API access enabled (and Google+ for oAuth): | |
* https://console.developers.google.com/apis/api/sheets.googleapis.com/overview | |
* https://console.developers.google.com/apis/api/plus.googleapis.com/overview | |
* | |
* API Documentation: | |
* https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append | |
* | |
*/ | |
'use strict' | |
require('dotenv').config() | |
const google = require('googleapis') | |
const sheets = google.sheets('v4') | |
const updateSpreadsheet = () => { | |
const oauth2Client = new google.auth.OAuth2( | |
process.env.GOOGLE_ID, | |
process.env.GOOGLE_SECRET | |
) | |
oauth2Client.setCredentials({ | |
refresh_token: process.env.GOOGLE_REFRESH_TOKEN | |
}) | |
oauth2Client | |
.refreshAccessToken((err, tokens) => { | |
if (err) return console.error(err) | |
oauth2Client.setCredentials({ | |
access_token: tokens.access_token | |
}) | |
// The following call will create a spreadsheet and return an ID that can | |
// be used with the API. Note that oAuth API can only be used to access | |
// files it creates, not files already on a drive (unless you apply to | |
// Google for additional privilages.) | |
/* | |
sheets.spreadsheets.create({ auth: oauth2Client }, (err, response) => { | |
if (err) return console.error(err) | |
console.log(`New Spreadsheet ID: ${response.spreadsheetId}`) | |
}) | |
*/ | |
sheets.spreadsheets.values.append({ | |
spreadsheetId: process.env.GOOGLE_SPREADSHEET_ID, | |
range: 'Sheet1', | |
valueInputOption: 'RAW', | |
insertDataOption: 'INSERT_ROWS', | |
resource: { | |
values: [ | |
[new Date().toISOString(), "Some value", "Another value"] | |
], | |
}, | |
auth: oauth2Client | |
}, (err, response) => { | |
if (err) return console.error(err) | |
}) | |
}) | |
} | |
// Run at startup | |
updateSpreadsheet() | |
setInterval(() => { | |
updateSpreadsheet() | |
}, 60000 * 60) // Run again every hour |
Hi @rotimi-best
were able to find a solution for your problem I have a similar requirement.
Brilliant article on how to obtain the refresh & access token in a non production / test environment: https://www.ibm.com/docs/en/app-connect/cloud?topic=gmail-getting-oauth-client-id-client-secret-access-token-refresh-token-google-applications
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I have a use case where I am trying to create an attendance register on the spreadsheet and usually when the register is taken you don't mark by rows, you do it by columns, so I need to write + for those who were present and - for those who were absent into the spreadsheet from my app, how do I achieve that? I haven't found how to do it, I only found how to append by rows or by a particular range, but using a particular range wouldn't be predictable cause I can't be able to tell the next column that is empty that needs to be appended to.
An example of an attendance register