Inserting external data

The steps below explain the process of inserting data into the spreadsheet by an external link in ONLYOFFICE Document Server.

1. The user copies the cell value to the clipboard from the document editor of the source spreadsheet. At the same time, the special data is also copied.
2. The user inserts the copied data into the document editor of the destination spreadsheet.
3. The document editor requests a link to the source file by sending the data to the document manager.
4. The document manager sends the source spreadsheet link to the document editor.
5. The document editor sends a request to the document editing service for spreadsheet downloading.
6. The document editing service downloads the source spreadsheet from the document storage service.
7. The document editing service sends all the necessary data to display in the document editor of the destination spreadsheet.

How this can be done in practice

1. Create a source spreadsheet from where the data will be copied.

2. Specify the document.referenceData parameter in the initialization config of the source spreadsheet:

   var docEditor = new DocsAPI.DocEditor("placeholder", {
       "document": {
           "referenceData": {
               "fileKey": "BCFA2CED",
               "instanceId": "https://example.com"
           },
           ...
       },
       ...
   });

3. When the user copies the data from the source spreadsheet, the clipboard receives a list of the following values:

   • the sheet name and the range from where the data was copied which will be used later to refresh the copied data;
   • the document.referenceData object which will be used to check the availability of insering data into the destination spreadsheet by the external link;
   • the file name which will be used to display a formula in the editor.
4. Create a destination spreadsheet where the external data will be inserted.

5. Specify the onRequestReferenceData event handler in the initialization config of the destination spreadsheet for the Paste link and Update values buttons to be displayed:

   var docEditor = new DocsAPI.DocEditor("placeholder", {
       "events": {
           "onRequestReferenceData": onRequestReferenceData,
           ...
       },
       ...
   });

6. If the clipboard has the source spreadsheet data specified in step 3, and the destination spreadsheet has the onRequestReferenceData event handler in the initialization config, then the Paste link button is displayed in the dialog box.

   

7. When the user clicks the Paste link button, the formula is inserted into the current cell, and the referenceData object is saved to the destination file. The inserted formula is displayed as follows:

   ='[fileName]sheetName'!cell

   Parameter	Description	Type	Example
   cell	The cell from where the data was copied.	string	E5
   fileName	The file name from where the data was copied.	string	new.xlsx
   sheetName	The sheet name from where the data was copied.	string	Sheet1

   The data update request to the file will be sent to the file URL.

   Please note that you can enter a formula of the specified format in the cell, and the data from the extrenal file will be inserted as well. But in this case, the onRequestReferenceData event will be executed only with the path parameter.

8. When the user is trying to refresh data from the source file by clicking the Update values button in the External links dialog box of the Data tab, the onRequestReferenceData event is called. An object with the unique file data received from the source file and the file path or name are sent in the data parameter.

   To send the data to the setReferenceData method, it is recommended to search for the file by the referenceData parameter first. If there is no such a field or a file cannot be found, then the path parameter is used.

   var onRequestReferenceData = function (event) {
       var referenceData = event.data.referenceData;
       var path = event.data.path;
       ...
   };

   

9. In order to refresh the data from the source file, the setReferenceData method must be called:

   Please note that this method is executed only when the user has permissions to the source file.

   docEditor.setReferenceData({
       "fileType": "xlsx",
       "path": "sample.xlsx",
       "referenceData": {
           "fileKey": "BCFA2CED",
           "instanceId": "https://example.com"
       },
       "url": "https://example.com/url-to-example-document.xlsx"
   });

   Where the example.com is the name of the server where document manager and document storage service are installed. See the How it works section to find out more on Document Server service client-server interactions.