Search

Google Sheets

Google Sheets

This page outlines the steps to install and configure the CData Connect Connector for Google Sheets. After installation, Google Sheets will be able to pull data from sources that you have connected to your CData Connect account.

Note: Before you can configure and use Google Sheets with CData Connect, you must first connect a data source to your CData Connect account. See Connections for more information.

Installation and Setup

  1. Sign in to Google Sheets and open a spreadsheet.

  2. With a spreadsheet open, click Extensions in the navigation bar, highlight Add-ons, and click Get add-ons.

  3. Type CData Connect into the search bar and click the CData Connect add-on.

  4. Click Install and then Continue on the pop-up.

  5. Select your Google account and sign in if needed. When prompted to approve the connection, click Allow.

  6. Return to the spreadsheet. Click Extensions in the navigation bar, highlight CData Connect and click Open.

  7. The configuration pane appears to the right of your spreadsheet. Click Authorize to sign in to CData Connect.

  8. Enter your CData Connect credentials and click Continue.

  9. When a success message appears, close the tab and return to Google Sheets.

Import Data

After you establish a connection, the CData Connect panel shows these options:

  • Import
  • Refresh

  • Logs

Import

To import data from a connected data source follow these steps:

  1. Click Import.

  2. Select an option from the drop-down menu: Connections, Virtual Datasets, or Derived Views. Then follow the directions for the option you selected.

Import Connections

  1. Select a connection from the drop-down list.

  2. 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, sorting options, and limits. View the generated query and adjust if necessary.
    • For Custom SQL, enter the SQL Statement in the provided field.

  3. Click Execute.

  4. When prompted, choose to output the data in either the current sheet or a new one.

Import Virtual Datasets

  1. Select a workspace from the drop-down list.

  2. Select either Query Builder or Custom SQL.
    • For Query Builder, select a virtual dataset and the columns to display. If desired, you can also set filters, sorting options, and limits. View the generated query and adjust if necessary.
    • For Custom SQL, enter the SQL Statement in the provided field.

  3. Click Execute.

  4. When prompted, choose to output the data in either the current sheet or a new one.

Import Derived Views

  1. 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, sorting options, and limits. View the generated query and adjust if necessary.
    • For Custom SQL, enter the SQL Statement in the provided field.

  2. Click Execute.

  3. When prompted, choose to output the data in either the current sheet or a new one.

Refresh

To update the imported data in your spreadsheets, click Refresh in the main menu of the CData Connect extension. (Click the back arrow to return to the main menu of the extension, if necessary.) Then follow these steps:

  1. Select the checkboxes next to the Google Sheets spreadsheets that you want to update.

  2. 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.
  3. The data in the selected Google Sheets spreadsheets update with new data.

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

Logs

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

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

Advanced Query Settings

When importing data, you can use Filters and 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.

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, and greater than.
  • 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.

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.