How to send data to a Google spreadsheet, in 2 seconds?
Tags: growth, hack, googleapis, pizzly
Published: 2019-09-13 09:00:26

---
title: How to send data to a Google spreadsheet, in 2 seconds?
published: true
description: In this guide, you dive deep into the code of Push To GSheet, a free service that lets you push any data to a Google spreadsheet.
tags: growth, hack, googleapis, pizzly
cover_image: https://pix.watch/MwFTXd/vRshnX.jpeg
---

**Note:** *The "Push to GSheet" service used in this article is no longer active, but you can use [Pizzly](https://github.com/Bearer/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:

![GIF](https://media.giphy.com/media/lkdgvyS7701v93PeCF/giphy.gif)

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`](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append) to push any type of data. All that it asks for, are values formatted as a two-dimensional `array`:

```js
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](https://developers.google.com/oauthplayground/) to retrieve one easily. But I'm going to use a service called [Pizzly](https://github.com/Bearer/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`](https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id) as per the documentation. Each spreadsheet has a unique ID that we can find by looking into the `URL`:

```txt
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.

```js
// 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/${spreadsheetId}/values/A1:append?${query}`, 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](https://community.bearer.sh/pushtogsheet):~~

[![Push To GSheet website](https://pix.watch/S1Vdqk/N66g68.png)](https://community.bearer.sh/pushtogsheet)



*The "Push to GSheet" service used in this article is no longer active, but you can use [Pizzly](https://github.com/Bearer/Pizzly/) to host your own version.*

_PS: it's 100% free and open source_ ✌️