Official |[Google Sheets] Writing

Operation name

Writing

Function overview

Writes the input data to the specified spreadsheet on Google Drive.

Data model

The data model of this component is table model type.

Properties

= Remarks =

For details on use of variables, refer to Variables.

Basic settings

Item name	Required/Optional	Use of variables	Description	Remarks
Name	Required	Not available	Enter a name that is used on the script canvas.
Input data	Required	Not available	Select a component on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
Connect To	Required	Not available	Select a connection resource. Add A new connection resource can be added. Edit list Connection resource settings can be edited in > HULFT INTEGRATE > Connections.
Folder Path	Optional	Available	Select or enter an absolute path of a folder on Google Drive which starts with slash "/".	If omitted, the root folder "/" is used.
File Name	Required	Available	Enter the file name of the spreadsheet.	If the spreadsheet with the specified file name doesn't exist, a new spreadsheet with the specified file name is created and data is written to that file. The created spreadsheet has the "Sheet1" sheet.
Sheet Name	Optional	Available	Select or enter the sheet name of the spreadsheet specified in File Name.	If omitted, data is written to the first sheet. If the sheet with the specified sheet name doesn't exist, a new sheet with the specified sheet name is created and data is written to that sheet. Case-insensitive.
Columns	Optional	-	Specify column name for each column. Each column can be operated with the following buttons: Up Moves the selected column upward by one row. Down Moves the selected column downward by one row. Add Adds a column. Delete Deletes the column.
Columns/Column Name	Optional	Available	Specify a column name for each column.

Property actions

Item name	Description	Remarks
Refresh folder path list	Get folder paths in the specified destination and set them to Folder path.	If Destination is specified/edited or a folder has been added/edited on Google Drive, the changes can be reflected using this property action.
Refresh file list	Get file names in the specified folder path and set them to File name.	If Folder path is specified/edited or a file has been added/edited on Google Drive, the changes can be reflected using this property action. If a variable is set to Folder path, this action will be invalid.
Refresh sheet list	Get sheet names from the specified file and set them to Sheet name.	If File name is specified/edited, or a sheet has been added/edited on Google Sheet, the changes can be reflected using this property action. If a variable is set to Folder path or File name, this action will be invalid.
Refresh columns	Retrieve the column names from the first row values of the specified spreadsheet and set them to Columns.	If Sheet name is specified/edited, or a column has been added/edited on Google Sheet, the changes can be reflected using this property action. If a variable is set to Folder Path, File Name, or Sheet Name, this action will be invalid. When Specify Start Cell Position To Write is set, the row that meets the condition specified in Specify Start Cell Position To Write becomes the target row.

Setting Of Writing

Item name	Required/Optional	Use of variables	Description	Remarks
Insert To The First Row	Optional	Not available	Select whether to insert column names into the first row of the specified spreadsheet. Selected Insert column names into the first row. Not selected (Default) Don't insert column names.
Overwrite	Optional	Not available	Select whether to overwrite if the specified spreadsheet already exists. Selected (Default) Overwrite. If the specified spreadsheet doesn't exist, a new spreadsheet with the name specified in File Name is created and data is written to that file. Not selected Don't overwrite. If the specified spreadsheet exists, the SpreadsheetAlreadyExistedException is thrown and the write 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. Selected Delete. It will be ignored when the specified sheet doesn't exist. Not selected (Default) Don't delete.	Enabled when Overwrite is selected. Note If a sheet is deleted and then the data is written to it, the sheet will be positioned at the far right. 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. Selected Specify the start position for the write operation. Not selected (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 write operation begin with. Specify Cell Address (Default) Enter Cell Address To Start to start the writing process from the specified cell address. Specify Start Cell Value Enter Start Cell Value to start the writing process from the cell that matches with the specified value.	Enabled if Specify Start Cell Position To Write is selected. Depending on selected method, the corresponding input field is displayed (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 in 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 the condition to start writing.	Enabled, if Specify Cell Value is selected in Specified Condition To Start Writing.
Specify Batch Size To Write	Optional	Not available	Select whether to specify the number of rows to be written in a batch. Selected Specify the number of rows to be written in a batch. Not selected (Default) Don't specify the number of rows in a batch.	If selected, the writing of data is divided and 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 data that has been already written will remain on the spreadsheet. If not selected, data writing is performed all at once. Thus data won't remain even if the process is failed. The process will take less time but 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 connector.

Schemas

Input schema

The number of columns varies depending on the Columns settings.

= Remarks =

For schema structure, refer to XML Framework.

Output schema

None.

Loading schema in Mapper

The schema is loaded automatically.

= Remarks =

For details, refer to Edit Schema.

Transaction

Transaction isn't supported.

Parallel Stream Processing

PSP is supported.

= Remarks =

For details on PSP, refer to Parallel Stream Processing.

Available component variables

Component variable name	Description	Remarks
count	The number of rows written is returned.	The default value is null.
folderPath	The folder path to the spreadsheet to which the data is written is returned.	The default value is null.
fileName	The file name of the spreadsheet to which the data is written is returned.	The default value is null.
message_category	When an error occurs, the category of the message code corresponding to the error is stored.	The default value is null.
message_code	When an error occurs, the code of the message code corresponding to the error is stored.	The default value is null.
message_level	When an error occurs, the severity of the message code corresponding to the error is stored.	The default value is null.
error_type	When an error occurs, the error type is stored.	The default value is null. The format of the error type is as follows. Example: java.io.FileNotFoundException
error_message	When an error occurs, the error message is stored.	The default value is null.
error_trace	When an error occurs, the trace information for the error is stored.	The default value is null.

Message codes, exception messages, and limitations

Connector	Message code	Exception message	Limitations
Messages and limitations of the Google Sheets connector