Overview
Configuration is provided for establishing connections with the Zoho Sheet service. The configuration is used in the REST Connection Manager.
Setup
Press 
icon to get more information about the connection parameters.
Obtain data
Use REST Source component to get data from service resource.
Insert data
Use REST Destination component to insert data into service resource.
Quick Start
In this task, we will show you step-by-step how to create a connection to Zoho Sheet REST API using COZYROC's REST Connection Manager.
Step 1. Run "Visual Studio" under "Administrator account". Right click on Connection managers Tab and Select New Connection. In Add SSIS Connection Manager scroll down and select Connection managers for REST Connection and click Add.
Step 2. From drop-down menu Configuration select Zoho Sheet. Then click New in the Token File parameter to create a new token file.
Step 3: In the next wizard click on Next button.
Step 4: Provide Email and then click on 'Next' button.
Step 5: Provide Password and then click on 'Sign In' button.
Step 6: In the next wizard click on TRUST button.
Step 7: Save generated access token to file. Specify token protection password. Click OK.
Step 8. Click "Test Connection" to test your connection.
Congratulations! You have now established a connection to your Zoho Sheet instance.
In this guide, we will show how to read data from the Zoho Sheet service resource using the COZYROC REST Source component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open "SSIS Toolbox" and drag and drop COZYROC "REST Source" component.
Step 3. Double click on the "REST Source" component and select Zoho Sheet connection from the combo. Select from available Zoho Sheet resources listed in the second combo below.
Specify the required parameter in the grid below or Optionally you can include extra parameters in the grid below if needed.
Step 4. Click on the "Columns tab" and select Zoho Sheet resource columns you want to read.
Step 5. Click Preview to make sure all necessary resource columns are included.
Bravo! You have now learned how to read data from Zoho Sheet service.
In this guide, we will show how to write data to the Zoho Sheet service resource using the COZYROC REST Destination component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open the "SSIS Toolbox" and drag and drop COZYROC "REST Destination" component.
Step 3. Double click on the "REST Destination" component and select Zoho Sheet connection from the combo. Also, select resource action and Zoho Sheet resource you want to work on. Specify the parameter in the grid below if it is required.
Step 4. Click on the "Mapping" tab and map the input columns for the process you are configuring.
If you want to provide input for other resource composite objects, change the input above and map the columns.
Done. You have now learned how to write data into the Zoho Sheet resource.
Configuration
Base URL address: https://sheet.zoho.com/api/v2.
- OAuth 2
-
The authentication uses an authorized token. The token will be refreshed with the following expression:
{{=Date.now() + (response.expires_in - 300)}}.The authentication has the following user-defined parameters:
- Scope:
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
Documentation: https://sheet.zoho.com/help/api/v2/#serverapp.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/templates.
The result is extracted from:{{=response.templates}}.The following request parameters will be automatically processed:
-
method:
template.list
Documentation: https://sheet.zoho.com/help/api/v2/#WORKBOOK-List-all-templates.
-
method:
- resource_id
Template: ShortText.
- created_by
Template: ShortText.
- created_time
Template: DateTime. A read-only field.
- last_modified_time
Template: DateTime. A read-only field.
- template_name
Template: ShortText.
- template_url
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/workbooks.
The result is extracted from:{{=response.workbooks}}.The following request parameters will be automatically processed:
-
method:
workbook.list
Documentation: https://www.zoho.com/sheet/help/api/v2/#WORKBOOK-List-all-workbooks.
-
method:
- [Create] action
-
Endpoint URL address:
/create.The following request parameters will be automatically processed:
-
method:
workbook.create
Documentation: https://www.zoho.com/sheet/help/api/v2/#WORKBOOK-Create-workbook.
-
method:
- resource_id
A key field. Template: ShortText.
- workbook_name
Template: ShortText.
- workbook_url
Template: ShortText.
- permission
Data type: DT_I4
- created_by
Template: ShortText.
- created_time
Template: DateTime. A read-only field.
- last_modified_time
Template: DateTime. A read-only field.
- worksheets
-
The value is gathered from
/{{=item.resource_id}}address. The gathered value is processed with{{=response.worksheet_names}}expression. Field components:- An array.
- Contains the following components: worksheet_name, worksheet_id.
Documentation: https://sheet.zoho.com/help/api/v2/#WORKSHEET-List-all-worksheets.
Based on resource template Base.
- [Update] action
-
Endpoint URL address:
/share.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
The following request parameters will be automatically processed:
-
method:
workbook.share -
resource_id:
{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}} -
application/x-www-form-urlencoded:
{{={ share_json : [{ user_email : item.user_email, access_level : item.access_level }] } }}
Documentation: https://sheet.zoho.com/help/api/v2/#WORKBOOK-Share-workbook.
- resource_id
Template: ShortText.
- user_email
Template: ShortText.
- access_level
Template: ShortText.
Based on resource template Base.
- [Update] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
The following request parameters will be automatically processed:
-
method:
ranges.format.set -
application/x-www-form-urlencoded:
{{={ format_json : [item] } }}
Documentation: "https://sheet.zoho.com/help/api/v2/#UPDATEWORKBOOK-Format-ranges".
- worksheet_id
Template: ShortText.
- range
Template: ShortText.
- fill_color
Template: ShortText.
- font_color
Template: ShortText.
- horizontal_alignment
Template: ShortText.
- vertical_alignment
Template: ShortText.
- font_name
Template: ShortText.
- font_size
Template: ShortText.
- row_height
Template: ShortText.
- column_width
Template: ShortText.
- bold
Template: ShortText.
- italic
Template: ShortText.
- underline
Template: ShortText.
- strikethrough
Template: ShortText.
- wrap_text
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.
The result is extracted from:{{=response.namedranges}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
The following request parameters will be automatically processed:
-
method:
namedrange.list
Documentation: "https://sheet.zoho.com/help/api/v2/#UPDATEWORKBOOK-List-all-named-ranges".
- [Create] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
The following request parameters will be automatically processed:
-
method:
namedrange.create
Documentation: "https://sheet.zoho.com/help/api/v2/#UPDATEWORKBOOK-Create-named-range".
- [Update] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
The following request parameters will be automatically processed:
-
method:
namedrange.update
Documentation: "https://sheet.zoho.com/help/api/v2/#UPDATEWORKBOOK-Update-named-range".
- [Delete] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
The following request parameters will be automatically processed:
-
method:
namedrange.delete
Documentation: "https://sheet.zoho.com/help/api/v2/#UPDATEWORKBOOK-Delete-named-range".
- name_of_range
Template: ShortText.
- worksheet_name
Template: ShortText.
- worksheet_id
Template: ShortText.
- range
Template: ShortText.
- start_row
Data type: DT_I4
- start_column
Data type: DT_I4
- end_row
Data type: DT_I4
- end_column
Data type: DT_I4
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.
The result is extracted from:{{=response.worksheet_names}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
The following request parameters will be automatically processed:
-
method:
worksheet.list
Documentation: https://sheet.zoho.com/help/api/v2/#WORKSHEET-List-all-worksheets.
- [Create] action
-
Endpoint URL address:
/{{=item.resource_id}}.The following request parameters will be automatically processed:
-
method:
worksheet.insert
Documentation: https://sheet.zoho.com/help/api/v2/#WORKSHEET-Create-worksheet.
-
method:
- [Delete] action
-
Endpoint URL address:
/{{=item.resource_id}}.The following request parameters will be automatically processed:
-
method:
worksheet.delete
Documentation: https://sheet.zoho.com/help/api/v2/#WORKSHEET-Delete-worksheet.
-
method:
- resource_id
A key field. Template: ShortText.
- worksheet_name
Template: ShortText.
- worksheet_id
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.
The result is extracted from:{{=response.records}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
- worksheet_id: Required. Specify the worksheet identifier.
The following request parameters will be automatically processed:
-
method:
worksheet.records.fetch -
worksheet_id:
{{=ZohoSheet.UI.WorksheetEditor.getId(parameters.worksheet_id)}}
Documentation: https://sheet.zoho.com/help/api/v2/#TABULAR-Fetch-records-from-worksheet.
- [Create] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
- worksheet_id: Required. Specify the worksheet identifier.
The following request parameters will be automatically processed:
-
method:
worksheet.records.add -
worksheet_id:
{{=ZohoSheet.UI.WorksheetEditor.getId(parameters.worksheet_id)}} -
application/x-www-form-urlencoded:
{{={ json_data : [item] } }}
Documentation: https://sheet.zoho.com/help/api/v2/#TABULAR-Add-records-to-worksheet.
- [Update] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
- worksheet_id: Required. Specify the worksheet identifier.
- criteria: Specify criteria for update. Optional.
The following request parameters will be automatically processed:
-
method:
worksheet.records.update -
worksheet_id:
{{=ZohoSheet.UI.WorksheetEditor.getId(parameters.worksheet_id)}} -
application/x-www-form-urlencoded:
{{={ data : item } }}
Documentation: https://sheet.zoho.com/help/api/v2/#TABULAR-Update-records-in-worksheet.
- [Delete] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
- worksheet_id: Required. Specify the worksheet identifier.
The following request parameters will be automatically processed:
-
method:
worksheet.records.delete -
row_array:
{{=_.pluck(batch, 'row_index')}}
Documentation: https://sheet.zoho.com/help/api/v2/#TABULAR-Delete-records-from-worksheet.
- header_row
Data type: DT_I4
- row_index
Data type: DT_I4
- [External]
-
The external fields URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}. The external fields list is extracted from:{{=ZohoSheet.getSheetFields(response.records)}}.
Based on resource template Base.
- [Update] action
-
Endpoint URL address:
{{=item.resource_id}}.The following request parameters will be automatically processed:
-
method:
worksheet.copy
Documentation: https://sheet.zoho.com/help/api/v2/#WORKSHEET-Copy-worksheet.
-
method:
- resource_id
A key field. Template: ShortText.
- worksheet_id
Template: ShortText.
- worksheet_name
Template: ShortText.
- new_worksheet_name
Template: ShortText.
- insert_after
Template: ShortText.
Based on resource template Base.
- [Update] action
-
Endpoint URL address:
/{{=item.resource_id}}.The following request parameters will be automatically processed:
-
method:
worksheet.rename
Documentation: https://sheet.zoho.com/help/api/v2/#WORKSHEET-Rename-worksheet.
-
method:
- resource_id
A key field. Template: ShortText.
- old_name
Template: ShortText.
- new_name
Template: ShortText.
Based on resource template Base.
- [Create] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
- worksheet_id: Required. Specify the worksheet identifier.
The following request parameters will be automatically processed:
-
method:
records.columns.insert -
worksheet_id:
{{=ZohoSheet.UI.WorksheetEditor.getId(parameters.worksheet_id)}}
Documentation: https://sheet.zoho.com/help/api/v2/#TABULAR-Insert-columns-to-records.
- header_row
Data type: DT_I4
- insert_column_after
Template: ShortText.
- column_names
Template: LongText. A composite field.
Based on resource template Base.
- [Delete] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
- worksheet_id: Required. Specify the worksheet identifier.
The following request parameters will be automatically processed:
-
method:
column.delete -
worksheet_id:
{{=ZohoSheet.UI.WorksheetEditor.getId(parameters.worksheet_id)}}
Documentation: "https://sheet.zoho.com/help/api/v2/#UPDATEWORKBOOK-Delete-column".
- column
Data type: DT_I4
Based on resource template Base.
- [Update] action
-
Endpoint URL address:
/{{=ZohoSheet.UI.WorkbookEditor.getId(parameters.resource_id)}}.The action has the following user-defined parameters:
- resource_id: Required. Specify the workbook identifier.
- worksheet_id: Required. Specify the worksheet identifier.
The following request parameters will be automatically processed:
-
method:
cell.note.set
Documentation: "https://sheet.zoho.com/help/api/v2/#UPDATEWORKBOOK-Set-note-to-cell".
- row
Data type: DT_I4
- column
Data type: DT_I4
- note
Template: ShortText.
- [Read] action
-
The result is extracted from:{{=response.data}}.
The result is paginated.The following request parameters will be automatically processed:
-
start_index:
{{=parameters.iterator}} -
count:
{{=parameters.batchSize}} -
_includeUserParameters:
{{=parameters}}
-
start_index:
- [Create] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/x-www-form-urlencoded:
{{=item}}
-
_includeUserParameters:
- [Update] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/x-www-form-urlencoded:
{{=item}}
-
_includeUserParameters:
- [Delete] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/x-www-form-urlencoded:
{{=item}}
-
_includeUserParameters:
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- TemplateT
Field components:
-
Uses template:
DateTime. - Contains the following components: created_time, last_modified_time, template_name, template_url, resource_id, created_by.
-
Uses template:
Knowledge Base
What's New
- New: Introduced connection.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.