How to connect Google sheet API to Snowflake using external network access. (Part 1)

Objectives

• Enable Google Sheet API
• Create and understand network rule
• Create and understand security integration
• Create secrets
• Create external network access integration

Overview

The aim is to create data transfer pipeline between Snowflake and Google Sheets, empowering users to easily move data between these platforms for analysis, collaboration, and reporting.

Google Sheets API enables developers to interact programmatically with Google Sheets; developers can read, overwrite, append, and delete data from Google Sheets.

Read and Write quota on Google sheet

Google Sheets API has per-minute quotas, and they’re refilled every minute, Google Sheet API allows 300 requests per minute to read and write operations.

All use of the Google Sheets API is available at no additional cost. Exceeding the quota request limits doesn’t incur extra charges and your account is not billed.

Steps

Open Google Cloud Console and navigate to Enabled APIs & services.

click on Enabled APIs & Services

Click on ENABLE APIS AND SERVICES button.

Click on ENABLE APIS AND SERVICES

In the search bar, type ‘google sheet’.

click on google sheet API

Click on the Enable button to enable the Google Sheets API.

Click on Enable

Now that you have enabled Google Sheets API, the next step is to set up credentials by clicking on the CREDENTIAL tab as shown in the image below.

Click on the Configure consent screen.

Google OAuth consent screen

When you use OAuth 2.0 for authorization, your app requests authorizations for one or more scopes of access from a Google Account. Google displays a consent screen to the user including a summary of your project and its policies and the requested scopes of access.

Select External and click on create.

Fill out the form shown on the screen as follows

App name: This should be your project ID. To access project ID click on the project name (My Project) in my case then copy the project ID, should be like this <project name>-project id (my-project-112323)

User support email: could be your email or a group email

Authorized email: I used google.com, in your case, you might want to use your domain.

You can leave the APP logo and App domain section blank depending on your use case. Now click on save and continue to configure the OAuth consent screen.

Select scope as appropriate, we would be using least privilege here by enabling only what we need. We want to be able to create, delete, and edit Google Sheets using Google Sheets API.

Now you can add testing users, only test users can access the application

Now that we have set up the OAuth consent screen the next set is to handle authentication. Click on CREATE CREDENTIALS as shown and select OAuth client ID.

Select Web application as the application type and fill in the credential name

create credential

Once you create credentials you should have some like this, keep your Client ID and secret safe.

Open the created credential on the console

Authorized redirect URIs: https://developers.google.com/oauthplayground

The next step is to get refresh token using the OAuth2 playground.

• Go to Google’s OAuth Playground
• Click on the OAuth 2.0 configuration button
• select use your own OAuth credentials
• Input OAuth Client ID and OAuth Client secret generated earlier
• Use https://www.googleapis.com/auth/spreadsheets as the scope and click on the Authorise APIs button.

Generate refresh token

Enough of Google Cloud setup 😂 lol, the next step is to set up on Snowflake.

Network rule on snowflake

Network rule on Snowflake is the feature that restricts traffic in and out of Snowflake. It allows external services to send requests in Snowflake. Network rule specifies whether the identifiers in the rule are permitted or prohibited.

Note: only an administrator or role granted access to create Network Rule can create a Network Rule.

Let’s go ahead to create Network Rule for Google Sheets API

CREATE OR REPLACE NETWORK RULE google_sheet
 TYPE = HOST_PORT
 VALUE_LIST = ('sheets.googleapis.com')
 MODE = EGRESS
 COMMENT = 'This is network rule for google sheet integration'
;

• Network rule name: google_sheet
• TYPE: HOST_PORT
• MODE: EGRESS, allows Snowflake to send requests to an external destination.

You can view all created network rules by running

SHOW NETWORK RULES

This lists all network rules defined in the system and shows properties attached to the rules

You can also drop rules using

DROP NETWORK RULE google_sheet

Create security integration

Security integration provides an interface between Snowflake and external services. It allows users to authenticate, this could be OAuth, SAML2, or SCIM. For this example, we would be using Snowflake OAuth, A Snowflake OAuth security integration that enables clients that support OAuth to redirect users to an authorization page and generate access tokens (and optionally, refresh tokens) for access to Snowflake.

Note: only an administrator or role granted access to create Integrations can create a Security integration.

We will make use of the API credential created earlier.

CREATE OR REPLACE SECURITY INTEGRATION gsheet_oauth
 TYPE = API_AUTHENTICATION
 AUTH_TYPE = OAUTH2
 OAUTH_CLIENT_ID = '17846xxx-xxxxxx.apps.googleusercontent.com'
 OAUTH_CLIENT_SECRET = 'GOCSPX-xxxxxxxxxx'
 OAUTH_TOKEN_ENDPOINT = 'https://oauth2.googleapis.com/token'
 OAUTH_AUTHORIZATION_ENDPOINT = 'https://accounts.google.com/o/oauth2/v2/auth'
 OAUTH_ALLOWED_SCOPES = ('https://www.googleapis.com/auth/spreadsheets')
 ENABLED = TRUE;

Once you create security integration, you can view created integrations

SHOW INTEGRATIONS

Create Secret

Secrets represent stored credentials. Create a secret with the OAuth client credentials flow and use OAuth refresh token generated from the OAuth2 playground.

CREATE OR REPLACE SECRET gsheet_secret
 TYPE = oauth2
 API_AUTHENTICATION = google_oauth
 OAUTH_REFRESH_TOKEN = '1//04yjlsZutljyCCgYIARAAGAQSNwF-L9IrSaxGo0PopuIobC52NeAoR4Dx2ToE3JqElLXZkUyqr3Fpr4z_lIDi-7d-WUAUd6dD4wI'

DESC SECRET gsheet_secret;

Create External Access Integration:

External network access allows secure access to specific network locations external to Snowflake, then uses that access from within the handler code for user-defined functions (UDFs) and stored procedures.

External network access uses secrets that represent stored credentials, rather than using literal values, within handler code to authenticate with external network locations.

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION gsheet_api_access_integration
 ALLOWED_NETWORK_RULES = (google_sheet)
 ALLOWED_AUTHENTICATION_SECRETS = (gsheet_secret)
 ENABLED = TRUE
 COMMENT = 'google sheet API access';

Now we can use EXTERNAL ACCESS INTEGRATION in UDF and procedure handlers that access external locations.

References:

• https://docs.snowflake.com/en/developer-guide/external-network-access/external-network-access-overview
• https://developers.google.com/sheets/api/guides/concepts