I’m using a BigQuery view for my sales data in Google Sheets. The setup works well but I’m facing a small issue. I’ve got a formula that counts orders for a specific date:
One approach that has worked well for me is to use Google Apps Script to develop a custom function that interfaces directly with BigQuery. This method removes the need for manual refreshes and supports dynamic data retrieval.
You can begin by setting up a service account with the necessary BigQuery permissions and then write a script in your Google Sheet that handles both authentication and query execution. For example, a custom function such as getBigQueryData(date) could call your BigQuery API and return the count of orders for a specific date. In your sheet, invoking =getBigQueryData(A1) would automate the process. Although initial configuration is required, this solution ultimately streamlines data refreshes and minimizes manual intervention.
I’ve encountered this situation in my own projects. Instead of having to click the ‘Apply’ button, I developed a small Google Apps Script that calls the BigQuery API directly. The process involved setting up a service account with the required permissions and writing a custom function that executes your query. Although this requires some initial setup and a comfort level with JavaScript, it allows the data to refresh automatically when the query runs. Another option is to use third-party add-ons, but I found the scripting approach offered more flexibility and real-time data updates.