How to handle event on data change in excel sheet to refresh all pivot tables?

Question

I have a VBA code applied to Worksheet and its change. So whenever there is new entry or deletion (any change) in the sheet, it refresh all the pivot tables attached to it -

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub

( I am not very familiar with the VBA or office script code, so sorry for basic question.)

But this does not work on excel online. Hence i need a code to use in excel online code editor (or typescript). So Far, I am able to write this code -

async function main(workbook: ExcelScript.Workbook) {
  await Excel.run(async (context) => {
    console.log("Adding Sheet OnChange Handler");
    let mysheet = context.workbook.worksheets.getItem("Attendance");
    mysheet.onChanged.add(ref);
    await context.sync();
    console.log("Added a worksheet-level data-changed event handler.");
  }
  )};
function ref(workbook: ExcelScript.Workbook) {
  let selectedsheet = workbook.getActiveWorksheet();
  selectedsheet.refreshAllPivotTables();
  console.log("Pivot Refreshed.");
};

I am getting an error Cannot find name 'Excel' and it should work whenever there is any change in the worksheet which is not the case. Please help me with this. Thanks.

Answer 1

I think you are missing the () at the end of the refreshAllPivotTables method.

Please try this -

function main(workbook: ExcelScript.Workbook) {
  workbook.refreshAllPivotTables(); // Refresh all pivot tables
}

Answer 2

As replied by @Daniel G. Wilson , it is not possible in script right now. So I used the Power Automate approach.

I wrote the script in my code editor and saved it-

function main(workbook: ExcelScript.Workbook) {
let selectedsheet = workbook.getWorksheet("Pivot");
selectedsheet.refreshAllPivotTables();
console.log("Pivot Refreshed");
};

Then I created a Scheduled flow in Power Automate. Steps are -

1. Create new Scheduled flow.
2. Give some name.
3. Set the starting time and the interval to run it automatically.
4. Select new step.
5. Select Excel Online Business.
6. Select Run a Script.
7. Choose file location and Script name.
8. Save and test the flow.

It works fine. Thanks.