How to update a cell based on some modification in another cell with drag update.

[Jatin]

Hi team,

Please check this stackblitz example:

https://stackblitz.com/edit/react-k6cra4

 

Here, if I'm updating the AO cell (i.e Verified By CC)  to either Yes or No.
If the value I put is Yes then I need to put today's date in the AN cell  (i.e Verified By CC Date)  for the same row.

This is working fine if I update each cell individually, i.e AO2, AO3, AO4 one by one. But if I do the drag value, this doesn't work.
And the reason not working is that I'm referring to the sheet.activeCell() .

I wanted to know if there is another way to achieve this (i.e AN cell should be updated if I do drag update on AO cell for all the columns)

Please let me know if you didn't understand or have some confusion about the problem statement.

Thanks! :)

[Veselin Tsvetanov]

Hi Jatin,

In order to properly get info about the updated cells in the Spreadsheet even when using the drag-to-fill, you should handlethe change event of the widget:

change: function(e) {
  let topLeft = e.range.topLeft();
  let bottonRightRow = e.range._ref.bottomRight.row;
  let topLeftRow = topLeft.row;
  let sheet = e.sender.activeSheet();
  
  if (topLeft.col === 0) {
    sheet.batch(() => {
      let i;
      
      for (i = topLeftRow; i <= bottonRightRow; i++) {
      	sheet.range("B" + (i + 1)).value("checked");
      }         
    });
  }
}

Here is a Dojo sample implementing the above suggestion:

https://dojo.telerik.com/AyAkoyEz/5

[Jatin]

Hi Veselin,

Thanks for the above example, but the real problem is I'm using Spreadsheet widget in React. The solution you have provided is for jQuery application. The onChange event parameters and functions are different for React. I couldn't find e.range.topLeft(); and others.

Would you mind updating the stackblitz example that I have given (https://stackblitz.com/edit/react-k6cra4)?

Or

Is there any way to find out what's the index of the current updated cell, i.e if I do drag-to-fill for 4 rows, the onChange will be called four times. Is it possible to get the index of that row inside the onChange event?

Thanks!

[Veselin Tsvetanov]

Hi Jatin,

Here is how the Spreadsheet change event should be defined in React:

      change={(e) => {
        let topLeft = e.range.topLeft();
        let bottonRightRow = e.range._ref.bottomRight.row;
        let topLeftRow = topLeft.row;
        let sheet = e.sender.activeSheet();

        if (topLeft.col === 40) {
          sheet.batch(() => {
            let i;

            for (i = topLeftRow; i <= bottonRightRow; i++) {
              if (sheet.range('AO' + (i + 1)).value() === 'Yes') {
                sheet
                  .range('AN' + (i + 1))
                  .value(new Date().toISOString().slice(0, 10));
              } else {
                sheet.range('AN' + (i + 1)).value(null);
              }
            }
          });
        }
      }}

Here is a modified version of the StackBlitz sample:

https://stackblitz.com/edit/react-jnjwjd

[Jatin]

Hi Veselin,

 

Thanks for the solutions, it is working but I'm facing another challenge in this.
The update is happening correctly, but when I try to access the new/updated data using

MySpreadSheet.current.widgetInstance.activeSheet().dataSource.data()

It doesn't show the updated data.

To be more clear, on the same stackbltiz example, I have added a console.log() on line 1065. The console.log outputs the current data of the sheet of each column. If you will notice, it shows verified_by_cc field as null which should show the updated date.

In short, whatever update is made in the change event of the spreadsheet is not captured in the MySpreadSheet.current.widgetInstance.activeSheet().dataSource.data().

Is it possible to get the updated data here? Thanks!

[Veselin Tsvetanov]

Hi Jatin,

Could you, please, modify the StackBlitz sample, so that it demonstrates the issue in question and send it back to me? Currently, line 1065 is in the Spreadsheet change event handler before the manual change of the values in column AN. Therefore, it is not expected that DataSource data there contains the changed value.

[Jatin]

Hi Veselin,

For some reason when I'm saving the stackBlitz example it's not actually saving it to the server. Can you add this below code to the line after 1065? Please refer to the attached screenshot for reference.

console.log(
     MySpreadSheet.current.widgetInstance.activeSheet().dataSource.data()
);

So in short, I'm only consoling the output of current data in the spreadsheet. If I update the AO field(Verified by cc) to Yes, it updates the AN field(Verified By CC Date) to the current date. Now when I'm again updating any other field of that row, it shows AN field is undefined (i.e in the next console.log cycle).

 

Thanks!

[Veselin Tsvetanov]

Thank you for the additional explanation.

Here is a modified StackBlitz sample in which the altered value in the Spreadsheet is persisted in the DataSource:

https://stackblitz.com/edit/react-82sktm

In order to achieve that I have altered the logic, so that is directly works with the data items:

        if (topLeft.col === 40) {
          setTimeout(() => {
            sheet.batch(() => {
              let i;

              for (i = topLeftRow; i <= bottonRightRow; i++) {
                if (sheet.range('AO' + (i + 1)).value() === 'Yes') {
                  sheet.dataSource
                    .data()
                    [i - 1].set('verified_by_cc', new Date());
                } else {
                  sheet.dataSource.data()[i - 1].set('verified_by_cc', null);
                }
              }
            });
          });
        }