Connector Dialog for Google Sheets

NOTE: The Google Sheets API only supports spreadsheets created within Google Sheets itself and does not support Excel files imported into the system.

Enter values into the dialog box to connect and fetch data from your Google Sheets data source. Select a specific tab to display specific options.
On the connector dialog, hover your mouse over the field name to display a description or help about that field. 

Connection Tab

Fill out the fields below to initiate a successful connection. Bold fields are required.

Notes

  • Optional Fields. Other fields are optional but you may need to consult your data source administrator for other field values you need to enter to connect. 
  • Connection Strings that Exceed 1020 characters. For Connection Strings that exceed 1020 characters, a File DSN is temporarily created. Refer to this page for details on the implementation.
  • Support for Short Names: Progress short names are supported by this release of Monarch Data Prep Studio, specifically in the Other Connection Options field. Refer to this page for details on the implementation.

Field

Description

Authentication Method

Determines the authentication method to be used during the course of the session.

Select the method from the drop-down list:

  • ServiceAccount . If you select this method you need to enter the Key File Path.

  • OAuth2.0. If you select this method you need to enter the ClientId and ClientSecret

Key File Path

This is a required field if you selected Service Account as the Authentication Method.

Enter the Service Aoount key file (in JSON format) containing the credentials.

Client ID

Enter the client ID key for your application when authenticating to REST endpoints with OAuth 2.0 enabled (AuthenticationMethod=OAuth2).

Note:  In some cases, the value of this option is the same as your user name or authenticating address. In others, this value is supplied with your Client Secret. If you experience and authentication error, verify that you are using the correct value.

Client Secret

Enter the client secret for your application when authenticating to REST endpoints with OAuth 2.0 enabled.

Important: The client secret is a confidential value used to authenticate the application to the service. To prevent unauthorized access, this value must be securely maintained.

Scope

Enter a space-delimited list of OAuth scopes that limit the permissions granted by an access token at the time of connection

Default value is:

https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file https://www.googleapis.com/auth/drive.readonly

FileQuery

Enter the query string to filter the files to return.

To search for a specific set of files or folders, use the query string q field with the files.list method to filter the files to return by combining one or more search terms.

A query string contains the following three parts:

query_term operator values

Where:

  • query_term is the query term or field to search upon.
  • operator specifies the condition for the query term.
  • values are the specific values you want to use to filter your search results.

Example

The following query string filters the search to only return folders by setting the MIME type:
q: mimeType = 'application/vnd.google-apps.folder'

Refer to this page for more details.

Range Column Start

Required. Specifies the start column number of the range.

Range Column End

Required. Specifies the end column number of the range.

Range Row Start

Optional. Specifies the start row number of the range. If no value specified, row start will be set to 0.

Range Row End

Optional. Specifies the end row number of the range. If no value specified, row start will be set to 1000.

First Row Is Headers

Select True to convert the first row in the sheet to a column header.

Default is True.

Reset

Select to True to reset the session. 

Default is False.

Application Name

Name of the application.

Default value is Monarch Google Sheets
Tables and View Tab

Tables and Views

 

Allows you to load, and then select, the tables from your database by manually selecting from a list.

To load tables and views via search and select:

  1. Select the Tables and Views tab.

  2. Click Load Tables to load a list of predefined tables or views. These are displayed on the Tables List (at the left side of the dialog box).

  3. Select a table from the Tables List.

    The columns of the table are displayed in the Columns List (beside the Tables List).

  4. Select the columns to add to your data table by checking the column boxes.

Note: If you wish to add a duplicate column, select this column from the Add a duplicate column drop-down list provided.

 

To select a column:

Check the boxes beside the columns you want to use. If you want to use all columns check the Column box on the header.

 

To aggregate columns:

 

If the data returned are to be aggregated, check the Aggregate box on the header, then select the aggregation method you want to use for each column. The following aggregation methods are possible for each data type:

 

To filter columns:

 

If the data returned are to be filtered, check the Apply Filter on the header. Then, for each column, select a filter method from the filter drop down and then enter the filter value in the Filter Value box.

Notes:

  • If you are unsure about the filter value format of Date field, hover on the Filter Value text box to display a tooltip hint:

  • Some data sources require case-sensitive filter values. Refer to this list to check which data sources have case-sensitive filters.
  • To load tables and views using an SQL Query, select the Query tab.

Preview Data

Click to fetch data using the credentials and criteria you have specified. This allows you to confirm your connection, ensure you have connected to the correct table, check your query, and verify the information that will be loaded.

Notes:

  • Only 10 records are displayed to allow you to verify that you are selecting the correct data. This limit prevents system slow down.

  • The button is only enabled if the connection is active and the query is correct. Check your connection or your query if one of the buttons is disabled.
Query Tab

Query

Allows you to load, and then select, the tables from your database by entering a SQL Statement.

If you already selected Tables and Views, this box is updated to show the corresponding  SELECT * FROM TABLE query.

If you want to define the query, enter the SELECT, EXEC, and/or WITH statements to execute.

Notes

  • To minimize security risks and prevent SQL Injection attacks:

  • Only Select, Exec, and/or With statements are allowed.

  • The following are NOT allowed:

    • Comment characters ( -- )

    • Semi-colons ( ; )

  •  In cases where you need to use a series of SQL statements with or without comments, we recommend you use stored procedures to contain these statements. The stored procedure can then be executed from the form.

  • The Preview Data and OK buttons are only enabled if the connection is active and the query is correct. Check your connection or your query if one of the buttons is disabled.

Preview Data

Click to fetch data using the credentials and criteria you have specified. This allows you to confirm your connection, ensure you have connected to the correct table, check your query, and verify the information that will be loaded.

Notes:

  • Only 10 records are displayed to allow you to verify that you are selecting the correct data. This limit prevents system slow down.

  • The button is only enabled if the connection is active and the query is correct. Check your connection or your query if one of the buttons is disabled.

OK/CANCEL

Select OK to establish the connection and fetch data or Cancel to close the dialog. 

The state of the OK and PREVIEW buttons provides a handy status about the state of your connection:

  • The OK and PREVIEW DATA buttons are enabled as soon as the required fields have been entered and verified. This usually means a connection to the data source has been established and a table has been selected.

  • The OK button is disabled when an update is made that can lead to an invalid result (e.g. the User Name or Password is not valid, or the the SQL statement in the Query tab is invalid). In such cases, use  PREVIEW DATA to validate that data can be retrieved. If this fails verify your connection information or query until data can be retrieved.

 

 

 

© 2024 Altair Engineering Inc. All Rights Reserved.

Intellectual Property Rights Notice | Technical Support