Google Sheets Exporter

📘

For more information on the Google Sheets API please read the Google Sheets API documentation

Set up

---

Source

To get set up with the Google Sheets connector, you will only need your Google login. You will be able to ingest data into any spreadsheet your user has write permissions for or a new spreadsheet in your drive.

Reports

There are 4 reports New Sheet in New Spreadsheet, New Sheet in Existing Spreadsheet, Replace Sheet and Append Sheet.

For the Replace Sheet and Append Sheet reports you must specify a Spreadsheet ID and Sheet ID. For the New Sheet Report it is optional to specify a Spreadsheet ID but a Sheet ID is not necessary.

New Spreadsheet

A new workbook is created in your root folder within Google Drive with the name [Extract Name]-[Date] and the new data is populated in a Sheet called Sheet1.

New Sheet

You must specify a Spreadsheet by entering the Spreadsheet ID. The data in the chosen source table populates a new sheet with the name [Extract Name]-[Date] within the specified Spreadsheet.

Replace Sheet

You must specify a Spreadsheet ID and Sheet Name. The data within the specified sheet of the specified Spreadsheet is deleted and it is replaced by the data within the chosen source table.

Append Sheet

You must specify a Spreadsheet ID and Sheet ID. The data within the chosen source table is appended onto the bottom of the specified sheet within the specified spreadsheet.

🚧

Blank Cells

Some cells may appear blank but unless the value has been deleted these cells will be treated as if they are populated. To avoid data being inserted into the wrong column or row please ensure the value of any cell you wish to ignore has been deleted.

Identifiers

You can get the Spreadsheet ID from the URL of the spreadsheet you are looking to export into. See the example below:

https://docs.google.com/spreadsheets/d/<spreadsheet_id>/edit#gid=0

If the URL of your sheet is https://docs.google.com/spreadsheets/d/1VNdo9JyNFj5Bdg4v5AlutIEzzW3_0OYShRjhktFOvdQ/edit#gid=0

Spreadsheet ID - 1VNdo9JyNFj5Bdg4v5AlutIEzzW3_0OYShRjhktFOvdQ

Sheet Name - taken from the bar at the bottom of the spreadsheet (it is case sensitive).

G-Sheets Data Types

If Text is selected the data is loaded into Google Sheets with the data type set to text (dates will be pre-fixed with a quotation mark).

If Detect is selected Google Sheets converts the data type into what it believes is correct (this may mean some data is truncated or rounded).

Source Table

You need to choose the table within the warehouse you wish to export. The Database and Schema can be chosen from the relevant dropdowns where as the table name must be entered accurately as free text (it is not case sensitive).

Limitations

---

• It is not possible to export data if there are more than ~10,000 characters in a single cell
• The Append Sheet report expects the the columns within the destination sheet to remain the same. If a change to the order or number of columns is made the data may be exported into the incorrect column.
• All columns from the source table will be ingested into the sheet. Users are not able to choose specific columns. Please build a transform with only the neccessary columns if you wish to restrict the columns exported into the sheet.