2

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.

Shaikh Naushad
  • 53
  • 1
  • 9

2 Answers2

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
}
Sudhi Ramamurthy
  • 2,358
  • 1
  • 10
  • 14
  • 1
    Hi, it is working only when i manually run the script. but i need to run this script whenever there is any change in data in a particular sheet. Any help on that? – Shaikh Naushad Sep 13 '20 at 08:44
  • @ShaikhNaushad unfortunately this isn't possible yet—though it's something we'll hopefully be working towards soon. Would running the script periodically be acceptable? If so, you may be able to accomplish this by running the script with Power Automate, say every 10 minutes. – Daniel G. Wilson Sep 14 '20 at 18:17
  • 1
    @DanielG.Wilson Thanks, I have used the same approach using power automate. Thanks :) – Shaikh Naushad Sep 15 '20 at 19:12
  • @ShaikhNaushad great! I'm glad you were able to work things out—if you have time to share your approach as an answer, I'm sure it will be very helpful to others with similar goals. – Daniel G. Wilson Sep 16 '20 at 23:28
1

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.

Shaikh Naushad
  • 53
  • 1
  • 9