Omnata Product Documentation
  • Omnata Sync for Snowflake
    • What is Omnata Sync for Snowflake?
    • How it works
      • Terminology
      • Branching Mode
      • Sync Directions and Strategies
        • Outbound
        • Inbound
      • Rate Limiting
      • Access Control
      • Notifications
      • Security and Privacy
      • Internal tables
      • Internal Stored Procedures
      • FAQ
    • Step-by-step guides
      • 1. Install the Omnata Sync Engine
      • 2. Install the Omnata Plugin
      • 3. Configure the Plugin
      • 4. Create a connection
      • 5. Create a sync
    • Apps
      • Aerobotics
        • 📘Release History
      • Airtable
        • 📘Release History
      • Amazon Ads
        • Privacy Notice
      • ApprovalMax
        • 📘Release History
      • Bamboo HR
        • 📘Release History
      • Clockify
        • 📘Release History
      • Contentful
        • 📘Release History
      • GitHub
        • 📘Release History
      • Github
      • Google Ads
        • 📘Release History
      • Google Sheets
        • 📘Release History
      • HubSpot
        • 📘Release History
      • Hubspot
      • Infor Data Lake
        • 📘Release History
      • Jira
        • 📘Release History
      • LinkedIn Ads
        • 📘Release History
      • Mailgun
        • 📘Release History
      • Marketo
        • 📘Release History
      • Meta Marketing
        • 📘Release History
      • Microsoft Ads
        • 📘Release History
      • Microsoft Dynamics 365 Business Central
        • 📘Release History
        • 📘Release History
        • 📘Release History
        • 📘Release History
        • 📘Release History
      • Microsoft Entra ID
        • 📘Release History
        • 📘Release History
        • 📘Release History
        • 📘Release History
      • Microsoft Excel
      • Microsoft SQL Server
        • 📘Release History
      • Monday.com
        • 📘Release History
      • MRPeasy
        • 📘Release History
      • PayHero
        • 📘Release History
      • Pinterest Ads
        • Privacy Policy
      • PostgreSQL
        • 📘Release History
      • Salesforce
        • Salesforce Permissions needed
        • Formula Fields
        • How we use the Salesforce APIs
        • 📘Release History
      • Salesforce Marketing Cloud
        • OAuth for APIs, SFTP for file transfer with GPG on outbound
        • OAuth for APIs, SFTP for file transfer
        • OAuth for APIs, Cloud Storage for file transfer
        • 📘Release History
      • Shopify
        • Outbound sync data structures
        • 📘Release History
      • Slack
        • 📘Release History
      • Tiktok Ads
        • Privacy Policy
      • Typeform
        • 📘Release History
      • Wise
        • 📘Release History
      • Xero
        • 📘Release History
      • Zendesk Support
        • 📘Release History
        • 📘Release History
    • Plugins
      • Anatomy of a Plugin
      • Example Plugins
        • Example Plugin: Slack
        • Example Plugin: Zoho CRM
      • Creating Plugins
      • Advanced Plugin topics
        • Advanced rate limiting / concurrency
        • Custom Jinja filters
        • Custom Record Transformers
        • Dynamic Configuration Forms
        • Test case generation
    • Branching
      • Inbound Sync branching
      • Outbound Sync branching
    • Integrations
      • dbt
        • Validation Tests (coming soon)
    • 📘Release History
  • Omnata Connect for Salesforce
    • Overview
    • Getting Started
      • Install the Salesforce App
      • Connect to your data warehouse
        • Snowflake
        • BigQuery
        • Rockset
        • Firebolt
        • SingleStore (previously MemSQL)
      • Deciding which mode to use
    • Omnata with Salesforce Connect (External Objects)
      • Object Configuration
      • View your data in a list
      • Link to other objects
      • Use in a Report
      • Database schema changes
      • Writing to External Objects
    • Omnata with Salesforce Lightning Components
      • Object Configuration
      • View your data in a list
      • Link to other objects
      • Using the Lightning Component on a page
      • Assigning Permissions
    • Advanced Features
      • Row Level Filtering
      • Multi-Currency handling
        • About Multi-Currency
        • Support in Omnata Connect
        • Apex Features
    • Integrations
      • Datadog
    • Omnata with Salesforce Apex
    • Security
    • Use cases
      • Linked object drill-downs
      • Global Search
      • ERP and historical data
      • Embedded product metrics
    • Best Practices
      • Global Search
      • Change Management
      • Snowflake table design
      • Salesforce page layout
      • Salesforce Caching
Powered by GitBook
On this page
  • Prerequisites
  • Authentication methods
  • Inbound Syncs
  • Supported Sync Strategies
  • Outbound Syncs
  • Supported Targets
  • Supported Sync Strategies
  • Functions
  1. Omnata Sync for Snowflake
  2. Apps

Google Sheets

Google Sheets is a spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google.

PreviousRelease HistoryNextRelease History

Last updated 6 months ago

Prerequisites

You will need to enable, or have access to, the and the .

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:

  1. Visit and select your project

  2. Click "Create Credentials" and choose "OAuth Client ID" as the type

  3. Choose "Web Application" as the application type

  4. Add a redirect URI of the form https://apps-api.c1.<region>.<cloud-vendor>.app.snowflake.com/oauth/complete-secret

If you're unsure what value to for the redirect URI, the following Snowflake query will construct it for your account: select 'https://'||t.value:host::varchar||'/oauth/complete-secret' as URI from table(flatten(input => parse_json(system$allowlist()))) as t where t.value:host::varchar LIKE 'apps-api%';

  1. Click Create, and use the generated Client ID and Secret during Omnata connection creation.

  1. In the Omnata App, Create a Connection and follow the instructions to create and authorise the integration.

Service Account

To use a service account:

  1. Create a new key:

  2. Download the JSON file, copy its contents, and provide it during connection configuration

  3. On the details tab of the service account, copy the email address of the service account

  4. 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

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));

Create a in the appropriate GCP project

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.

service account
Google Drive API
Google Sheets API
https://console.cloud.google.com/apis/credentials