Best Of

Hi @meagans

Here's a guide on a workflow for automating your work between GCP BQ and Smartsheet, including different strategies for keeping your data in sync.

The "Why": One-Way Data Flow from BigQuery

A typical pattern is to establish a one-way data flow from Google BigQuery (BQ) to Smartsheet. BQ is a fantastic data warehouse for analyzing massive datasets, but it isn't designed for the kind of row-by-row collaboration and updates we do in Smartsheet.

Therefore, a typical scenario involves pulling a specific dataset from BQ into Smartsheet, where your team can then utilize it for tracking, project management, and planning.

The "How": Prototyping and Automation

The best way to build this is with a two-step process:

1. Prototype in Google Colab: Use this free, web-based Python environment to write and test your script. You can easily connect to both BigQuery and the Smartsheet API to make sure everything works perfectly.
2. Deploy to GCP Cloud Functions: Once your script is ready, move it to Cloud Functions. This allows you to run it on an automated schedule (e.g., daily at 6 AM) without needing to manage a server.

Strategies for Keeping Your Sheet Updated

How you update the data in Smartsheet depends on your use case, especially if the sheet is connected to reports or dashboards.

Method 1: Overwrite an Existing Sheet (Stable Snapshot)

This is often the best approach, especially when your sheet feeds Smartsheet reports or dashboards. Because reports are linked to a specific Sheet ID, creating a new sheet every time would break the connection.

The process is simple and reliable:

1. Delete all existing rows from the target sheet.
2. Add the current rows from your BigQuery query.

This keeps your dashboard connections intact while ensuring the sheet is a perfect, up-to-date snapshot of the BQ data.

Method 2: Create a New Sheet (Fresh Snapshot)

This method involves creating a brand-new sheet every time the script runs. It's very simple to code, but it has a drawback: since it generates a new Sheet ID each time, it's not suitable for sheets connected to ongoing reports or dashboards.

Method 3: Accumulate in an Existing Sheet (Growing Log)

Another option is to continuously add new rows from BigQuery to a sheet at each cycle, allowing it to grow over time. This can be useful for logging, but you need to be mindful of Smartsheet's 20,000-row limit.

Ultimately, since BigQuery is your true source for the complete data history, simply overwriting the sheet with the latest data (Method 1) is often the most practical and efficient solution.

Demo Code

For demo purposes, the code takes the simple Method 2 approach.

To get you started, this GitHub Gist provides a template for connecting to BigQuery and populating a Smartsheet. You can easily adapt this base code to implement any of the methods described above.

▶️ Click here for the GitHub Gist Demo Code

Fetching Data from BigQuery

Working with Google BigQuery in Colab is straightforward.

First, you authenticate your Google account with just a few lines of code. Then, you write your SQL query and use the popular pandas library to load your data into a "DataFrame," which is basically a powerful table. Using pandas is great because you can easily clean, transform, or reformat your data before you send it over to Smartsheet.

Using the Smartsheet API

After you have your data, it's time to use the Smartsheet Python SDK. Your script can do several things:

• Create a new sheet from scratch.
• Add rows to that newly created sheet.
• Add rows to a pre-existing sheet (you'll need its Sheet ID).

This gives you full control over how and where your BigQuery data lands in Smartsheet.

https://app.smartsheet.com/b/publish?EQBCT=966efbcace2e448e8458daadd4dabcc5

Accessing BigQuery Code Snippets in Colab

You can find this helpful, ready-to-use code right inside any Colab notebook.

1. Open a Colab notebook.
2. On the left sidebar, click the < > (Code snippets) icon.
3. In the filter search bar, type "BigQuery".
4. Select the snippet titled "Snippets: BigQuery " or "Getting started with BigQuery".

Clicking "Insert" will add a code cell with all the necessary boilerplate for authentication and running a query with the results loaded directly into a pandas DataFrame.

You can access Google Colaboratory here:

https://colab.research.google.com/

If you're looking to implement a more complex solution and need further assistance, you may want to contact an expert like Michelle.

Hey, it's been a few years since this post, and I wanted to check if there have been any updates from the Smartsheet side on getting access to Workspace details across all users on our account. Is that something that we might eventually see? I can't imagine we are the only ones who want to ensure that all Workspaces are visible to Admins.