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
}