Google Sheets
Google Sheets is a spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google.
Prerequisites
You will need to enable, or have access to, the Google Drive API and the Google Sheets API.
You will also need to be able to create, or have access to, user OAuth credentials or a Service Account.
Authentication methods
OAuth (User Created)
This method uses your own Google Project to authorize access, and is the most secure method since your own OAuth credentials are used.
To generate credentials:
Visit https://console.cloud.google.com/apis/credentials and select your project
Click "Create Credentials" and choose "OAuth Client ID" as the type
Choose "Web Application" as the application type
Add a redirect URI of the form https://apps-api.c1.<region>.<cloud-vendor>.app.snowflake.com/oauth/complete-secret
Click Create, and use the generated Client ID and Secret during Omnata connection creation.

In the Omnata App, Create a Connection and follow the instructions to create and authorise the integration.
Service Account
To use a service account:
Create a service account in the appropriate GCP project
Create a new key:
Download the JSON file, copy its contents, and provide it during connection configuration
On the details tab of the service account, copy the email address of the service account
Open the sheet that you want to sync to/from, and invite the service account as viewer/editor by email address
Inbound Syncs
For each inbound sync, a single spreadsheet is selected and multiple sheets can be included in the sync.
Sheets need to be structured like a database table. The top row are the headers with data below, with no merged cells or blank space.
Supported Sync Strategies
Full Refresh
Outbound Syncs
Supported Targets
With the Google Sheets app, you copy the contents of a Snowflake table/view into a Google Sheet.
Supported Sync Strategies
Replace (a full replacement of sheet contents)
Functions
Our Plugin app comes with UDFS for interacting with Google Sheets via SQL in worksheets, notebooks and Streamlit apps. You can find these functions in the Plugin app database UDFS schema.
GET_SPREADSHEET_METADATA
Gets metadata about a Google Sheet
Parameters:
CONNECTION_SLUG (VARCHAR): The slug of the connection to query
SPREADSHEET_ID (VARCHAR): The ID of the spreadsheet
Example:
select (OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.GET_SPREADSHEET_METADATA(
CONNECTION_SLUG=>'my_google_sheets_connection',
SPREADSHEET_ID => 'spreadsheet_id')
LIST_SPREADSHEETS
Returns a list of spreadsheets
Parameters:
CONNECTION_SLUG (VARCHAR): The slug of the connection to query
SEARCH_ALL_DRIVES (BOOLEAN): Whether to search all drives
SHARED_WITH_ME (BOOLEAN): Show only files shared with the configured user
Example:
Returns a list of spreadsheets with name like...
select *
from table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.LIST_SPREADSHEETS(
CONNECTION_SLUG=>'my_google_sheets_connection'))
where Name like '%my_spreadsheet_name%';
LIST_SPREADSHEET_SHEETS
Returns a list of spreadsheets
Parameters:
CONNECTION_SLUG (VARCHAR): The slug of the connection to query
SPREADSHEET_ID (VARCHAR): The ID of the spreadsheet
Example:
Returns the list of sheets in a spreadsheet
select *
from table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.LIST_SPREADSHEETS(
CONNECTION_SLUG=>'my_google_sheets_connection',
SPREADSHEET_ID => 'spreadsheet_id'));
READ_SHEET
Returns the contents of a sheet
Parameters:
CONNECTION_SLUG (VARCHAR): The slug of the connection to query
SPREADSHEET_ID (VARCHAR): The ID of the spreadsheet
SHEET_NAME (VARCHAR): The name of the sheet
Example:
Returns the contents of a sheet
select *
from table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.READ_SHEET(
CONNECTION_SLUG => 'my_google_sheets_connection',
SPREADSHEET_ID => 'spreadsheet_id',
SHEET_NAME => 'Sheet1'));
WRITE_SHEET
Overwrite the contents of a sheet
Parameters:
CONNECTION_SLUG (VARCHAR): The slug of the connection to query
SPREADSHEET_ID (VARCHAR): The ID of the spreadsheet
RECORD (OBJECT): The row values
SHEET_NAME (VARCHAR): The name of the sheet (default null: use the first sheet)
INCLUDE_HEADERS (BOOLEAN): Whether to include headers in the output (default true)
HEADING_ORDER (ARRAY): The order of the columns in the output (default null: alphabetical order)
CLEAR_SHEET (BOOLEAN): Whether to clear the sheet before writing (default true)
INTERPRET_RAW (BOOLEAN): Whether to interpret the data as raw (default true)
Example:
with test as(
select 1 as INT_COL,'abc' as VARCHAR_COL
union
select 2 as INT_COL,'def' as VARCHAR_COL
)
select *
from test,
table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.WRITE_SHEET(
OMNATA_SYNC_ENGINE.API.PLUGIN_CONNECTION('google-sheets-test'),
'1qGaYzPo1stadlO0LbqTDl7vh55nHwTCtiWcCtlU1kCk',
object_construct('Integer Column',INT_COL,'Varchar column',VARCHAR_COL),
'Sheet1') over (partition by 1));
Last updated