> For the complete documentation index, see [llms.txt](https://docs.omnata.com/omnata-product-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.omnata.com/omnata-product-documentation/omnata-sync-for-snowflake/apps/google_sheets.md). # Google Sheets ## Prerequisites You will need to enable, or have access to, the [Google Drive API](https://console.cloud.google.com/apis/library/drive.googleapis.com) and the [Google Sheets API](https://console.cloud.google.com/apis/library/sheets.googleapis.com). 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 to the Google OAuth app {% hint style="info" %} If you're unsure what value to use for the redirect URI, the following Snowflake query will construct it for your account: ```sql 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%'; ``` The redirect URI is also displayed in the Connectivity step during the connection creation flow in Omnata. {% endhint %} 5. Click Create, and use the generated Client ID and Secret during Omnata connection creation.
6. In the Omnata App, Create a Connection and follow the instructions to create and authorise the integration. You will need the **Client ID**, **Client Secret** and **Subdomain** (usually the same as your company name or email domain) #### Service Account To use a service account: 1. Create a [service account](https://console.cloud.google.com/iam-admin/serviceaccounts) in the appropriate GCP project 2. Create a new key:
3. Download the JSON file, copy its contents, and provide it during connection configuration 4. On the details tab of the service account, copy the email address of the service account 5. Open the sheet that you want to sync to/from, and invite the service account as viewer/editor by email address ## Inbound Syncs #### Data structure requirements Sheets must be structured like a database table: * The specified header row contains column headers. * Data rows follow immediately below the headers. * No merged cells in the data range. * No blank rows or columns within the data. ### Sync modes Inbound syncs support three modes, chosen via the **Sync Mode** radio button on the inbound sync configuration form:
ModeHow it worksWhat you provide
Single FileOne sync targets one spreadsheet. Each sheet in the workbook becomes a stream. Full Refresh only.A Spreadsheet picked from a dropdown of spreadsheets the connection can see, plus a Header Row number.
Multi-File IndividualOne sync targets a folder and you get a stream per matched spreadsheet. It assumes only one sheet per file. Supports Full Refresh and Incremental (Google Drive modifiedTime as the cursor).A Parent Folder (Google Drive folder URL or ID), a filename regex, an optional comma-separated Primary Key Columns list, and a Header Row number.
Multi-File ConsolidatedAll matched spreadsheets are consolidated into a single stream. Column schema is the union of columns across every matched file. Supports Full Refresh and Incremental (Google Drive modifiedTime as the cursor)A Parent Folder, a filename regex, and a Header Row number.
Both multi-file modes preview the matched files under the regex so you can sanity-check the pattern before saving. #### Inbound sync options Both single-file and multi-file modes surface two dropdown filters that control how spreadsheets are discovered: | Field | Default | Notes | | ------------------------------------- | ------- | ---------------------------------------------------------------------------------------------- | | Only show spreadsheets shared with me | off | When on, uses the Drive `sharedWithMe` filter when listing spreadsheets. | | Search all shared drives | on | When on, includes all shared drives in the file search. Turn off to restrict to My Drive only. | #### Header row All three modes expose a **Header Row** input (1-indexed row number). Rows above the specified header row are skipped; the specified row is used as column headers; and data starts on the row immediately below. #### Primary key (Multi-File Individual) Multi-File Individual mode exposes a **Primary Key Columns** input. Provide a comma-separated list of column names that uniquely identify each row within a file. The connector applies these as the source-defined primary key on every stream, and composite keys are supported. ### Supported Sync Strategies * Full Refresh ## Outbound Syncs #### Supported Targets You can copy the contents of a Snowflake table or view into a specific sheet within a Google Spreadsheet. #### Supported Sync Strategies * **Replace** — writes the Snowflake data into the sheet, optionally clearing the sheet first (see options below). #### Outbound configuration options
FieldNotesDefault
Value Input OptionRAW (values stored as-is) or USER_ENTERED (values parsed as if the user typed them — dates, formulas, etc.). See Google's ValueInputOption reference.—
Include HeadersWhen on, the first row of the sheet contains column headers.off
Clear Sheet prior to writingWhen on, the target sheet is cleared before the new data is written. When off, only the range covered by the incoming data is overwritten — existing rows outside that range are left in place.on
Preserve original column orderWhen on, columns are written in the order supplied by the source table/view. When off, the plugin's default column ordering is used.off
## Functions The plugin exposes consumer-callable 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, typically `OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS` #### `GET_SPREADSHEET_METADATA` Gets metadata about a Google Sheet. Parameters: * `CONNECTION_SLUG` (VARCHAR) * `SPREADSHEET_ID` (VARCHAR) sql ```sql 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 the connection can see. Parameters: * `CONNECTION_SLUG` (VARCHAR) * `SEARCH_ALL_DRIVES` (BOOLEAN) * `SHARED_WITH_ME` (BOOLEAN) sql ```sql select * from table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.LIST_SPREADSHEETS( CONNECTION_SLUG => 'my_google_sheets_connection')) where Name like '%quarterly%'; ``` #### `LIST_SPREADSHEET_SHEETS` Returns the sheets within a spreadsheet. Parameters: * `CONNECTION_SLUG` (VARCHAR) * `SPREADSHEET_ID` (VARCHAR) sql ```sql select * from table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.LIST_SPREADSHEET_SHEETS( CONNECTION_SLUG => 'my_google_sheets_connection', SPREADSHEET_ID => 'spreadsheet_id')); ``` #### `GET_SHEET_HEADERS` Returns the header-row values from a sheet. Parameters: * `CONNECTION_SLUG` (VARCHAR) * `SPREADSHEET_ID` (VARCHAR) * `SHEET_NAME` (VARCHAR, optional — defaults to the first sheet) sql ```sql select OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.GET_SHEET_HEADERS( CONNECTION_SLUG => 'my_google_sheets_connection', SPREADSHEET_ID => 'spreadsheet_id', SHEET_NAME => 'Sheet1'); ``` #### `READ_SHEET` Returns the contents of a sheet. Parameters: * `CONNECTION_SLUG` (VARCHAR) * `SPREADSHEET_ID` (VARCHAR) * `SHEET_NAME` (VARCHAR) sql ```sql select * from table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.READ_SHEET( CONNECTION_SLUG => 'my_google_sheets_connection', SPREADSHEET_ID => 'spreadsheet_id', SHEET_NAME => 'Sheet1')); ``` #### `GET_SPREADSHEET_DATA_RAW` Reads a raw range from a spreadsheet using the Google Sheets API `values.get` call. Parameters: * `CONNECTION_SLUG` (VARCHAR) * `SPREADSHEET_ID` (VARCHAR) * `RANGE` (VARCHAR) * `FIELDS` (VARCHAR, optional) sql ```sql select OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.GET_SPREADSHEET_DATA_RAW( CONNECTION_SLUG => 'my_google_sheets_connection', SPREADSHEET_ID => 'spreadsheet_id', RANGE => 'Sheet1!A1:D100', FIELDS => NULL); ``` #### `WRITE_SHEET` Overwrite the contents of a sheet. Parameters: * `CONNECTION_SLUG` (VARCHAR) * `SPREADSHEET_ID` (VARCHAR) * `RECORD` (OBJECT) — the row values * `SHEET_NAME` (VARCHAR, default first sheet) * `INCLUDE_HEADERS` (BOOLEAN, default `TRUE`) * `HEADING_ORDER` (ARRAY, default `NULL` — alphabetical) * `CLEAR_SHEET` (BOOLEAN, default `TRUE`) * `INTERPRET_RAW` (BOOLEAN, default `TRUE`) sql ```sql with test as ( select 1 as INT_COL, 'abc' as VARCHAR_COL union all select 2 as INT_COL, 'def' as VARCHAR_COL ) select * from test, table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.WRITE_SHEET( CONNECTION_SLUG => 'google-sheets-test', SPREADSHEET_ID => '1qGaYzPo1stadlO0LbqTDl7vh55nHwTCtiWcCtlU1kCk', RECORD => object_construct('Integer Column', INT_COL, 'Varchar Column', VARCHAR_COL), SHEET_NAME => 'Sheet1') over (partition by 1)); ``` #### `CLEAR_SHEET` Clears cell contents in a sheet, either the whole sheet or a specific range. Parameters: * `CONNECTION_SLUG` (VARCHAR) * `SPREADSHEET_ID` (VARCHAR) * `RANGE` (VARCHAR, optional) — omit to clear the entire first sheet; supply an A1-notation range (e.g. `Sheet1!A2:D`) to clear just that range. sql ```sql select OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.CLEAR_SHEET( CONNECTION_SLUG => 'my_google_sheets_connection', SPREADSHEET_ID => 'spreadsheet_id', RANGE => 'Sheet1!A2:D'); ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.omnata.com/omnata-product-documentation/omnata-sync-for-snowflake/apps/google_sheets.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.