DEV Community

loading...
Cover image for How to send data to a Google spreadsheet, in 2 seconds?
Bearer.sh

How to send data to a Google spreadsheet, in 2 seconds?

Corentin
Co-founder and VP Engineering at Mailmeteor.com πŸš€
・Updated on ・3 min read

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:

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 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... 
]
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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/${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);
Enter fullscreen mode Exit fullscreen mode

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:

Push To GSheet website

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 ✌️

Discussion (23)

Collapse
easrng profile image
easrng

It's even eaiser with Google Forms, if you want the general public to be able to add to the sheet. Just send a request mimicing a form response.

Collapse
iketiunn profile image
ike

Yes, and I actually did it in my app recently, it's convenient with the form field editor.

But you have to extract the form meta to be able to make a request, and it can only send a request per line.

Also I made a tool to make this process easier too.
link: restful-google-form.vercel.app

Collapse
frenchcooc profile image
Corentin Author • Edited

Great idea! Works like a charm as well 😍 (edited)

Collapse
easrng profile image
easrng

Why wouln't you be able to use it from a server? it works OK for me.

Collapse
chrisachard profile image
Chris Achard

Oh, neat! I have always thought google sheets could be used as a kind of database, but thought it would be a lot harder to integrate with... thanks! I'll definitely try this out.

oh - is getting data out of the spreadsheet just as easy? (I guess once you have the token, then yes?)

Collapse
frenchcooc profile image
Corentin Author

Hi Chris! Thanks a lot. Really appreciate ;)

Getting values is just as easy. Replace step 4 with the following and you will be ready to go:

gsheet
  .auth('YOUR_BEARER_AUTH_ID')
  .get(`${spreadsheetId}/values/A:Z`)
  .then(({ data }) => {
    console.log(data)
  })
Collapse
corporatedrone profile image
CorporateDrone • Edited

Hello Corentin. I've been looking for something like this for a long time. For fetching the data, your example uses 'gsheet'. Is this another library? I can't quite figure out what to do with it.

I also tried modifying the fetch example using get, but got a CORS error.

Collapse
chrisachard profile image
Chris Achard

Awesome - going to play with that this weekend :)

Collapse
treboryx profile image
Robert

Hi, this is great. I've got one question however, would you be able to update a specific row, based on a value?

Collapse
frenchcooc profile image
Corentin Author

Hi Robert! As far as I know, the Google Sheets API doesn't provide an endpoint to do that easily.

So you have to perform two requests (fetching data, then saving it) and some data manipulation in between.

1. Fetching all the sheet values

// This will returns all values for columns A to Z
gsheet
  .auth('YOUR_BEARER_AUTH_ID')
  .get(`${spreadsheetId}/values/A:Z`)
  .then(({ data }) => {
    console.log(data)
    // Loop through the data object
    // and do the value comparison here
  })

2. Updating a cell based on its position in the sheet (A1 notation)

// This will update the cell B2 (and only this cell)
gsheet
  .auth('YOUR_BEARER_AUTH_ID')
  .put(`${spreadsheetId}/values/B2?valueInputOption=RAW`, { body: { values: [['new value']] } })
  .then(({ data }) => {
    console.log('Saved!')
  })

I'll let do the glue between this two functions, depending on your need. Feel free to share your snippet at the end. It might help other readers πŸ™

Collapse
treboryx profile image
Robert

Hey thank you Corentin, that was actually pretty helpful. Here's a quick implementation of mine:

 gsheet.auth('YOUR_BEARER_AUTH_ID')
  .get(`${spreadsheetId}/values/A:Z`)
  .then(({ data }) => {
    const value = "Kyle"; // value I want to find and replace
    const newValue = "Corentin"; 
    const cols = 'abcdefghijklmnopqrstuvwxyz'.split('');

    for (let i = 0; i < data.values.length; i++) {
      if (data.values[i].indexOf(value) > -1) {
        const row = i + 1;
        const col = cols[data.values[i].indexOf(value)].toUpperCase();
        gsheet.auth('YOUR_BEARER_AUTH_ID')
        .put(`${spreadsheetId}/values/${col}${row}?valueInputOption=RAW`, { body: { values: [[newValue]] } })
        .then(({ data }) => {
          console.log('Saved!')
        })
      }
    }
  })
Thread Thread
frenchcooc profile image
Corentin Author

Awesome Robert! Thanks a lot for sharing

Thread Thread
treboryx profile image
Robert

Hi Corentin, sorry if this is any bother but after working with this for a little bit more, I found out that this only works with spreadsheets created specifically from your app, if I create one manually myself it returns a 404. Tested with multiple spreadsheet IDs that I created.

Thread Thread
frenchcooc profile image
Corentin Author • Edited

Hi Robert! That's 100% true. This is due to the OAuth scope used by this project.

I'm using googleapis.com/auth/drive.file which grants "per-file access to files created or opened by the app". I had to do so, because Google has recently limited developers access to Google customers data. To avoid an important audit process (that would have costed me thousands of dollars), I went with the minimal scope required.

Have a look more deeply into Google Sheets scopes here: developers.google.com/sheets/api/g... - and if you want to go over that restriction, feel free to create your own integration with Google Sheets by creating an account on Bearer.sh

Bearer has a free plan with up to 100K API calls / month.

Collapse
olimpioadolfo profile image
Olimpio

I was looking for this. 🀩πŸ”₯πŸ”₯πŸ”₯

Collapse
valxntine profile image
Val

This is pretty cool, Corentin!
I've had ideas for a few tools that would make working life easier for me and my colleagues, and this solves one of many little problems!

Collapse
frenchcooc profile image
Corentin Author

Thanks a lot! I hope you gonna enjoy using it ✌️

Collapse
meenatoor1 profile image
Meena Toor

This is so cool corentin.

In theory, could it mean that you can populate linkedin jobs as they are posted, to the g ss?

Or is it limited to just names?

Collapse
frenchcooc profile image
Corentin Author

Hi Meena! You can pass whatever data you want. It just has to be formatted as a two-dimensional array:

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

Happy coding πŸ‘©β€πŸ’»

Collapse
cynthiablue profile image
cynthiablue

I want to send data to Google Sheets using Vue (well, Quasar) and I'm having a heck of a time finding the right syntax. Have you done this with Vue?

Collapse
gribias profile image
Gabriel Correia

Hello!
I was looking for a post like this!
how do you get 'A_BEARER_AUTH_ID'?

Collapse
gribias profile image
Gabriel Correia

Nvm i found it :) thanks a lot

Collapse
ziaongit profile image
Zia

Hi,
I am providing the same service on Fiverr with very low prize. Please have look on my Gig.
fiverr.com/share/yvYWrG

Kind Regards
Zia