# Google Sheets

{% embed url="<https://youtu.be/eunlC7NFRus>" %}

### 1. Setup

To add a Google Sheets data source to the application, you first need to create a [Service Account](https://cloud.google.com/iam/docs/understanding-service-accounts) with Google. Service Accounts allow third-party applications to read data from your Google apps without needing to log-in each time. During Service Account setup you will be provided with a JSON key file. You need to upload this file to the application when setting up the data source.

### 2. How to create a Google Service Account?

1. Open the [API Credentials Page](https://console.cloud.google.com/apis/credentials). If prompted, select or create a project.
2. Click the “Create credentials” button. On the dropdown that appears, chose “Service account key”.
3. On the following page, use the dropdown to select the project you elected in step 1. For role select `Project > Viewer` from the tree menu.
4. Under key type, select JSON and hit “Create”

A `.json` file will then download to your computer. In the application under Settings, add a new data source for `GoogleSpreadsheet`. In the modal that appears, name this connection and upload the `.json` file you downloaded from the Google credentials console.

### 3. Querying

Once you have setup the data source, you can load spreadsheets into the application. To do so, you need to share the spreadsheet with the Service Account’s email address. This can be found in the [Google Sheets API credentials page](https://console.cloud.google.com/apis/api/sheets.googleapis.com/credentials) or in the JSON file under the `"client_email"` key. Sharing is done like you would share with any regular user.

After the spreadsheet is shared with your Service Account email address, create a new query in the application and select your Google Sheets data source. In the query editor text box, type your desired Spreadsheet ID. You can optionally select a specific tab of your spreadsheet by adding its tab position as a zero-indexed number separated by a vertical bar or pipe symbol.

For example:

```html
1DFuuOMFzNoFQ5EJ2JE2zB79-0uR5zVKvc0EikmvnDgk|0
```

to load the first sheet or

```html
1DFuuOMFzNoFQ5EJ2JE2zB79-0uR5zVKvc0EikmvnDgk|1
```

to load the second. That’s the whole query. Leave out any SQL at this point.

{% hint style="info" %}

**What is the Spreadsheet ID?**

You can find your Spreadsheet ID in its URL. So if the spreadsheet URL is:

```html
https://docs.google.com/spreadsheets/d/
b94d27b9934d3e08a52e52d7da7dabfac484efe37
```

Then the ID will be:

```html
b94d27b9934d3e08a52e52d7da7dabfac484efe37
```

{% endhint %}

{% hint style="warning" %}
This procedure might fail if your organization has restrictions on sharing spreadsheets with external accounts. To improve outcomes, be sure to create the Service Account with a Google account from the same organization.
{% endhint %}

### 4. Filtering The Data

When you connect a Google Sheet with the application, we load it in full. You can generate visualizations from the data and add it to your dashboards. If you want to filter some data or aggregate it beyond what a pivot table can accomplish, you can use one of the following methods:

* Use the [Query Results Data Source](https://docs.inverse.watch/user-guide/queries/querying-existing-query-results) which allows you to query results from other queries.
* Use [Google BigQuery’s integration with Google Drive](https://cloud.google.com/blog/big-data/2016/05/bigquery-integrates-with-google-drive) to create a Google BigQuery external table based on the Google Spreadsheet

### 5. A Note About Dates&#x20;

The application uses [Python-dateutil ](https://dateutil.readthedocs.io/en/stable/)to parse dates from Google Spreadsheets. If you experience issues where the application parses the date incorrectly, try adjusting the date formatting in your sheet to ISO8601 or one of the formats shown [here](https://dateutil.readthedocs.io/en/stable/examples.html#parse-examples).

### &#x20;<a href="#filtering-the-data" id="filtering-the-data"></a>


---

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

```
GET https://docs.inverse.watch/user-guide/data-sources/google-sheets.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
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.