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

0 Answers 18 Views
Spreadsheet
Jatin
Top achievements
Rank 1
Iron
Jatin asked on 10 Feb 2022, 01:31 PM

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
Telerik team
commented on 15 Feb 2022, 11:07 AM

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
Top achievements
Rank 1
Iron
commented on 15 Feb 2022, 12:46 PM

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
Telerik team
commented on 18 Feb 2022, 07:11 AM

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
Top achievements
Rank 1
Iron
commented on 08 Mar 2022, 06:18 PM

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
Telerik team
commented on 11 Mar 2022, 07:02 AM

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
Top achievements
Rank 1
Iron
commented on 21 Mar 2022, 12:29 PM

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
Telerik team
commented on 24 Mar 2022, 11:01 AM

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);
                }
              }
            });
          });
        }

No answers yet. Maybe you can help?

Tags
Spreadsheet
Asked by
Jatin
Top achievements
Rank 1
Iron
Share this question
or