Edit Spreadsheet with JSON data

Purpose

Users can create a spreadsheet by uploading a JSON file from their local drive and edit it online in Zoho Sheet.

HTTP Request URL

https://{api.office-integrator_domain}/sheet/officeapi/v1/spreadsheet

Request Parameters

Parameter	Value	Description
Mandatory Parameter
apikey	423s*****	Uniquely identifies the web application in which the sheet editor is integrated.
document	File	Upload the input JSON file that needs to edited from your local drive or desktop. Please refer this link for sample JSON data.
callback_settings	{ "save_format":"zsheet", "save_url": "https://zylker.com/save.php", "savetype":"changes_only", "save_url_params": { .... } }	Using this param, you will be able to specify the following; save_format -> Specify the output format in which the edited spreadsheet needs to be saved. save_url -> Provide your server location to which the latest content needs to be pushed back when the 'Save' is performed. savetype -> If this key is passed, only the newly added and modified records in the spreadsheet will be pushed back to your server when the the 'Save' is hit. The below key value is optional; save_url_params -> To customize the output parameters in which the spreadsheet details will be pushed from our end.
Optional Parameters - Editor Customization
editor_settings	{ "language":"en" "country":"IN" }	Set Editor preferences like interface language and country for new spreadsheets.
permissions	{ "document.export":true, "document.print":true, "document.edit":true }	Define spreadsheet permissions for user. Option to allow or revoke spreadsheet "Export", "Print" and "Edit" access.
document_info	{ "document_name":"Sample", "document_id":"1349" }	Set a unique id and specify display name of the new spreadsheet.
user_info	{ "display_name":"Ken" }	Identify a user in the spreadsheet with "display_name".
ui_options	{ "save_button":"hide" }	Specify whether the "Save button" option needs to be shown or hidden.

Note:

Editor customization parameters and their values are case-sensitive.

Editor Customization Parameters

editor_settings

Parameter	Type	Possible Values	Default Value
language	string	en / de / es / fr / ja / nl more...	en
country	string	IN / US / GB / MX / BR ...	IN

permissions

Parameter	Type	Possible Values	Default Value
document.export	boolean	true / false	true
document.print	boolean	true / false	true
document.edit	boolean	true / false	false

callback_settings

Parameter	Type	Possible Values	Default Value
save_format	string	zsheet/ xlsx / xls / ods / csv / tsv	xlsx
save_url	string	https://zylker.com/save.php	URL
savetype	string	changes_only
Optional Keys
save_url_params	JSON	Maximum key count - 10 Allowed characters for <additional_user_key> - Alphabets, Numbers, Hyphen, Underscore and Dot. Maximum Length - 100 characters Maximum length of <value> - 1000 characters

document_info

Parameter	Type	Possible Values	Default Value
document_id	string	24eru24960v
document_name	string	Sample Spreadsheet	Untitled

user_info

Parameter	Type	Possible Values	Default Value
display_name	string	Ken/James/Adam	Guest

ui_options

Parameter	Type	Possible Values	Default Value
save_button	string	show/hide	show

Edit Spreadsheet - Error Codes

Code	Description
2831	Error occurred. Incorrect parameter value.
2841	Unable to import from URL.
2851	No such spreadsheet.

For a full list of error handling cases in Edit Spreadsheet, refer here.

save_url usage:

Zoho Office Integrator allows partners and businesses to save the spreadsheet content back to their specified storage server through "save_url" method.

A save_url is a publicly accessible Webhook or a Web URL to which Zoho will push the updated spreadsheet content from sheet editor to the partner's server.

In order to use the save_url method, partners should fulfill the below requirements:

Partner's remote servers need to expose one of their ports - port 443 (HTTPS) or port 80 (HTTP) - from their location, for Zoho sheet to push the data back.
The save_url value specified in the HTTPS/HTTP POST request should be a proper domain name and publicly accessible over the web. Example: https://zylker.com/save.php

save_url_params usage:

By default, when the 'Save' is performed, we will be pushing back the information with the following key names:

Key Name	Type	Description
content	File	File content will be pushed back in the form of bytes.
filename	String	Spreadsheet name
format	String	Specifies the format in which the spreadsheet has been exported.

If you wish to customize the above key names that you need to send back during save call, you can make use of the 'save_url_params' key inside 'callback_settings' parameter.

Once the 'save_url_params' key is configured, only the key values defined inside will be pushed back to your server. The default key names (like filename and format) will not be included. To include them, you need to define your own keys for format and filename by using the system variable $format and $filename respectively.

