# 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 <https://console.cloud.google.com/apis/credentials> 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 for the redirect URI, the following Snowflake query will construct it for your account:\
\
`select 'https://'||get(parse_json(SYSTEM$allowlist()),0):"host"::varchar||'/oauth/complete-secret' as REDIRECT_URL;`
{% endhint %}

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

<figure><img src="https://2119005510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FedNbhp7XNeTdK7we4Ka5%2Fuploads%2Fgit-blob-8a55f871aee82d3406ed3ad9ce675b6123f21c26%2FGoogle%20sheet%20client%20ID%20and%20secret.png?alt=media" alt=""><figcaption></figcaption></figure>

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:

   <div align="left"><figure><img src="https://2119005510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FedNbhp7XNeTdK7we4Ka5%2Fuploads%2Fgit-blob-826eadc63548be1a5880badb5683090326d1f124%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure></div>
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

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.\
![](https://2119005510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FedNbhp7XNeTdK7we4Ka5%2Fuploads%2Fgit-blob-332e0469f4e0eb3e7e9186eb2420ba0e0ea67fa4%2Fimage.png?alt=media)

#### 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:

```sql
select (OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.GET_SPREADSHEET_METADATA(
                CONNECTION_SLUG=>'my_google_sheets_connection',
                SPREADSHEET_ID => 'spreadsheet_id')
```

#### LIST\_SPREADSHEETS <a href="#soql_query" id="soql_query"></a>

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

```sql
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 <a href="#soql_query" id="soql_query"></a>

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

```sql
select * 
from table(OMNATA_GOOGLE_SHEETS_PLUGIN.UDFS.LIST_SPREADSHEETS(
                CONNECTION_SLUG=>'my_google_sheets_connection',
                SPREADSHEET_ID => 'spreadsheet_id'));
```

#### READ\_SHEET <a href="#soql_query" id="soql_query"></a>

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

```sql
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 <a href="#write_sheet" id="write_sheet"></a>

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:

```sql
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(
        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));
```