Note: The "Push to GSheet" service used in this article is no longer active, but you can use Pizzly to host your own version.

I'm a huge fan of Google APIs. I use a lot of them on a daily basis. Whether in my code or through the infinite number of services that rely on it.

Recently, I was running a scraping script and quickly found myself doing some horrible copy/pasting into a Google Sheet. I thought that there should be a better way to dynamically push data into a spreadsheet:

Having a quick look at the Google Sheets API reference, I found an /append endpoint that would do the work nicely. 30 minutes later, I was sipping lemonade while my script was running in the background 😎

If I wasn't in love with Google APIs, I'd certainly have moved on to something else. But wait, 20 minutes to send data to a spreadsheet? Come on! Let's try to make it as simple as searching on Google.

What do we need?

First, understanding the API. As said just before, Google Sheets provides a handy endpoint /append to push any type of data. All that it asks for, are values formatted as a two-dimensional array:

const values = [
  // First row
  ["A1","A2","A3"],
  // Second row
  ["B1","B2","B3"]
  // Third row... 
]

Data will always be appended to the sheet. So if the sheet has some values on row #1, new values will be added on row #2 (and so-on).

Next, handling the authentication. Like most APIs that give access to users' data, the Google Sheets API uses OAuth2. It's an authentication schema that is both very powerful and... complicated to setup.

What's interesting for us, is that each request to the Google Sheets API requires an access_token - a long string, unguessable, that certifies the request is made on behalf of a user.

Google provides a playground to retrieve one easily. But I'm going to use a service called Pizzly that handles the OAuth-dance securely for me and will handle refresh token, something that the playground doesn't (disclaimer: I helped built Pizzly).

Anything else? We are just missing on which spreadsheet the data shall be appended. That's the spreadsheetID as per the documentation. Each spreadsheet has a unique ID that we can find by looking into the URL:

https://docs.google.com/spreadsheets/d/{{spreadsheetId}}/edit#gid=0

Show me the code!

Wrapping it up, it took 10 lines of code to actually send data to any spreadsheet.

// 1. Run npm install node-fetch

// 2. Import an HTTP client

const fetch = require("node-fetch");

// 3. Declare spreadsheet and values to append

const spreadsheetId = 'SPREADSHEET_ID'

const data = [["firstname", "lastname"], ["John", "Doe"]]

// 4. Send data with a POST request

const baseUrl = "https://pushtogsheet.herokuapp.com";

const query = valueInputOption=RAW&pizzly_pkey=pope8Qy8qfYyppnHRMgLMpQ8MuEUKDGeyhfGCj;

const url = new URL(/proxy/google-sheets/spreadsheets/</span><span class="p">${</span><span class="nx">spreadsheetId</span><span class="p">}</span><span class="s2">/values/A1:append?</span><span class="p">${</span><span class="nx">query</span><span class="p">}</span><span class="s2">, baseUrl);

fetch(url.href, {

    method: "POST",

    body: JSON.stringify({ values: data }),

    headers: { 'Pizzly-Auth-Id': 'CONNECT_FIRST' }

  })

  .then((res) => res.text())

  .then(console.log)

  .catch(console.error);

How can I use it?

I did my best to make pushing data to gsheet as easy as googling, and crafted a dedicated website for that:

The "Push to GSheet" service used in this article is no longer active, but you can use Pizzly to host your own version.

PS: it's 100% free and open source ✌️