Cell Event Listener via Office scripts in MS Excel WebApp

Question

In the Excel WebApp (Office 365) it is possible to place Office Scripts via the "Automate" tab, which is using the JavaScript-syntax and which could automate excel like a VBA-macro, but for the excel WebApp (Screenshot).

How is it possible to create an event-listener for a specific cell like cell: A1.
(A scenario would be: If the content/value of cell: A1 is changed then write something in cell: B1)

Stringency

In VBA you add your code in the worksheet.change event, then check if the range changed is the range you want and do the corresponding code you need. Check if the web version have a default event for you to alter and add code. In case of a table you may want to know if the range changed intersect with the desired column of the table. – Hegel

Answer 1

The Office Scripts doesn't support events yet. You can provide feature feedback at this link:

https://excel.uservoice.com/forums/274580-excel-for-the-web?category_id=143439

Mullane

Answer 2

I needed something similar to this and built a workaround using a PowerAutomate Flow and an Office Script. (Be aware this approach is not efficient)

For my needs I wanted to update a 'last updated time' whenever particular cell values changed on a given sheet within the workbook.

To do this I wrote a an office script that was able to create a checksum/hash over the cell values and stored this in another cell on the sheet. When called it would compare the 'current hash' against the 'stored hash' and if it had changed, both persist the current hash as the new stored hash and set the current date & time in a cell (the end-goal I had achieved).

function main(workbook: ExcelScript.Workbook) {
  let date = new Date();
  // c/o https://mcmap.net/q/1916156/-convert-javascript-date-object-to-excel-serial-date-number#:~:text=finally%20I%20was%20able%20to,*%2060%20*%2060%20*%2024))%3B
  
  // Interesting issue here, where when it executes in flows we end up with a time different to when we execute directly in the script. We don't need hourly accuracy, so we'll live.
  let now = 25569.0 + ((date.getTime() - (date.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));

  workbook.getWorksheet("Dashboard").getCell(36, 5).setValue(now);
    workbook.getWorksheets().forEach((workSheet)=>{
      if( workSheet.getName().indexOf('Logs')!= -1 && workSheet.getName() != "IgnoreThisSheet") {
        const selectedCell = workSheet.getCell(0, 20);
        const calculatedHash = calculateHash(workSheet.getRange("J12:J18"));
        const currentHash = +selectedCell.getValue();
        if( currentHash != calculatedHash ) {
          selectedCell.setValue(calculatedHash);
          let lastUpdateCell= workSheet.getCell(1, 20);
          lastUpdateCell.setValue(now)        
        }
      }
    });
}
const primes = [2, 3, 5, 7, 11, 13, 17, 19, 23, 29];

function calculateHash(range: ExcelScript.Range) : Number  {

  let hash=0;
  let position =0;  
  range.getColumn(0).getValues().forEach((rangeValueRow) => {
    let nextValue= +rangeValueRow[0];
    if(isNaN(nextValue)) {
      nextValue = 0;
    }
    if (position < primes.length) {
      hash += nextValue * primes[position];
    } else {
      // If the list is longer than the prime array, use a default prime
      // This runs the risk if someone goes wild and has 
      // a lot of KRs then their checksum won't neccessarily take effect (in a subtle and painful way that will probably leave you fuming when you read this note!)
      hash += nextValue * 31; 
    }    
    position++;
  });
  return hash;
}

Effectively this approach allows me to monitor changes to arbritary cells in the O365 Excel spreadsheet (Something that was trivial previously with VBA in offline Excel.)

I then configured a power automate flow to 'Schedule an Office Script to run in Excel' and had this running every 5 minutes calling the Script.

This approach appears to work absolutely fine in both desktop copies of the spreadsheet and online copies.

Veal