Remote calculations and silent sheet update.

[Marek Baranowski]

Hello,

I'm using React's Spreadsheet component as an interface for editing Google Sheets in my app. Let me explain my use case:

1. I periodically poll Google Sheet API for data in case there are any changes

2. I convert received JSON to format that is compatible with Syncfusion spreadsheet:

{
  name: "xsheet",
  rows: [
    {
      index: 0,
      cells: [{ index: 0, value: 123 }]
    },
    {
      index: 1,
      cells: [{ index: 0, formula: "=A1 + 2", value: 125 }]
    },
    {
      index: 4,
      cells: [{ index: 3, formula: "=A1 + A2", value: 248 }]
    }
  ]
};

As you can see, I set the "value" prop right away so that Syncfusion spreadsheet doesn't calculate it itself. I need to rely solely on the data and calculations results provided by GSheets API.

3. When the cell is being edited, I need to prevent saving the new value,  but instead send that value through GSheets API and receive updated sheet values.

4. The last part which I have problem with, is setting those values on a sheet. But my issue is that when I set it by updating "sheets" props, the sheet is being rerendered and for example it gets scrolled up.

I want to preserve scroll position, selection etc., just silently update displayed values for the recalculated ones received from API.

How to do that? I wasn't able to find a proper way to update range or the whole sheet in documentation.

Best Regards!

[Marek Baranowski]

I've prepared a simple reproduction of the issue.  It's not using Google Sheets API due to the hassle related to authorization, so I created a simple service that acts in a similar way.

As you can see, when I set the new values of the sheet after getting it from API, the sheet gets reset - being scrolled to the top and having selection changed. How to fix that?

https://pasteboard.co/JDDNk6X.gif

Here is the code in the codesandbox: https://codesandbox.io/s/fqxd4?file=/src/index.js

[Sangeetha Priya Murugan]

Hi Marek Baranowski, 

 

Thank you for contacting Syncfusion support. 

 

We have checked your reported issue based on your provided sample and we would like to let you know that, you have constructed the sheet model with entered value for A1 cell in update method. To update the entered cell value in spreadsheet, we would suggest you to use updateCell method as like as below. 

 

 updateValue() { 

    let cell = this.spreadsheet.getActiveSheet().activeCell; // to get active cell 

    this.spreadsheet.updateCell( 

      { value: document.getElementById("ssValue").value }, 

      cell 

    ); 

  } 

 

 

For your convenience, we have prepared the sample based on your requirement. Please find the link below. 

 

Sample Link: https://stackblitz.com/edit/react-l9ad6s?file=index.js 

 

Could you please check the above sample and get back to us, if you need any further assistance 

 

Regards, 

Sangeetha M 

[Marek Baranowski]

Hi Sangeetha,

Thank you for response, but it's not really relevant to my issue, maybe I wasn't clear.

My scenario:

- user edits cell, hits enter

- my code catches this change and prevent it to happen (cancels it?), instead I want to send this new value to Google Sheets API so that the source Google spreadsheet gets updated and recalculated

- in response to my previous request, Sheets API sends back a 2D array of values that have updated

- now I want to set this new values to the Syncfusion spreadsheet so that it's visible to user. At the same time I want to avoid losing scroll position or cell selection, user should feel like it was an ordinary spreadsheet operation - except there will be small delay after he hits enter; at this point I'm ok with the delay.

In other words, I kinda want to swap Synfusion calculation engine with Google Sheets API.

To be honest the simplest solution to my issue would be "updateCells" function instead of "updateCell". I need to update many cells at once. Probably I could use "updateCell" in some loops but that doesn't feel like a good solution.

Here is an example of such function: https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluesvalues

[Sangeetha Priya Murugan]

Hi Marek Baranowski,  

 

Thank you for your update. 

 

We have checked your reported requirement and it can be achievable in our spreadsheet by using the ranges property as like as below. 

 

 updateValue() { 

    let sheet = this.spreadsheet.getActiveSheet(); // to get active sheet 

    let cell = sheet.activeCell; // to get active cell 

    let data = [ 

      { Key1: document.getElementById("ssValue").value, Key2: "Your value" }, 

      { Key1: document.getElementById("ssValue").value, Key2: "Your value" } 

    ]; //  construct the datasource 

    sheet.ranges[0] = { 

      dataSource: data, 

      startCell: cell 

    }; // to update the range in spreadsheet 

    this.spreadsheet.refresh(); // to refresh the spreadsheet with the modified changes 

  } 

 

 

For your convenience, we have prepared the sample that load the values for multiple cells, by constructing the data source in a button click event. Please find the link below. 

 

Sample Link: https://stackblitz.com/edit/react-l9ad6s-ahbued?file=index.js 

 

Could you please check the above details and get back to us, if you need any further assistance 

 

Regards, 

Sangeetha M