I am looking to replace jdbc data retrieval from SQL Server database to Google Sheets because jdbc is too slow with the new V8 GApps script interpreter
I have very many sheets which follow a similar process
Sheet user iniates a database procedure that results in an output table in SQL Server
Output table is uploaded to the sheet (currently by jdbc) and the data displayed as tables/charts on the sheet
I am trying replacing the upload to the sheet by
Datastream from the output table (triggered by it being repopulated by the SQL Server procedure) into a BigQuery dataset then retieving the revised output data into the Google sheet
Either using Connected Sheets
or BigQuery API to retrieve from the BiqQury dataset
The question is how to know when the Datastrem has completed its upload and the BigQuery dataset is up to date with the revised output data.
In manual testing of the steps, if you query the BigQuery dataset too soon you get either no data or the previous data
Is there a function that can be called from the Google Sheet apps script to check when the Datastream process has completed.
Here’s a quick guide to help you resolve the issue:
Polling the BigQuery API: Since Datastream doesn’t emit a direct “completion” event, the most practical approach in Google Apps Script is to periodically poll BigQuery to check for new or expected data, such as a specific column or row that signals completion. This method offers simplicity and direct control over timing, works with both Connected Sheets and the BigQuery API, and is suitable when immediate data availability isn’t critical. However, it adds some complexity, requires a backoff strategy to avoid quota issues, and may introduce latency if Datastream’s timing is unpredictable.
**Datastream Metadata and Events:**BigQuery provides detailed metadata views that are ideal for checking data freshness, making them a reliable and efficient solution for Apps Script workflows where Datastream completion must be inferred. While Datastream and Cloud Logging can offer deeper operational insights, such as ingestion times or error statuses, polling BigQuery metadata via the Advanced Service is often the simplest and most accurate method. This avoids the complexity of parsing logs or indirect signals and offers a practical, low-latency path to detect when new data has landed.
**Google Cloud Functions (or Cloud Run):**A more advanced approach involves setting up a Cloud Function (Node.js or Python) or a Cloud Run service triggered by a successful Datastream event. This service then securely calls your Apps Script using UrlFetchApp.fetch() with an authentication token. Alternatively, Apps Script can poll a flag in Firestore or Datastore that the Cloud Function sets. This method decouples data processing from script logic, improves scalability and error handling, and supports complex workflows—but requires familiarity with Cloud Functions, event configuration, and cross-service authentication. It’s ideal for resilient pipelines where polling is insufficient.
If you need further assistance with your project, feel free to reach out to Google Cloud Support at any time.
Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.