ETL+ for Google Sheets

How to connect your Google Sheets to ETL+ for data extraction.

Installation Checklist

• Click here to fill out the DataSelf Analytics Deployment Form.

• Click here for ETL+ pre-installation.

ETL+ Mapping to your Google Sheets

Preparing Google Sheets with Google

1. Grant ETL+ with rights to extract your Google Sheets:

   1. Open your Google Sheets spreadsheet

   2. Click Share

   3. Enter dsservice@ds-etlplus-api.iam.gserviceaccount.com into Email.

   4. Select Editor on the dropdown list

   5. Click Send.

Creating an ETL+ Google Sheets Source Object

1. Login to ETL+ (help here).

2. Click Add Source > Google Sheets on the https://dataself.atlassian.net/wiki/spaces/DS/pages/1993670657.

3. Enter Alias name. Give a name for the source object shown on the Source Objects panel.

4. Schema.

5. Spreadsheet ID.

   1. Copy the segment of the Google Sheet URL shown in the example below to the From your Google Sheets to Spreadsheet ID.

      

      Segment of URL to copy to Spreadsheet ID.

6. Target (optional). Name of Google spreadsheet.

   • To connect to all Google spreadsheet tabs leave Target (optional) empty.

   • To connect to a single spreadsheet tabs enter the sheet name in Target (optional). ,

7. Click Connect.

You should see the new Google Sheets source and Google sheet names on the Source Objects panel.

Maintaining an ETL+ Google Sheet Source Object

1. Maintaining a Google Sheet source: On ETL page’s left panel, right-click the Google Sheet source → Properties.

2. Mapping Tips: To map a group of cells from a Google Sheets table, use the name of the sheet followed by an exclamation mark, the column of the top left cell, its row, a colon, the row of the bottom right cell, and its column. Example: Sheet3!B3:D10.

   • All rows in one column: Sheet3!B:B (use the column name twice and omit the row numbers).

   • All rows in multiple adjacent columns: Sheet3!B:D (use the names of the first and last column in the range and omit the row numbers).

   • All columns in multiple adjacent rows: Sheet3!2:10 (use the numbers of the first and last row in the range and omit the column names).

Mapping and Loading Tables

• To view tables available from your Google Sheets source on the left panel, right-click the Google Sheets source → Refresh.

• To map a Google Sheets table to the data warehouse, double-click its name on the left panel, or right-click on it → Add Table.

  • You’ll now see the table added/mapped to the center panel.

• To preview its data, right-click the table on the center panel → Preview.

• To load its data to the data warehouse, right-click the table on the center panel → Load Now.

• Click the Log icon (top right) to view data load results. Log page help here.

Schedule the Data Load

• On the ETL main page, click Refresh Batch (top right icon on the center panel).

• Select a Refresh Batch or create a new one to load your Google Sheets source data on a schedule (help here) → on the right panel, be sure the Google Sheets source tables to load have their boxes checked and in the desired load order.

• To load them now, click the Refresh Batch Now (play icon) on the left panel for the desired Refresh Batch.

• To schedule the data load:

  • Click WTS Settings -> enter the WTS parameters → Apply.

  • This step might require you to run ETL+ app as “Run as administrator” in Windows.

• Click Close -> Close.

Testing

After ETL+ has loaded your Google Sheets source tables, this is to check if the data extraction process finished successfully and row count per extracted table matches with your Google Sheets source tables.

Steps

• Login to ETL+ (help here).

• On the ETL main page, click the top-right Log icon. Log page help here.

• Scroll down on the log page and double-check that no table load failed, and that the row count matches with the source Google Sheets tables. Following is an example highlighting two tables from a sample Google Sheets database and their loaded row count.

  

  Example of a log from a Google Sheets source

v2023.07