Read Sheet

Operation Name

Read Sheet

Function overview

Reads a worksheet from the spreadsheet on Google Drive.

Data Model

Table model type.

Properties

For information about using variables, refer to "variables".

Basic settings

Item name	Required/Optional	Use of Variables	Description	Remarks
Name	Required	Not available	Enter the name on the script canvas.

Required settings

Item name	Required/Optional	Use of Variables	Description	Remarks
Connect To	Required	Not available	Select Global Resources. [Add]: Adds new global resource. [Edit list]: Global resource settings can be managed from "Edit Resource list".
Folder Path	Optional	Available	Select or enter an absolute path of a folder on Google Drive which starts with slash ("/").	The path becomes relative to the "/" root of the filesystem.
File Name	Required	Available	Enter the name of the spreadsheet.
Sheet Name	Optional	Available	Select a spreadsheet or enter the name for the spreadsheet to retrieve.	If the sheet name is omitted, or the specified sheet does not exist, the first sheet is read. Does not distinguish on upper/lower cases.
Columns	Optional	-	Specify the column. Each column can be operated with the following buttons. [Add]: Adds a column. [Up]: Moves the order of the selected column upwards by one. [Down]: Moves the order of the selected column downwards by one. [Delete]: Deletes the column.
Columns / Column Name	Optional	Available	Enter a column name.

Property Action

Item name	Description	Remarks
Refresh folder path list	Gets folder path in the specified destination and set to [Folder path].	If [Destination] is specified/edited, or add/edit of folder is performed on Google Drive, the edition can be reflected using this property action.
Refresh file list	Gets file name in the specified folder path and set to [File name].	If [Folder path] is specified/edited, or add/edit of folder is performed on Google Drive, the edition can be reflected using this property action. If variable is set to [Folder path], this action will be invalid.
Refresh sheet list	Gets sheet name from the specified file and set to [Sheet name].	If [File name] is specified/edited, or add/edit of sheet is performed on Google Sheet, the edition can be reflected using this property action. If variable is set to [Folder path] or [File name], this action will be invalid.
Refresh columns	Retrieves the column name from the value of the 1st row of the specified spreadsheet and set to the [Columns].	If [Sheet name] is specified/edited, or add/edit of column is performed on Google Sheet, the edition can be reflected using this property action. Disabled if the spreadsheet specified does not exist remotely or variables are used in [Folder Path] / [File Name] / [Sheet Name]. If [Start Condition] is specified, the rows meet the criteria specified in the [Start Condition] become the row of interest.

Start/End Condition

