Goal: I want to insert and update values to not just existing columns in a spreadsheet (which I am able to do fine), I want to be able to dynamically create additional columns as the need arises. This can happen a lot in financial reports where as time progresses a financial spreadsheet receives a new column of the past/new month or quarter.
How can I do this? If it's not possible with the retool API, do you have an example of how to set up a rest api call with google and using the auth info provided for the google sheet connection so I can deal with one less problem (meaning the authentication)?
Apologies for the inconvenience, currently the built in Retool options for Google Sheets queries do not include the action you would need to add columns to the table.
I would imagine that you would need to programmatically let the table know that for all previous rows what the value of the newly added column would be, might need to check the Google Sheets API docs for that.
After a quick google around, I found that "To add a column to your Google Sheets, you can use the batchUpdate method to insert a column by using the InsertDimensionRequest" so that might be doable with a REST API call to the Google Sheets API.
On the authentication side, you will have to authenticate the Resource to be able to use it, and the token will expire after a certain amount of time and require a re-auth to use the query.
I can make a feature request for the Google Sheets Query action for you and keep you updated!