Access level: read, write
This connection enables seamless integration between Google Sheets and Jedox. The connection is necessary to extract and load data to and from Google Sheets.
| OAuth token | Authentication must use oath2RefreshToken. See OAuthToken Connection for details. |
| Google Sheet Name | Drop-down list shows the names and IDs of all the worksheets available in the connection, but once a selection is made, only the name will appear in the field. If a worksheet name is specified here, there is no need to also select a Google Sheet ID. If both a Google Sheet Name and a Google Sheet ID are identified in the connection, the ID will take precedence. If the desired worksheet name has a space at the beginning or end, the name should be entered into the input field with single quotes around it, e.g. ' sheet name '. |
| Google Sheet ID |
Drop-down list shows all IDs (and associated sheet names) available in the connection. Note that the IDs can be very long and will appear concatenated in the drop-down list. If ID is used, it will have priority over Name. The Google Sheet ID can be found in the URL for the spreadsheet (see example below).
|
| Worksheet | Default is "Sheet1" if no selection is made. |
| Worksheet range | range of cells in the desired worksheet, e.g. "!A1:D100". Default is the whole sheet. |
Accessing Google Sheets with Excel Connection
It is possible to use an Excel File connection to access Google Sheets, a web-based spreadsheet program that is part of Google Drive. The Google Sheet has to be published to the web in the format "Microsoft Excel (.xlsx)". It is convenient to activate the option "Automatic republish when changes are made".
Note that the published file doesn't carry over sharing permissions, so anyone will be able to access it. Make sure that the Google account allows this public publishing. In the ExcelFile connection, the URL of the published file must be used with location "HTTP". No password is required. As publishing is done with HTTPS, the SSL mode has to be set to "trust" or "verify".
Using Google sheets allows also the extraction from the web analytics service Google Analytics. Therefore the Google Analytics spreadsheet Add-on has to be installed in Google analytics. Create a Google Sheet for the data and set a suitable interval in the Add-on to refresh the data e.g. monthly. The Google Sheet has to be published to the web as described above and can then be used in the ExcelFile connection.
Updated August 27, 2024