Item name	Required/Optional	Use of Variables	Description	Remarks
Specify Condition To Start Reading	Optional	Not available	Select whether to specify a condition to start reading. [Checked]: Specifies the condition to start reading. [Not Checked]: (default) Does not specify the condition to start reading.
Start Condition	Optional	Not available	Specify the condition to start reading. [Start From Cell Matched To Specified String]: (default) Starts reading from the cell that matches [Matched String]. [Starts From Specified Cell Address]: Starts reading from [Cell Address].	Enabled, when [Specify Condition To Start Reading] has been checked. According to the selected specifying method, switches to the corresponding input field ([Matched String] / [Cell Address]).
Matched String	Optional	Available	Enter the matching string as the condition to start reading.	Enabled when [Start From Cell Matched To Specified String] is selected in [Start Condition]. Does distinguishes on upper/lower cases.
Cell Address	Optional	Available	Enter the cell address as the condition to start reading. Example: A1	Enabled when [Starts From Specified Cell Address] is selected in [Start Condition]. If omitted, this operation reads from A1. Does distinguishes on upper/lower cases.
Specify Condition To Stop Reading	Optional	Not available	Select whether to specify a condition to end reading. [Checked]: Specifies the condition to end reading. [Not Checked]: (default) Does not specify the condition to end reading.
End Condition	Optional	-	Specify the condition to end reading. Each column can be operated with the following buttons. [Add]: Adds a column. [Up]: Moves the order of the selected column upwards by one. [Down]: Moves the order of the selected column downwards by one. [Delete]: Deletes the column.	Enabled when [Specify Condition To{ Stop Reading] is checked. Reads till the last row before the specified "end reading row". Please refer to " End reading condition" for details .
End Condition / Column Name	Optional	Not available	Select the column name to check [Column Name].
End Condition / Row Number	Optional	Available	Enter the row number to check the end reading condition. If [(Not specified)] is select, all the rows are checked for end reading condition.	If omitted, all the rows are checked for end reading condition.
End Condition / Condition	Optional	Available	Enter the value for the specified end condition. If [(Not specified)] is selected, regardless of the value of the cell, the row specified in [Row Number] is the row to end reading.	If omitted, check empty string as the condition value.
End Condition / Logical Operator	Required	Not available	Select the logical operator of [Condition]. [AND]:(default) [OR]:

Setting Of Reading

Item name	Required/Optional	Use of Variables	Description	Remarks
Ignore The First Row	Optional	Not available	Select whether to read the values from the first row of the specified spreadsheet. [Checked]: Does not treat as data. [Not Checked]: (default) Treats the data as.

Data Processing Method

Item name	Required/Optional	Use of Variables	Description	Remarks
Mass data processing	Required	Not available	Select a data processing method. [Use script settings]: (default) Applies mass data processing settings of script property to adapter. [Disable]: Does not perform mass data processing. [Enable]: Performs mass data processing.

Comment

Item name	Required/Optional	Use of Variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this adapter. The description will be reflected in the specifications.

Schema

Input Schema

None.

Output Schema

Depending on the [Columns] setting, the number of columns may be different.

See " Schema of Table Model " for details regarding schema structure .

Loading schema in Mapper

Schema is loaded automatically.

See "Edit Schema" for any details .

Mass data processing

Mass data processing is supported.

In mass data processing, when you save the result data in a file, data is temporary stored in the memory. Thus if data amount is enormous, OutOfMemoryError may occur in mass data processing. In that case, expand the capacity of memory allocation in DataSpiderServer.

PSP Usage

PSP is supported.

For details on PSP, refer to "Parallel Stream Processing".

Available component variables

Component variable name	Description	Remarks
count	Returns the number of rows read from the sheet.	The value defaults to null. Not value when using Parallel Stream Processing.
folderPath	Returns the folder path to the spreadsheet read.	The value defaults to null.
fileName	Returns the name of the spreadsheet read.	The value defaults to null.
message_category	Stroes the category to which corresponding message code belongs to, when an error occurs.	The value defaults to null.
message_code	Stores its corresponding message code of occured error.	The value defaults to null.
message_level	Stores the severity of the corresponding message code of the occured error.	The value defaults to null.
error_type	Stores the type of the occured error.	The value defaults to null. Error is represented in the format depicted below. Example:java.io.FileNotFoundException The message may vary depending on DataSpider Servista version.
error_message	Stores the error message for the occured error.	The value defaults to null. The message may vary depending on DataSpider Servista version.
error_trace	Stores stack trace of the occurred error.	The value defaults to null. The message may vary depending on DataSpider Servista version or the client application used.

Specify End Reading Condition

If [Specify Condition To Stop Reading] is not checked or [End Condition] is not specified, all rows in the spreadsheet will be read.

When multiple [OR] s are specified in [End Condition / Logical Operator], the following row is the row to end reading.
- Rows that match either condition
When multiple [AND] s are specified in [End Condition / Logical Operator], the following row is the row to end reading.
- If all of [(Not specified)] at [Row Number] are selected, a row that matches all the criteria on the same row.
- If a row number at [Row Number] is specified, the last row matching all criteria.
  Example:
  
  Spreadsheet to be read
  
  Row Number Column 1 Column2 Column3 Column4
  
  1 AAA BBB CCC DDD
  
  2 EEE FFF GGG HHH
  
  3 III JJJ KKK LLL
  
  4 AAA BBB CCC DDD
  
  5 EEE FFF GGG HHH
  
  6 III JJJ KKK LLL
  
  End reading condition (when row number is not specified)
  
  Column name Row Number Condition And/Or
  
  Column1 [(Not specified)] EEE [AND]
  
  Column2 [(Not specified)] FFF [AND]
  
  Read the end row and second row, only read the first row.
  End reading condition (when row number/condition is not specified)
  
  Column name Row Number Condition And/Or
  
  Column1 2 EEE [AND]
  
  Column2 5 FFF [AND]
  
  End reading row is 5th, reads till the 4th row.
  End reading condition (when row number is specified, condition is not specified)
  
  Column name Row Number Condition And/Or
  
  Column1 2 [(Not specified)] [AND]
  
  Column2 7 [(Not specified)] [AND]
  
  End reading row is 7th, reads till the 4th row.
  End reading condition (when condition that row number is specified and not specified is mixed)
  
  Column name Row Number Condition And/Or
  
  Column1 4 [(Not specified)] [AND]
  
  Column2 [(Not specified)] FFF [AND]
  
  End reading row is 5th, reads till the 4th row.
If both [Row Number] and [Condition] is [(Not specified)], reading ends without reading any row.

Row Number	Column 1	Column2	Column3	Column4
1	AAA	BBB	CCC	DDD
2	EEE	FFF	GGG	HHH
3	III	JJJ	KKK	LLL
4	AAA	BBB	CCC	DDD
5	EEE	FFF	GGG	HHH
6	III	JJJ	KKK	LLL

Column name	Row Number	Condition	And/Or
Column1	[(Not specified)]	EEE	[AND]
Column2	[(Not specified)]	FFF	[AND]

Column name	Row Number	Condition	And/Or
Column1	2	EEE	[AND]
Column2	5	FFF	[AND]

Column name	Row Number	Condition	And/Or
Column1	2	[(Not specified)]	[AND]
Column2	7	[(Not specified)]	[AND]

Column name	Row Number	Condition	And/Or
Column1	4	[(Not specified)]	[AND]
Column2	[(Not specified)]	FFF	[AND]

Specification Limits

Empty character is displayed as "null".

All data will be displayed as string type.
- The output format used to display numeric and date time values are what appears in "Formula" field upon selecting the target cell of the spreadsheet.
- The unicode character whose code point equals to U+00A5 represents the Yen currency format where applicable in the data retrieved.
For [End Condition / Column Name]
- If [Specify Condition To Stop Reading] is specified, configure [End Condition / Column Name] and directly change [Columns / Column Name], the target ending condition will not be used as a condition as it has a different column name. If [Columns / Column Name] has been changed directly, click [Finish] to close the properties screen. Then open the properties screen again and configure [Columns / Column Name].
- When multiple column name is set at [Columns / Column Name] and same name is specified at [End Condition / Column Name], the top of same name column in [Columns / Column Name] is used as appropriate.
Functions, Formulas, Comments, Gadgets, Charts, Images, Drawings, and Scripts are not supported to read.
- Reads the result of calculation from cells to which functions or formulas are set.
About duplicated spreadsheet name or folder name
- If two or more files/folders whose path is the same as the path of the spreadsheet specified in [File Name] exist, this operation reads one of them.
- If two or more folders whose path is the same as [Folder Path] exist, this operation reads one of them.
Only the files/folders located under "My Drive" can be accessed.

Main Exceptions

Exception name	Causes	Solution
ResourceNotFoundException Resource definition could not be found.Name: []	[Connect To] is not specified.	Please specify [Connect To].
ResourceNotFoundException Resource definition could not be found.Name: [<name of Global Resources>]	Resource definition selected in [Connect To] is not found.	Please check the global resource specified in [Connect To].
InvalidPropertyConfigurationException <Property name> is not specified.	[<Property name>] is not specified.	Specify the [<Property name>].
InvalidFolderPathException	The folder path specified in the [Folder Path] is invalid.	Please check [Folder Path].
SpreadsheetNotFoundException The spreadsheet specified could not be found. File name:[:<File name>]	The spreadsheet specified in the [File name] could not be found.	Make sure the [File Name] specified meets the following criteria: A spreadsheet which exists is specified. A file whose type is "Spreadsheet" or "Form" is specified.
InvalidCellAddressException The cell address specified is invalid. Cell address:[<Cell Address>]	[Cell Address] invalid address entered in the cell.	[Cell Address] address please check the correct cell.
InvalidRowNumberException The row number specified is invalid. row number:[<Row Number>]	The row number specified in the [End Condition / Row Number] is invalid.	Correct the row number specified in the [End Condition / Row Number].
java.lang.IllegalArgumentException Invalid extended help URI: Request rate limit exceeded.	Exceeded the limit on the number of Google Sheets API invocation allowed concurrently.	Reduce the number of the concurrent invocation.

Notes

HTTPS is used in the communication between Google Drive and this adapter.

New format of "spreadsheet" and "form" have been introduced by service update of Google Drive in January 2015 at present. The existing file on Google Drive will be transfered to the new format within 2015.
- This adapter corresponds to both new and old format of "spreadsheet" and the old format of "form".
- If new format of "form" is used, responses can be operated within this adapter by specifying spreadsheet to response destination.

The following error may occur because of excess of the restriction of the Google Sheets API when this operation runs simultaneously in multi-thread. In this case decrease the number of concurrencies.
- java.lang.IllegalArgumentException: Invalid extended help URI: Request rate limit exceeded.

Row Number	Column 1	Column2	Column3	Column4
1	AAA	BBB	CCC	DDD
2	EEE	FFF	GGG	HHH
3	III	JJJ	KKK	LLL
4	AAA	BBB	CCC	DDD
5	EEE	FFF	GGG	HHH
6	III	JJJ	KKK	LLL

Row Number	Column 1	Column2	Column3	Column4
1	AAA	BBB	CCC	DDD
2	EEE	FFF	GGG	HHH
3	III	JJJ	KKK	LLL
4	AAA	BBB	CCC	DDD
5	EEE	FFF	GGG	HHH
6	III	JJJ	KKK	LLL

Read Sheet

Operation Name

Function overview

Data Model

Properties

Basic settings

Required settings

Property Action

Start/End Condition

Setting Of Reading

Data Processing Method

Comment

Schema

Input Schema

Output Schema

Loading schema in Mapper

Mass data processing

PSP Usage

Available component variables

Specify End Reading Condition

Spreadsheet to be read

End reading condition (when row number is not specified)

End reading condition (when row number/condition is not specified)

End reading condition (when row number is specified, condition is not specified)

End reading condition (when condition that row number is specified and not specified is mixed)

Specification Limits

Main Exceptions

Notes

Row Number	Column 1	Column2	Column3	Column4
1	AAA	BBB	CCC	DDD
2	EEE	FFF	GGG	HHH
3	III	JJJ	KKK	LLL
4	AAA	BBB	CCC	DDD
5	EEE	FFF	GGG	HHH
6	III	JJJ	KKK	LLL