Google Sheets is the ultimate destination to store and analyze your data. The challenge is to get outside data into Google Sheets in an automated fashion.

This is an introductory post to show you how to create new documents and store data into Google Sheets via its API.


Get Access Token

Before we start, let's use Google OAuth to fetch your access token. Sign in with Google below.


Hello, Google Sheets!

Our objectives are simple:

  1. Using Google Sheets API, create a new spreadsheet document
  2. Append rows to the document

Let's start with creating a new document. We will be using Google's offical NPM package, googleapis. See documentation for creating new sheet documents.


// Import googleapis package
const { google } = require("googleapis")

// Initialize authentication object
const auth = new google.auth.OAuth2()
auth.setCredentials({ access_token: process.env.GOOGLE_SHEETS_TOKEN })

// Create new sheet
const sheets = google.sheets({ version: "v4", auth })
const resp = await sheets.spreadsheets.create({
    resource: {
        properties: { title: "Hello Google Sheets" }
    }
})
console.log(`New document created: ${resp.data.spreadsheetId}`)

Note when creating new document, we are passing in a properties object. In this case it allows us to specify the title of the new document. Other supported properties include locale, timezone, etc. See complete reference here.

The script above should return the new document ID. This ID is necessary in subsequent steps when read/write to the spreadsheet. You can also get the document ID by looking in the browser URL field.

Append New Rows

To take the previous example one step further, let's append a few rows of data. See Google documentation on append rows.


const insert = {
    spreadsheetId,
    range: "A1:G",
    valueInputOption: "USER_ENTERED",
    insertDataOption: "INSERT_ROWS",
    resource: {
        values: [
            ["row 1 col 1", "row 1 col 2"],
            ["row 2 col 1", "row 2 col 2", "row 2 col 3"],
            ["row 3 col 1"]
        ]
    }
}
await sheets.spreadsheets.values.append(insert)

Above script would insert 3 rows, each of different length, into a new spreadsheet document. Try replace the spreadsheetId with a spreadsheet of your own, and tweak the values fields with you own data.

A few attributes worth noting:

  • range is the A1 notation of append site. You can optionally specify sheet name here. e.g. Sheet1!A1:G.
  • resource.values represents the data we want to insert. It's in the form nested array – outter array represents rows and inner array represent columns. Your data must be in this nested form even if you only need to insert one row. e.g. values: [["row 1"]].