If the 'save_url_params' is passed as an empty JSON or the system variable $content is not configured, the file bytes will be pushed back with the multipart name 'content' by default.

Syntax:

"save_url_params": {
      <custom_content_key>: $content,
      <custom_format_key>: $format,
      <custom_filename_key>: $filename,
      <additional_user_key>: <value>,
      <additional_user_key>: <value>,
      ....
      <additional_user_key>: <value>
      },

Example:

"save_url_params": {
      file: $content,
      extension: $format,
      spreadsheet_name: $filename,
      <additional_user_key>: <value>,
      <additional_user_key>: <value>
      },

In the above example, the file bytes will be pushed back in the multipart name 'file', file format in the key name 'extension' and spreadsheet filename in the key name 'spreadsheet_name'.

savetype usage:

If you wanted only the newly added and modified records in the spreadsheet to be pushed back to your server when the the 'Save' is hit, then you need to pass the 'savetype' key value inside the 'callback_settings' parameter.

Sample JSON Array that will be returned:

{
"added_data":[
        ["f2902712-29dc-43bc-b921-0543e2345d59","fd","","","FALSE"],
        ["55d90783-9df6-4c89-a4fa-de8a399565b0","fd","","","FALSE"],
        ["66eb4b92-1af4-4140-ad08-8898cf193337","fd","","","FALSE"],
        ["a7811e96-bd70-4ffc-a7b0-86cdd4fb79b3","fd","","","FALSE"],
        ["e61df8f7-8db5-44c1-a350-5221f0e67b3a","fd","","","FALSE"]],
"headers":["id","item name","ExpDate","Price","CheckBox"],
"modified_data":[["1235","Soap","8/2/2021","50","FALSE"]
]
}

Key Name	Description
added_data	The newly added records in the spreadsheet will be listed.
headers	List of headers available in the spreadsheet will be listed. Note: You will not be able to add new headers while editing. Only the headers that are present in the spreadsheet while importing will be listed.
modified_data	The modified existing records will be listed.

Sample Request

Copiedcurl -X POST \
  https://api.office-integrator.com/sheet/officeapi/v1/spreadsheet \
  -H 'content-type: multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW' \
  -F apikey=423s***** \
  -F 'editor_settings={'\''language'\'':'\''en'\'','\''country'\'':'\''IN'\''}' \
  -F 'permissions={'\''document.export'\'':true,'\''document.print'\'':true,'\''document.edit'\'':true}' \
  -F 'callback_settings={'\''save_format'\'':'\''zsheet'\'','\''save_url'\'':'\''https://zylker.com/save.php/'\''}' \
  -F 'document_info={'\''document_name'\'':'\''Sample'\'', '\''document_id'\'':1349}' \
  -F 'user_info={'\''display_name'\'':'\''Ken'\''}' \
  -F 'ui_options={'\''save_button'\'':'\''show'\''}'

Sample Response

Copied{
    "gridview_url": "https://api.office-integrator.com/sheet/officeapi/v1/57a295e6ccb2be712117c8bffec4fb166838b7abae482d80e1e3be25edcfc5a71d371cbd6dca44febfba35e74261899c585f4e2f6e7487cdb9c65a4c1bc18a34?zview=rgrid",
    "save_url": "https://api.office-integrator.com/sheet/officeapi/v1/spreadsheet/57a295e6ccb2be712117c8bffec4fb166838b7abae482d80e1e3be25edcfc5a71d371cbd6dca44febfba35e74261899c585f4e2f6e7487cdb9c65a4c1bc18a34/save",
    "session_delete_url": "https://api.office-integrator.com/sheet/officeapi/v1/session/273ca6c7afcf01b28e852233a7ae61f85fd7b3458ac1f29bc1f10a11e17e9c09a451cb5b41c6ee6993a42296f369a0d4",
    "session_id": “273ca6c7afcf01b28e852233a7ae61f85fd7b3458ac1f29bc1f10a11e17e9c09a451cb5b41c6ee6993a42296f369a0d4",
    "document_delete_url": "https://api.office-integrator.com/sheet/officeapi/v1/spreadsheet/1349",
    "document_id": "1349",
    "document_url": "https://sheet.zoho.com/sheet/officeapi/v1/57a295e6ccb2be712117c8bffec4fb166838b7abae482d80e1e3be25edcfc5a71d371cbd6dca44febfba35e74261899c585f4e2f6e7487cdb9c65a4c1bc18a34"
}