Google Sheets Reader Parameters

Service Connection

Information About FME and Web Connections

Connections store authentication information.

For general information about web connections, please see Using Web Connections.

For information about managing web services (including authorization credentials such as Client Id and Client Secret), please see Managing Web Services.

Connection

Select an existing connection, or Add Web Connection to define a new connection.

The new connection can be made visible only to the current user, or can be shared among multiple users.

Note: For production use, we recommend users obtain their own Client ID and Client Secret from the web service provider.

This parameter specifies a refresh token for OAuth 2.0 authentication to a specific Google account.

Notice: Refresh token handling is available to maintain compatibility with workspaces older than FME version 2018.0.

If you already know the token, you can copy/paste it into this parameter. To obtain a new token, click the browse button. This will prompt for a Google account authentication page from Google.

It is recommended that you save the defaults (from the Defaults button on the dialog) once the refresh token is retrieved so you will not have to re-authenticate in the future.

Constraints

Specifies the Spreadsheet to read. Two types of values are supported:

  • URL: To obtain the Spreadsheet URL, open the Google Sheet in a browser and copy the URL from the address bar. The domain in the URL must be docs.google.com or spreadsheets.google.com. It is an error if no such Spreadsheet exists, or if your account was not granted read access to the Spreadsheet.
  • Name: If the value is not a URL, it is assumed to be a name that uniquely identifies a Spreadsheet under your Google Drive account. It is an error if no such Spreadsheet exists.

A single Spreadsheet must be specified. To read from multiple Spreadsheets, use multiple instances of the Google Sheets Reader.

Click the Browse button to view a list of Worksheets under the selected Spreadsheet.

This parameter is optional. If it is not specified, all of the Worksheets in the selected Spreadsheet will be added to the workspace.

Schema Generation

These parameters are all optional. They influence the attributes produced on the schema, which determine which columns are requested from the server. Column indexes start at 1 or A.

This parameter influences schema generation.

If a row number is specified, the values in the specified row are interpreted as field names for the corresponding columns. These field names are used as attribute names in FME. Duplicate names are suffixed, leaving the name corresponding to the leftmost cell unsuffixed. Empty cells in the first row are not represented on the schema, and attributes from the corresponding columns will not be read.

If this parameter is not specified, then attribute names are alphabetical column labels (for example, A, B, C). The schema of feature types will have attributes spanning from Start Column to End Column. If End Column is not specified, then the attributes span to the last column of the worksheet.

Default: 1

The column from which to start creating user attributes. Optional. Not setting this parameter is equivalent to setting it to 1, or ‘A’.

The column at which to stop creating user attributes. Optional. User attributes on feature types will be created up to and including this column.

Advanced

  • Yes(default): Attribute results will contain the evaluated result of formulas. In addition, dates and numbers will also be read with formatting applied, such as currency symbols and decimal places.
  • No: Cells containing formulas are read back as formulas, and formatting is not applied to dates and numbers. This setting is useful for workflows where the intent is to copy or export worksheets to another spreadsheet inside or outside of Google Drive.

This parameter determines the behavior regarding blank cells. This behavior is configurable because the Google Sheets API makes no distinction between empty and null cell values.

  • Missing (default): No attribute will appear on the feature if the cell on the corresponding row is empty.
  • Null: Empty cells in each row are conveyed as null attributes on each feature.

This parameter determines approximately how many rows are retrieved in a single request. If not specified, a default value is used.

This parameter influences memory usage and progress logging, and does not need to be adjusted under most circumstances.

Use this parameter to expose Format Attributes in Workbench when you create a workspace:

  • In a dynamic scenario, it means these attributes can be passed to the output dataset at runtime.
  • In a non-dynamic scenario, this parameter allows you to expose additional attributes on multiple feature types. Click the browse button to view the available format attributes (which are different for each format) for the reader.