Get area from table (Google services)

Get area from table Version 4 (Net)

Action group: Google services

Description

This action retrieves values from the cell area.

Action icon

Settings of parameters 

Special conditions of use

The Google Sheets API has a limit of up to 60 read-requests per user per minute. To ensure that the action does not immediately generate an error when the limit on the number of requests is reached, the action includes a loop operation that will resend requests within a minute.

• If the request is completed in that time, the action will complete successfully.
• If the request cannot be sent in this time, the robot will terminate with an error.

For "With header" checkbox

Parameter is action: If true, the action will save the first row of the source table into the header of the returned table value, i.e. it will not be a row with the table contents.
If “false”, the first row will leave the table content part, and the header will use the default names - 1, 2, 3, etc.

Example

Get the table:

With this parameter enabled, the log will show "Columns: 3; rows: 2" because the first row of the taken area has become a header with the column names "No.", "Title", and "Tests". These headings cannot be accessed as a row of data to read, but you can use the column names to access a specific cell or column (actions from the "Tables" group). If the option is off, the log will show "Columns: 3; rows: 3", the column names in the header will be "1", "2" and "3".

For "Remove header duplicates" checkbox

When "true", indexes will be added to identical column names in the header of the tabular value that the actions return as a result. The numbering starts from 1.

Example 1

There is a table:

If "Remove header duplicates" = true, and "With header" = True, the action "Get area form table" will return a table with 10 rows and 4 columns. The names of the columns in the table will be: "No.", "Title", "Tests1", "Tests2".

Example 2


If "Remove header duplicates" = true, and "With header" = True, the action "Get area form table" will return a table with 4 rows and 5 columns. The names of the columns in the table will be:
"Test31", "Test21", "Test22", "Test32", "Test1".

If "false", the column names will remain the same. Actions on net that return/process such tables with duplicated column names will crash with an error, because net cannot have a header with the same column names.

For example, the source table has columns "Test1", "Test" and "Test". If we add indexes only to the current doubles, the names will become "Test1", "Test1" and "Test2", i.e. new doubles with the name "Test1" will appear. Therefore, when renaming a particular column, the action should check if another column already has such a name and if there is such a match, the current column should have the next index. That is, in this case the action will rename the columns as "Test1", "Test2" and "Test3".

Example of use

Task

There is a Google sheet with values:

Take the area of values from cell "B1" to "C4". The result should be written to a variable of the table type.

Solution

Use the "Context Google tables", "Get area form table" actions.

Implementation

1. Assemble a robot scheme with actions:
   
2. Set the "Context Google tables" action parameters.
   
   Fill in the "The file path" and "Application name" fields (more about filling in these fields in section Instructions for setting up a connection to Google Sheets/Docs/Drive).
3. Set the "Get area from table" action parameters.
   
   In the ID field specify the part of the table's link.
   
4. Click "Start" in the top panel.

Result

The program robot completed successfully. The required range of values was obtained and saved to the variable "Table".