DEV Community

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

Corentin on September 13, 2019

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 Go...

Read full post

easrng • Jun 18 '20

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.

ike • Jul 24 '20

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

Corentin • Jun 18 '20 • Edited

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

easrng • Jun 18 '20

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

Chris Achard • Sep 13 '19

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?)

Corentin • Sep 13 '19

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)
  })

CorporateDrone • Dec 21 '20 • 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.

Chris Achard • Sep 13 '19

Awesome - going to play with that this weekend :)

Theo • Nov 24 '21

Really cool work, thanks for sharing! I was dabbling with Google Sheet endpoints as well yesterday and managed to send POST straight to them without any middleman using apps script.

While it modifies the spreadsheet as intented it always returns an error, making it near unusable as you never know when it worked or hit a limit. Seems to be down to some redirect with POST requests, was wondering whether you had any insight?

local url = "https://script.google.com/macros/s/" .. scriptId .. "/exec"
local sendData =  "sheet=" .. "Feedback1" .. "&UserId=" .. userId .. "&Tag=" .. tag .. "&Thoughts=" .. thoughts
local success = pcall(function()
    return HttpService:PostAsync(url, sendData, Enum.HttpContentType.ApplicationUrlEncoded)
end)

Robert • Sep 16 '19

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

Corentin • Sep 17 '19

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 🙏

Robert • Sep 17 '19

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!')
        })
      }
    }
  })

Corentin • Sep 18 '19

Awesome Robert! Thanks a lot for sharing

Robert • Sep 20 '19

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.

Corentin • Sep 20 '19 • 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.

Olimpio • Sep 29 '19

I was looking for this. 🤩🔥🔥🔥

valentine • Sep 13 '19

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!

Corentin • Sep 13 '19

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

Meena Toor • Sep 14 '19

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?

Corentin • Sep 15 '19

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... 
]