Example Node.js code to append to a Google Spreadsheet every hour

Google Spreadsheet.js

/**
 * 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

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");
    }
  });
}
`

@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

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.

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

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