Snowflake Open Flow in Action – Part 5: Snowflake Openflow Connector for Google Sheets

  • June 4, 2025
  • Posted by: Indumathi G
  • Category: Snowflake
No Comments

Introduction:

Welcome to the next blog in our Snowflake OpenFlow series. In this post, we explore how to configure and implement the OpenFlow Connector for Google Sheets. Leveraging the Google Sheets API, this connector enables ingestion of spreadsheet data into Snowflake tables using a truncate-and-load strategy.

We’ll walk you through the setup process and provide a detailed overview of the connector’s workflow. If you’re looking to bring data from Google Sheets into Snowflake for reporting, analytics, or downstream applications, this connector offers a simple and effective solution.

Prerequisites:

  • Ensure that Openflow is setup in your snowflake account and a deployment is active. Refer my previous blog to see how to setup openflow in your snowflake account.
  • GCP Setup:
  • Login to your GCP Console and navigate to IAM & Admin > Service Accounts.
  • Click on Create service account. Fill in the Service Account details and click on Create and continue.
  • On the newly created service account, click on the three dots and select Manage Keys.
  • Click the Add Key dropdown and select Create new key.
  • Select Key type as JSON and select Create.
  • The key file will be downloaded as a .json file
  • Navigate to APIs & Services > Enabled APIs & services. Click on Enable APIs and services.
  • Search for Google Sheets API in Google Workspace section and click on it.
  • Click on Enable to enable this API
  • Your Service Account should have appropriate privileges to access this Google sheet. For this, open your google sheet, click on Share, give your service account id, choose either Viewer/Editor role and click on Done.

  • Snowflake Setup:
  • Create a new service user with type = ‘SERVICE’ and grant OPENFLOW_ADMIN_ROLE created during Openflow setup to this user.
  • Generate Key-pair – public and private keys for this service user
  • Create a Database and schema and give usage and appropriate privileges to the OPENFLOW_ADMIN_ROLE
  • Create a warehouse for the connector to use, with the smallest size and scale up if required. Grant Usage on this warehouse to OPENFLOW_ADMIN_ROLE.

USE ROLE ACCOUNTADMIN;
CREATE USER LEGACY_SERVICE_USER TYPE='SERVICE';
GRANT ROLE OPENFLOW_ADMIN_ROLE TO USER LEGACY_SERVICE_USER;
ALTER USER LEGACY_SERVICE_USER SET RSA_PUBLIC_KEY='MIIBI...';

USE ROLE ACCOUNTADMIN;
CREATE DATABASE IF NOT EXISTS OPENFLOW_GOOGLE_SHEETS;
USE OPENFLOW_GOOGLE_SHEETS;
CREATE SCHEMA IF NOT EXISTS OPENFLOW_GOOGLE_SHEETS;
USE SCHEMA OPENFLOW_GOOGLE_SHEETS;
CREATE WAREHOUSE OPENFLOW_GOOGLE_SHEETS_WH;
GRANT USAGE ON DATABASE OPENFLOW_GOOGLE_SHEETS TO ROLE OPENFLOW_ADMIN_ROLE;
GRANT ALL ON SCHEMA OPENFLOW_GOOGLE_SHEETS TO ROLE OPENFLOW_ADMIN_ROLE;
GRANT USAGE ON WAREHOUSE OPENFLOW_GOOGLE_SHEETS_WH TO OPENFLOW_ADMIN_ROLE;

 

  • Create a runtime using the active deployment created.

Configuring the connector:

  1. Once the Runtime is Active, navigate to the Openflow Overview page. Go to Featured Connections section and click on View more Connectors. Select Google Sheets connector and click on Add to Runtime.

  • Select your runtime from the Available runtimes drop-down list. Click Add.

  • Authenticate using your snowflake credentials.

  • Once the connector installation is complete, Openflow Canvas will appear with the connector process group added to it.

  • Right-click on the open canvas and click on Enable All Controller Services. Check whether all services are enabled by right-clicking on the connector and selecting Controller Services.

  • Now we will set the flow parameters by right-clicking on the connector and selecting Parameters.

  • There are three different groups of flow parameters to be set for this connector.
    • Google Sheets Destination Parameters:

