Writing

Operation Name

Writing

Function overview

Writes the input data to the specified 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.
Input data	Required	Not available	Select the component 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.	If the specified spreadsheet does not exist, this operation creates a new spreadsheet whose name is specified in this and writes to it. The created spreadsheet has the "Sheet1" sheet.
Sheet Name	Optional	Available	Select or input the sheet name of the spreadsheet file specified in [File Name].	If omitted, this operation writes to the first sheet. If the specified sheet does not exist, this operation creates the sheet and writes to it. Does not distinguish on upper/lower cases.
Columns	Optional	-	Specify column name for each 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	Specify column name for each column.

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 [Specify Start Cell Position To Write] is checked, the rows meet the criteria specified in the starting condition become the row of interest.

Setting Of Writing

Item name	Required/Optional	Use of Variables	Description	Remarks
Insert To The First Row	Optional	Not available	Select whether to insert the name of the column in the first row of the spreadsheet specified. [Checked]: Inserts column names into the first row. [Not Checked]: (default) Inserts no column name.
Overwrite	Optional	Not available	Select whether to overwrite if the spreadsheet specified already exists. [Checked]: (default) Overwrites if the specified spreadsheet does not exist. This operation creates the spreadsheet whose name is specified in [File Name] and writes to it. [Not Checked]: Does not overwrite. If the specified spreadsheet exists, the SpreadsheetAlreadyExistedException occurs and this operation fails to execute.
Deletion Of Target Sheet	Optional	Not available	Select whether to delete the specified sheet before writing data to the specified spreadsheet. [Checked]: Deletes. Ignores if the sheet does not exist. [Not Checked]: (default) Does not delete.	Enabled only when [Overwrite] is checked. If a sheet is deleted and written, position of the sheet will be at the right edge. If this operation failed to delete the sheet and the WorksheetDeleteFailedException occurred, a temporary sheet may remain in the spreadsheet.
Specify Start Cell Position To Write	Optional	Not available	Select whether to specify the start position for the write operation. [Checked]: Specify the start position for the write operation. [Not Checked]: (default) The start position for the write operation is not specified.
Specified Condition To Start Writing	Optional	Not available	Select the condition for the cell where the read operation begin with. [Specify Cell Address]: (default) Starts the process of writing from the cell specified at [Cell Address To Start]. [Specify Start Cell Value]: Starts the writing process from the cell that matches with [Start Cell Value].	Enabled if [Specify Start Cell Position To Write] has been checked. By specified selecting method, switch corresponding input field ([Cell Address To Start] / [Start Cell Value]).
Cell Address To Start	Optional	Available	Enter the cell address as the condition to start writing. Example: A1	Enabled, if [Specify Cell Address] is selected at [Specified Condition To Start Writing]. If omitted, this operation writes from A1.
Start Cell Value	Optional	Available	Enter the value of the cell used as criteria for the writer operation to begin at.	Enabled, if [Specify Cell Address] is selected at [Specified Condition To Start Writing].
Specify Batch Size To Writing	Optional	Not available	Select whether to specify the number of rows at each batch. [Checked]: Specifys the number of rows of each batch. [Not Checked]: (default) Does not specify the number of rows of each batch.	If checked, data writing is performed several times. Thus there will be less memory usage but it will take more processing time. Also, if data writing is failed at the second time or later, the existing data will remain on the spreadsheet. If not checked, data writing is performed at once. Thus data will not remain even if the process is failed. The process will take more time and the memory usage will increase according to the data amount.
Batch Size Of Writing (Rows)	Required	Available	Enter the number of rows to be written in a batch.	The default is "1000".

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

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

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

Output Schema

None.

Loading schema in Mapper

Schema is loaded automatically.

See "Edit Schema" for any details .

Transaction

Transaction is not supported.

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 written.	The value defaults to null.
folderPath	Returns the folder path to the spreadsheet to which the data is written.	The value defaults to null.
fileName	Returns the name of the spreadsheet to which the data is written.	The value defaults to null.
message_category	Stores 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, when an error occurs.	The value defaults to null.
message_level	Stores the severity of the corresponding message code, when an error occurs.	The value defaults to null.
error_type	Stores the type of the error for 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 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.

Specification Limits

About null and empty character
- If the value is null, no data will be written.
- If the input value specified for a cell is an empty character, whatever the value of this cell will be overwritten with the empty value.
All data will be written as string type data.
- When you write numeric or date/time data, convert data into the string which can be displayed in the "Formula" section when you select the target cell on the spreadsheet in the spreadsheet.
  - There are cases where strings in a format other than the format displaying in the "Formula" section may be written in as numeric, or date/time data.
- If currency data is written, "U+00A5" in Unicode must be used for yen sign.
It is not possible to insert a row and write to it.

The number of rows and columns are added accordingly to the region of the spreadsheet specified.

Format, Fonts, and background color used follow the specification of the target platform. Format cannot be specified.

Function, Formulas, Comments, Gadgets, Graphs, Images, Drawings, and script invocations are not supported.

About Spreadsheet and folders with the same name
- If there is more than one spreadsheet under the same name exists in the file specified by the [File Name], it is not guaranteed to which one of them data is written to.
- If more than one folder exists under the same name that is specified in the [Folder Path], it is not guaranteed which one of those folders will be used.
Only the files/folders located under "My Drive" can be accessed.

Main exceptions

Exception name	Causes	Solution
InputDataNotFoundException	[Input data] is not specified.	Specify the [Input data], or draw the data flow.
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].
SpreadsheetAlreadyExistedException	The spreadsheet specified in the [File Name] exists.	Either modify the [File Name] or check the [Overwrite] defined in the [Setting Of Writing].
InvalidCellAddressException The cell address specified is invalid. Cell address:[<Cell Address>]	[Cell Address To Start] invalid address entered in the cell.	[Cell Address To Start] address please check the correct cell.
WorksheetUpdateFailedException Failed to set the row/column number for the target spreadsheet. Sheet name:[<sheet name>] column number:[<column number>] row number:[<row number>]	Failed to set the row/column number for the target spreadsheet. The spreadsheet could be modified by other operations while executing.	Confirm that there are not other operations which modified the spreadsheet at the same time this operation executed.
WorksheetDeleteFailedException Failed to delete the worksheet. Sheet name:[<sheet name>] temporary sheet name:[<temporary sheet name>]	Failed to delete the worksheet. The spreadsheet could be modified by other operations while executing.	Confirm that there are not other operations which modified the spreadsheet at the same time this operation executed. And temporary sheet [<sheet name>] may remain in the spreadsheet.
BatchFailedException	Error occurred during while writing data to the spreadsheet.	Please check the error message and resolve the problem.
com.google.gdata.util.ServiceForbiddenException	No authority to update the spreadsheet is granted.	Make sure that sufficient authority is granted for the write operation in the sharing settings.
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 below errors occur and the process is failed if you execute write processing in multi-threads to the same spreadsheet, or if you click Update or Delete on the spreadsheet of the writing destination while in process.
- WorksheetUpdateFailedException: Failed to set the row/column number for the target spreadsheet. Sheet name:[<sheet name>] column number:[<column number>] row number:[<row number>]
- WorksheetDeleteFailedException: Failed to delete the worksheet. Sheet name:[<sheet name>] temporary sheet name:[<temporary sheet name>]
- BatchFailedException: Batch Interrupted (some operations might have succeeded) : a response has already been sent for batch operation update id='R1C1']]
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.
Refer to " Notes on loop (number of data) " for the case of placed in loop.