Google Format Deprecation Notice: The Google Sheets v3 API will be shut down by Google on June 8, 2021. FME uses this version of the API, so all Readers and Writers that use this format will stop working on June 8, 2021. See Google's deprecation notice for more information.
Safe Software will be creating a new Google Sheets Reader/Writer using the v4 API. This new format will be created as an FME Package that will be downloadable from the FME Hub.
Please follow the FME Community article Google Sheets API v3 Deprecation for more information.
Overview
The Google Sheets Reader/Writer plug-in provides FME with access to data within Google Sheets. This format can also work with data in Google Spreadsheets, the predecessor to Google Sheets.
- A single Google Sheet contains one or more worksheets.
- A worksheet corresponds to a feature type in FME.
- Each row in a worksheet corresponds to a feature in FME.
To reduce ambiguity, this document will refer to a Google Sheet as a spreadsheet.
Authentication
Google Sheets uses OAuth 2.0 authentication. The Google Sheets Reader/Writer accepts a Refresh Token directive instead of a username and password. After generating a Refresh Token, it should be saved as a default for reuse in FME. Refresh Tokens are normally valid indefinitely, but generating too many Refresh Tokens may invalidate previously-generated ones.
To obtain a refresh token: Click the browse button next to the Refresh Token field in the reader or writer parameters dialog.
The Google Sheets Reader/Writer requires a Request Token with a scope that includes both Google Drive and Google Sheets. Refresh Tokens are a secret - do not share it with others.
Styling
Styling options, such as fonts, text weight (bold), and background color are not available in the Google Sheets API, so the Google Sheets plug-in cannot read or write styling options. For cells containing dates or numbers, the Google Sheets Reader can be configured to read back values with date or number formatting options applied. See the Evaluate Formulas parameter on the Google Sheets Reader for details.
Reader Overview
The Google Sheets Reader supports reading any Spreadsheet accessible to your account, including ones published to the web.
The Google Sheets API conveys cell references in formulas using the R1C1 format. The Google Sheets Reader will always convert these references to the A1 style seen in the web interface.
No features are produced for blank rows.
Writer Overview
The Google Sheets Writer supports writing to any Spreadsheet for which your account has write access.
Note that writing behavior differs from most other FME writer plug-ins: rows cannot be truly deleted or inserted – they can only be cleared of values, or updated. It may be useful to think of the Google Sheets Writer as operating on a fixed matrix of cell values. The Google Sheets Writer must also read the current value of a cell in order to make any changes to it, as required by the Google Sheets API. This behavior is reported in the Workbench Log pane.
Similar to the Google Sheets web interface, formulas may be written simply by starting attribute values with an equal sign. Cell references may be specified in either the A1 format, or the R1C1 format. The Google Sheets Writer does not validate formulas.
Unlike the Google Sheets web interface, the Google Sheets Writer is unable to insert or delete rows in between existing rows of the Spreadsheet. This is a limitation of the Google Sheets Cells Feed API. In the context of the Google Sheets Writer, a Delete operation is equivalent to writing empty strings to the cells in a row. If there are not enough rows or columns in the worksheet to accommodate features, the worksheet will be resized to fit.
All feature types on the Google Sheets Writer must have at least one attribute defined on its schema. The order of attribute definition is important: when the Use Attribute Names As Column Positions parameter is set to No (the default), attributes correspond to columns in order of their definition, starting from the Start Writing At Column parameter. The Google Sheets Writer does not attempt to match attribute names to columns by reading the field names row of an existing worksheet.