Parameter Name Parameter Value Description
Destination Database OPENFLOW_GOOGLE_SHEETS Give the Snowflake Database name where the Google Sheets data will be loaded
Destination Schema   OPENFLOW_GOOGLE_SHEETS Give the Snowflake Schema name where the Google Sheets data will be loaded
Snowflake Account Identifier <Organization_name>-<Account_Name> Give the Snowflake account identifier where the Google Sheets data will be loaded
Snowflake Authentication Strategy KEY_PAIR  
Snowflake Private Key   Give the Service User’s Private key. Snowflake Private Key or Snowflake Private Key File needs to be filled
Snowflake Private Key File   Upload the Service user’s private key file. Snowflake Private Key or Snowflake Private Key File needs to be filled
Snowflake Private Key Password   Give the password to decrypt the Private key if encrypted. We will keep this blank here.
Snowflake Role OPENFLOW_ADMIN_ROLE Give the Snowflake Role used to access the Database objects and the warehouse
Snowflake Username LEGACY_SERVICE_USER Give the Snowflake Service user created for this data ingestion
Snowflake Warehouse OPENFLOW_GOOGLE_SHEETS_WH Give the Snowflake Virtual Warehouse created for this Data Ingestion

  • Google Sheets Source Parameters:

Parameter Name Parameter Value Description
Service Account JSON   Give the entire contents of the Service Account key file containing client_id, client_email, and private_key in JSON format.

  • Google Sheets Ingestion Parameters:

Parameter Name Parameter Value Description
Date Time Render Option FORMATTED_STRING Give the value as SERIAL_NUMBER (datetime as Serial Number) or FORMATTED_STRING (datetime as Strings)
Destination Database OPENFLOW_GOOGLE_SHEETS Give the Snowflake Database name where the Google Sheets data will be loaded
Destination Schema OPENFLOW_GOOGLE_SHEETS Give the Snowflake Schema name where the Google Sheets data will be loaded
Destination Table Prefix OPENFLOW_GOOGLE_SHEETS Give the prefix of the table that needs to be created in Snowflake
Ranges   Give the range to be retrieved from the spreadsheet. If blank, then all sheets will be retrieved.
Run Schedule 1m Give the run schedule for this data ingestion
Spreadsheet ID <Spreadsheet_Id> Give the Unique Id for this spreadsheet. You can get this from the URL of the spreadsheet
Value Render Option UNFORMATTED_VALUE Give FORMATTED_VALUE or UNFORMATTED_VALUE. Give FORMATTED_VALUE if you need all the columns in the destination table to be of VARCHAR type

  • Run the workflow by right-clicking on the connector and selecting Start

  • Once the workflow is successfully completed, you will be able to find a new table created in Snowflake.

The connector only performs truncate and load ingestion. This means that every time a file is ingested, the existing data in Snowflake is completely replaced with the new data from the file.

Conclusion:

The Snowflake OpenFlow Connector for Google Sheets simplifies the process of bringing your Google Sheets data into your Snowflake environment. By removing the need to manage complex data pipelines or low-level configurations, OpenFlow handles the technical heavy lifting – allowing you to focus on insights, not infrastructure. Whether you’re fueling dashboards, driving analytics, or building data-powered applications, this connector helps you automate and scale your data workflows. With seamless integration and reduced operational overhead, turning your Google Sheets into actionable Snowflake data has never been easier.

Referral Link: https://docs.snowflake.com/en/user-guide/data-integration/openflow/connectors/google-sheets/setup

Please feel free to reach out to us for your Snowflake solution needs. Cittabase is a Premier  partner with Snowflake.



Leave a Reply