> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cloud.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Google Sheets

> This page outlines the steps to install and configure the CData Connect Spreadsheets add-on for Google Sheets. After installation, Google Sheets is able to pull data from sources that you have connected to your CData Connect Spreadsheets account.

## Prerequisites

Before you can configure and use Google Sheets with Connect AI, you must first connect a data source to your Connect AI account. See [Sources](/en/Sources) for more information.

**Connect AI Customers Only:** You can also import Workspaces and Derived Views. To create a workspace, follow the instructions in [Workspaces](/en/Workspaces). To create a derived view (administrators only), follow the instructions in [Create a Derived View](/en/Data-Explorer#create-a-derived-view).

## Installation and Setup

<Steps>
  <Step>
    Sign in to [Google Sheets](https://docs.google.com/spreadsheets/) and either open a spreadsheet or create a new spreadsheet.
  </Step>

  <Step>
    With a spreadsheet open, select **Extensions** > **Add-ons** > **Get add-ons**.

    <Frame>
      <img src="https://mintcdn.com/cdata/U1GIlo3WoTfP-zjY/en/images/sheets_getaddon.png?fit=max&auto=format&n=U1GIlo3WoTfP-zjY&q=85&s=aa3c94069fd91369f1eb292f5ec41f38" alt="Sheets Get Add-on" width="891" height="286" data-path="en/images/sheets_getaddon.png" />
    </Frame>
  </Step>

  <Step>
    Search for *CData* in the search bar and click the **CData Connect Spreadsheets** add-on.

    <Frame>
      <img src="https://mintcdn.com/cdata/6FDv4aMDihHt3ws_/en/images/google_sheets_marketplace.png?fit=max&auto=format&n=6FDv4aMDihHt3ws_&q=85&s=d600be8af60ff48ba59afd8bae0eeb70" alt="Google Sheets Marketplace" width="788" height="670" data-path="en/images/google_sheets_marketplace.png" />
    </Frame>
  </Step>

  <Step>
    Click **Install** and then **Continue** on the pop-up.
  </Step>

  <Step>
    Select your Google account and sign in if needed. When prompted to approve the connection, click **Allow**.
  </Step>

  <Step>
    Return to the spreadsheet. Select **Extensions** > **CData Connect Spreadsheets** > **Open**.
  </Step>

  <Step>
    The configuration pane appears to the right of your spreadsheet. Click **Authorize** to sign in to CData Connect Spreadsheets.
  </Step>

  <Step>
    Enter your CData Connect Spreadsheets credentials and click **Continue**.
  </Step>

  <Step>
    When a success message appears, close the tab and return to Google Sheets.
  </Step>
</Steps>

After you establish a connection, the CData Connect Spreadsheets navigation menu appears:

<Frame>
  <img src="https://mintcdn.com/cdata/dJhcR4yZCQgD8aFQ/en/images/connect_spreadsheets_full.png?fit=max&auto=format&n=dJhcR4yZCQgD8aFQ&q=85&s=48fc42ae2faee14d2407708d5594328b" alt="Connect Spreadsheets Full" width="529" height="1097" data-path="en/images/connect_spreadsheets_full.png" />
</Frame>

## Set Up Connection

If you do not yet have the data connection you need for CData Connect Spreadsheets, you need to set one up.

<Steps>
  <Step>
    In the add-in pane, click **Connections**. Click **Add Connection**.
  </Step>

  <Step>
    Select the connector for the data you want to connect to Google Sheets. Use the **Search** field to find the connector.
  </Step>

  <Step>
    Enter your data connection settings by following the instructions on the connection page. Save and test your connection.
    After you have a successful connection, you can [Import Data](#import-data).
  </Step>
</Steps>

<Note>You can also use the **Connections** option to edit existing connections.</Note>

## Import Data

To import data from a data connection, follow these steps:

<Steps>
  <Step>
    Click **Import**.
  </Step>

  <Step>
    Select an option from the drop-down menu: **Connections**, **Workspaces**, or **Derived Views**. Then follow the directions for the option you selected.
  </Step>
</Steps>

<Note>
  Only Connect AI customers can access workspaces and derived views.
</Note>

### Import Connections

<Steps>
  <Step>
    Select a **Connection** from the drop-down list.
  </Step>

  <Step>
    Select either **Query Builder** or **Custom SQL**.

    * For Query Builder, select a schema (if there are multiple schemas), table, and columns. If desired, you can also set [filters](#filters), [sorting options](#sorting), and limits. View the generated query and adjust if necessary.
    * For Custom SQL, enter the SQL Statement in the provided field.
  </Step>

  <Step>
    Click **Execute**.

    <Frame>
      <img src="https://mintcdn.com/cdata/L-wC-3th9ZUn-60n/en/images/sheets_querybuilder.png?fit=max&auto=format&n=L-wC-3th9ZUn-60n&q=85&s=2e6e4fcc35b2f5a63a45affebb48fa0f" alt="Sheets Query Builder" width="304" height="814" data-path="en/images/sheets_querybuilder.png" />
    </Frame>
  </Step>

  <Step>
    When prompted, choose to output the data in either the current spreadsheet or a new one.
  </Step>
</Steps>

### Import [Workspaces](/en/Workspaces) (Connect AI Customers Only)

<Steps>
  <Step>
    Select a workspace from the drop-down list.
  </Step>

  <Step>
    Select either **Query Builder** or **Custom SQL**.

    * For Query Builder, select a workspace and the columns to display. If desired, you can also set [filters](#filters), [sorting options](#sorting), and limits. View the generated query and adjust if necessary.
    * For Custom SQL, enter the SQL Statement in the provided field.
  </Step>

  <Step>
    Click **Execute**.
  </Step>

  <Step>
    When prompted, choose to output the data in either the current spreadsheet or a new one.
  </Step>
</Steps>

### Import [Derived Views](/en/Data-Explorer#configure-derived-views) (Connect AI Customers Only)

<Steps>
  <Step>
    Select either **Query Builder** or **Custom SQL**.

    * For Query Builder, select a derived view and the columns to display. If desired, you can also set [filters](#filters), [sorting options](#sorting), and limits. View the generated query and adjust if necessary.
    * For Custom SQL, enter the SQL Statement in the provided field.
  </Step>

  <Step>
    Click **Execute**.
  </Step>

  <Step>
    When prompted, choose to output the data in either the current spreadsheet or a new one.
  </Step>
</Steps>

## Refresh Data

To update the imported data in your spreadsheets from the originating source, click **Refresh** in the main menu of the CData Connect Spreadsheets add-on. (Click the back arrow to return to the main menu of the add-on, if necessary.) Then follow these steps:

<Steps>
  <Step>
    Select the checkboxes next to the Google Sheets spreadsheets that you want to update.

    <Frame>
      <img src="https://mintcdn.com/cdata/L-wC-3th9ZUn-60n/en/images/sheets_refresh.png?fit=max&auto=format&n=L-wC-3th9ZUn-60n&q=85&s=ad24afd279860304c1ec04d7479ba277" alt="Sheets Refresh" width="379" height="295" data-path="en/images/sheets_refresh.png" />
    </Frame>
  </Step>

  <Step>
    Select a refresh option:

    * Click **Refresh Now** to manually refresh the data as soon as possible.
    * Click **Auto Refresh** to open a dialog where you can select the auto-refresh interval in hours. Please note that this interval applies to all spreadsheets that use Auto Refresh.
  </Step>

  <Step>
    The data in the selected Google Sheets spreadsheets update with new data.
  </Step>
</Steps>

<Note>
  If Auto Refresh is selected, a new Auto Refresh Status field appears. It lists the refresh interval and the affected spreadsheets. A **Reset Auto Refresh** button allows you to stop the auto refresh and set up a new one.
</Note>

<Frame>
  <img src="https://mintcdn.com/cdata/U1GIlo3WoTfP-zjY/en/images/sheets_autorefresh.png?fit=max&auto=format&n=U1GIlo3WoTfP-zjY&q=85&s=f232b9476fc8f5ccadb2cc4521a47833" alt="Sheets Auto Refresh" width="371" height="239" data-path="en/images/sheets_autorefresh.png" />
</Frame>

## Update Data

You can push changes from the Google Sheets spreadsheet to the originating data connection. Note that in order to update a spreadsheet, the data must contain at least one primary key. You must also have the proper permissions to update the source data. You cannot update read-only fields in the originating data connection.

Be sure to update your data frequently if you are using the auto refresh feature, or your changes may be overwritten by the originating data connection.

<Note>
  CData Connect Spreadsheets does not support updates for views (only tables).
</Note>

To update data in your spreadsheet, follow these steps:

<Steps>
  <Step>
    Make your changes to the Google Sheets spreadsheet. When you update data, the data is highlighted in red to signify that it is not yet updated to the originating data connection.
  </Step>

  <Step>
    If you want to update only select rows, highlight the cells or rows to be updated.
  </Step>

  <Step>
    On the **CData Connect Spreadsheets** add-on pane, click **Update**.
  </Step>

  <Step>
    Determine whether you want to **Update Selected** rows or **Update All**.
  </Step>

  <Step>
    Click **Execute**. Click **Confirm** to continue. This action updates the originating data connection and cannot be undone.
  </Step>

  <Step>
    CData Connect Spreadsheets returns a message whether the update was successful. If unsuccessful, CData Connect Spreadsheets displays the reason the update failed.
  </Step>

  <Step>
    If the update is successful, the data in red turns black. This indicates that the data is updated in the originating data connection.
  </Step>
</Steps>

## Insert Data

To insert a row or rows in a spreadsheet, follow these steps:

<Steps>
  <Step>
    If necessary, return to the main menu of the CData Connect Spreadsheets add-on.
  </Step>

  <Step>
    Use the Google Sheets function to insert rows in your spreadsheet (**Insert** > **Rows**).
  </Step>

  <Step>
    Enter the information into the row or rows. When you add data, the data is highlighted in red to signify that it is not yet updated to the originating data connection.
  </Step>

  <Step>
    If you want to update only the inserted row(s), highlight the row(s).
  </Step>

  <Step>
    On the **CData Connect Spreadsheets** add-on pane, click **Update**.
  </Step>

  <Step>
    Determine whether you want to **Update Selected** rows or **Update All**.
  </Step>

  <Step>
    Click **Execute**. Click **Confirm** to continue. This action inserts the data into the originating data connection and cannot be undone.
  </Step>

  <Step>
    CData Connect Spreadsheets returns a message whether the insertion was successful. If unsuccessful, CData Connect Spreadsheets displays the reason the insertion failed.
  </Step>

  <Step>
    If the insertion is successful, the data in red turns black. This indicates that the data is updated in the originating data connection.
  </Step>
</Steps>

## Delete Data

<Note>
  CData Connect Spreadsheets does not support deletion for views (only tables).
</Note>

To delete rows from a spreadsheet, follow these steps:

<Steps>
  <Step>
    If necessary, return to the main menu of the CData Connect Spreadsheets add-on.
  </Step>

  <Step>
    Select the rows you want to delete and click **Delete**. You can select any cell(s) in the row and CData Connect Spreadsheets deletes the entire row. CData Connect Spreadsheets prompts if you are sure you want to delete the given number of rows.
  </Step>

  <Step>
    Click **Confirm** to continue with the deletion. CData Connect Spreadsheets displays whether it deleted the rows successfully.
  </Step>
</Steps>

<Note>
  You cannot undo the row deletion.
</Note>

## Logs

Click **Logs** to open a dialog that lists the most recent queries, including:

* The time and date they occurred
* Their results (success or failure)
* The contents and parameters of the queries

See [Logs](/en/Logs) for more information.

## Advanced Query Settings

When importing data, you can use [Filters](#filters) and [Sorting](#sorting) to build your query.

### Filters

To add a filter, click the **+** next to the **Filters** header. You can add more filters by clicking the **+** again, and you can delete a filter by clicking the trash can icon next to it.

<Frame>
  <img src="https://mintcdn.com/cdata/U1GIlo3WoTfP-zjY/en/images/sheets_filter.png?fit=max&auto=format&n=U1GIlo3WoTfP-zjY&q=85&s=4d42a5d17c7fd24cc4b62418fac8ca6c" alt="Sheets Filter" width="454" height="190" data-path="en/images/sheets_filter.png" />
</Frame>

Each filter has three fields to fill out:

* **Column**—select the column from your table that you want to filter.
* **Op**—the operation the filter performs. Options are *equals*, *does not equal*, *contains*, *does not contain*, *less than*, *less than or equal to*, *greater than*, and *greater than or equal to*.
* **Value**—the value for the filter operation.

For example, if you wanted to retrieve AccountValues above \$100,000, you might set the Column to *AccountValues*, the Op to *greater than*, and the Value to *100,000*. Then, when you execute the query, only results matching this filter will be returned.

As you enter your filter parameters, the **Generated Query** at the bottom of the pane automatically updates.

### Sorting

To add a sorting rule to your query results, click the **+** next to the **Sort By** header. You can add more sorting rules by clicking the **+** again, and you can delete a sorting rule by clicking the trash can icon next to it.

<Frame>
  <img src="https://mintcdn.com/cdata/L-wC-3th9ZUn-60n/en/images/sheets_sort.png?fit=max&auto=format&n=L-wC-3th9ZUn-60n&q=85&s=bb1bd627e8ebaff2f20339660fccfd71" alt="Sheets Sort" width="452" height="188" data-path="en/images/sheets_sort.png" />
</Frame>

Each sorting rule requires a **Column** and a sorting **Order** (ascending or descending). If you add multiple sorting rules, the results are sorted in the order that the rules appear. The query gives highest sorting priority to the first rule, then the second rule, etc.

As you enter your sorting parameters, the **Generated Query** at the bottom of the pane automatically updates.