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 Zgsheet.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 🙏
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})=>{constvalue="Kyle";// value I want to find and replaceconstnewValue="Corentin";constcols='abcdefghijklmnopqrstuvwxyz'.split('');for(leti=0;i<data.values.length;i++){if(data.values[i].indexOf(value)>-1){constrow=i+1;constcol=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!')})}}})
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.
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.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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
2. Updating a cell based on its position in the sheet (A1 notation)
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 🙏
Hey thank you Corentin, that was actually pretty helpful. Here's a quick implementation of mine:
Awesome Robert! Thanks a lot for sharing
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.
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.