Read from and write into different worksheets using the same Google Sheets Connection

Overview

In Etlworks, the name or index of the Google Sheets worksheet is configured in the Google Sheets Connection.

Process

Specify worksheet name or index in the FROM or TO

If you want to use the same Connection to read from or write into different Google Sheets worksheets,  use the Spreadsheet ID|worksheet notation in the FROM or TO when creating the source-to-destination transformation.

For example: 1Ni3C3nO6Qtuc6wGXVd3WLKIdApXQ4B-S6r7RAK74ab|income tells the system to read data from the worksheet income in the spreadsheet with the ID 1Ni3C3nO6Qtuc6wGXVd3WLKIdApXQ4B-S6r7RAK74ab. 

You can use the worksheet index instead of the name. Index starts from 1.

Select data from the specific worksheet using Source query

The alternative to using the worksheet name or index in the FROM is to select data from the specific worksheet using the Source query. 

For example: select * from [income], where income is a worksheet name. Always enclose the worksheet name or index in [].

While it does look intuitive to use the SQL, it automatically disables the data streaming, so all the data in the worksheet will be stored in memory. Large worksheets could cause the Out of Memory error so we recommend specifying the worksheet name or index in the FROM.

Select data from the specific worksheet in the Etlworks Explorer

Use select fields from [worksheet_name_or_index] optional_where_clause to view the data in the specific worksheet when browsing data in the Etlworks Explorer.

For example: select * from [invoices], where invoices is a worksheet name. 

Rules:

• Always enclose the worksheet name or index in []. 
• You can use the worksheet index instead of the name. Index starts from 1.