I’ve solved a similar set of problems in my current role by piping all of our important sheets into BigQuery, saving/executing the SQL logic there, then making the result available as views that can be sent back to Sheets through Connected Sheets (or practically anywhere else eg Tableau via native connectors to BQ). For small datasets that fit in spreadsheets, the storage and query usage in BQ is practically free.
The trick (and I assume you’ve found ways around this) is dealing with the flaky Sheets API that throws a 500 error what feels like 1% of the time, and also deciding when/where/how to enforce the SQL data types and column headers. I made a config layer for the latter.. the interface to which is also a Sheet, lol.
The trick (and I assume you’ve found ways around this) is dealing with the flaky Sheets API that throws a 500 error what feels like 1% of the time, and also deciding when/where/how to enforce the SQL data types and column headers. I made a config layer for the latter.. the interface to which is also a Sheet, lol.