Skip to content

Instantly share code, notes, and snippets.

@iaincollins
Last active May 30, 2021 16:03
Show Gist options
  • Save iaincollins/43302ea047d4a77e6605350598d160c1 to your computer and use it in GitHub Desktop.
Save iaincollins/43302ea047d4a77e6605350598d160c1 to your computer and use it in GitHub Desktop.
Example Node.js code to append to a Google Spreadsheet every hour
/**
* 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
@anaamnizami
Copy link

anaamnizami commented Jan 27, 2018

Although I am specifying ValueInputOption i am still getting the following error Error: 'valueInputOption' is required but not specified,

    `function appendData(auth) {
  var sheets = google.sheets('v4');
  values: [ ["Void", "Canvas", "Website"], ["Paul", "Shan", "Human"] ];

  var body = {
    values: this.values,
    valueInputOption: "USER_ENTERED",
  };
  sheets.spreadsheets.values.append({
    auth: auth,
    spreadsheetId: '13QPQj1Ot0oBJms2eQzrKahwzGt13JxQkqf54j2zk3jI',
    range: 'Sheet1!A2:B', //Change Sheet1 if your worksheet's name is something else


    resource: body

  }, (err, response) => {
    if (err) {
      console.log('The API returned an error: ' + err);
      return;
    } else {
        console.log("Appended");
    }
  });
}
`


@gabrielaandb
Copy link

@anaamnizami I'm getting the 'valueInputOption' is required but not specified error with the googleapis package version ^25.0.0.

I changed it to version 24.0.0 and it worked as expected. There must have been a bug introduced in a recent update

@SamIlic
Copy link

SamIlic commented May 29, 2018

What kind of file is your ".env" and how is it formated? Also, where can I find:
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)

Btw, I'm relatively new to javascript and the google API so these might be basic questions.

@rotimi-best
Copy link

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
image

@mohnishisrani
Copy link

Hi @rotimi-best
were able to find a solution for your problem I have a similar requirement.

@george-c29
Copy link

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