Google Sheets connector#
The Google Sheets connector allows reading and writing Google Sheets spreadsheets as tables in Trino.
etc/catalog/example.properties to mount the Google Sheets connector
example catalog, with the following contents:
connector.name=gsheets gsheets.credentials-path=/path/to/google-sheets-credentials.json gsheets.metadata-sheet-id=exampleId
The following configuration properties are available:
Path to the Google API JSON key file
The base64 encoded credentials key
Sheet ID of the spreadsheet, that contains the table mapping
Maximum number of spreadsheets to cache, defaults to
How long to cache spreadsheet data or metadata, defaults to
Timeout when connection to Google Sheets API, defaults to
Timeout when reading from Google Sheets API, defaults to
Timeout when writing to Google Sheets API, defaults to
The connector requires credentials in order to access the Google Sheets API.
Open the Google Sheets API page and click the Enable button. This takes you to the API manager page.
Select a project using the drop down menu at the top of the page. Create a new project, if you do not already have one.
Choose Credentials in the left panel.
Click Manage service accounts, then create a service account for the connector. On the Create key step, create and download a key in JSON format.
The key file needs to be available on the Trino coordinator and workers.
gsheets.credentials-path configuration property to point to this file.
The exact name of the file does not matter – it can be named anything.
Alternatively, set the
gsheets.credentials-key configuration property.
It should contain the contents of the JSON file, encoded using base64.
The metadata sheet is used to map table names to sheet IDs. Create a new metadata sheet. The first row must be a header row containing the following columns in this order:
See this example sheet as a reference.
The metadata sheet must be shared with the service account user, the one for which the key credentials file was created. Click the Share button to share the sheet with the email address of the service account.
gsheets.metadata-sheet-id configuration property to the ID of this sheet.
The service account user must have access to the sheet in order for Trino to query it. Click the Share button to share the sheet with the email address of the service account.
The sheet needs to be mapped to a Trino table name. Specify a table name
(column A) and the sheet ID (column B) in the metadata sheet. To refer
to a specific range in the sheet, add the range after the sheet ID, separated
#. If a range is not provided, the connector loads only 10,000 rows by default from
the first tab in the sheet.
The first row of the provided sheet range is used as the header and will determine the column names of the Trino table. For more details on sheet range syntax see the google sheets docs.
Writing to sheets#
The same way sheets can be queried, they can also be written by appending data to existing sheets. In this case the service account user must also have Editor permissions on the sheet.
After data is written to a table, the table contents are removed from the cache described in API usage limits. If the table is accessed immediately after the write, querying the Google Sheets API may not reflect the change yet. In that case the old version of the table is read and cached for the configured amount of time, and it might take some time for the written changes to propagate properly.
Keep in mind that the Google Sheets API has usage limits, that limit the speed of inserting data.
If you run into timeouts you can increase timeout times to avoid
503: The service is currently unavailable errors.
API usage limits#
The Google Sheets API has usage limits,
that may impact the usage of this connector. Increasing the cache duration and/or size
may prevent the limit from being reached. Running queries on the
table without a schema and table name filter may lead to hitting the limit, as this requires
fetching the sheet data for every table, unless it is already cached.
Because Trino and Google Sheets each support types that the other does not, this connector modifies some types when reading data.
Google Sheets type to Trino type mapping#
The connector maps Google Sheets types to the corresponding Trino types following this table:
Google Sheets type
No other types are supported.
The connector provides specific Table functions to access Google Sheets.
sheet(id, range) -> table#
sheet function allows you to query a Google Sheet directly without
specifying it as a named table in the metadata sheet.
For example, for a catalog named ‘example’:
SELECT * FROM TABLE(example.system.sheet( id => 'googleSheetIdHere'));
A sheet range or named range can be provided as an optional
The default sheet range is
$1:$10000 if one is not provided:
SELECT * FROM TABLE(example.system.sheet( id => 'googleSheetIdHere', range => 'TabName!A1:B4'));