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

ParameterValueDescription
Mandatory Parameter
apikey423s*****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

ParameterTypePossible ValuesDefault Value
languagestringen / de / es / fr / ja / nl more... en
countrystringIN / US / GB / MX / BR ...IN

permissions

ParameterTypePossible ValuesDefault Value
document.export   booleantrue / falsetrue
document.printbooleantrue / falsetrue
document.editbooleantrue / falsefalse

callback_settings

ParameterTypePossible ValuesDefault Value
save_formatstringzsheet/ xlsx / xls / ods / csv / tsvxlsx
save_urlstringhttps://zylker.com/save.phpURL
savetypestringchanges_only 
Optional Keys
save_url_paramsJSON
  • 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

ParameterTypePossible ValuesDefault Value
document_idstring24eru24960v 
document_namestringSample SpreadsheetUntitled

user_info

ParameterTypePossible ValuesDefault Value
display_namestringKen/James/AdamGuest

ui_options

ParameterTypePossible ValuesDefault Value
save_buttonstringshow/hideshow

Edit Spreadsheet - Error Codes

CodeDescription
2831Error occurred. Incorrect parameter value.
2841Unable to import from URL.
2851No 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:

  1. 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.
  2. 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 NameTypeDescription
contentFileFile content will be pushed back in the form of bytes.
filenameStringSpreadsheet name
formatStringSpecifies 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 NameDescription
added_dataThe 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_dataThe modified existing records will be listed.

Sample Request:


				curl -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:


				{
    "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"
}