Streamlit App for Snowflake — Google Sheet Integration

Let’s create something awesome today…

Before we get started ensure your have the right access and necessary privileges to create the objects required to enable external network access which can be provided via a custom role by your account administrator

Step0 — Privileges

Step1 — Network Rule

Network rule helps restrict access to and from Snowflake, so we enable it for google spreadsheet

Step2 — Security Integration

Enables interface between Snowflake and third-party service

Step3 — Secret Object

It stores sensitive information, limits access to the sensitive information using RBAC, and is encrypted using the Snowflake key encryption hierarchy.

Add your OAUTH_REFRESH_TOKEN to the secret object

Step4 — External Access Integration

Enables access to external network locations from User-Defined functions or Stored Procedure.

Combing everything together…

We can write a function using the external access integration and secrets object to make a call to google api, all within Snowsight using Snowpark library

Let’s run some validation in Snowsight to ensure everything is working as expected

select google_sheet('<<your_google_sheet_id>>', '<SheetName>!<Range>');

Example - 
select google_sheet('1v-hPTRoOiYnmcGT2WPWbQq2JZyDsXf-la74zBq0vk', 'EV Population Data!A:Q');

We can optionally create a Stored Procedure in Snowflake to return us the Google spreadsheet dataset as a DataFrame

Now let’s use the python stored procedure in Streamlit to access google spreadsheet data in Snowflake

Streamlit App let you preview data, run some checks before you decide to load the dataset using ‘Override’ or ‘Append’ mode into the target table

Here is the complete code for the Streamlit App

You can now easily share this final product with your internal business users who can import google spreadsheet data securely and with a click of a button using Streamlit in Snowsight feature.

……………….………………It’s Snowing on a SunnyDay…!!! ………………………….