DEV Community

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

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!')
        })
      }
    }
  })
Enter fullscreen mode Exit fullscreen mode
Thread Thread
 
frenchcooc profile image
Corentin

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