Forum Discussion

PDTaylor's avatar
PDTaylor
Copper Contributor
Oct 22, 2021

Excel Script - Refresh data

Hi, 

 

I have an excel file which has a data connection. I have been trying to get the data and associated pivot tables to refresh through a script but it doesn't seem to work fully. The script is as follows:

 

function main(workbook: ExcelScript.Workbook) {
  // Refresh all data connections

 

  workbook.refreshAllDataConnections();
  workbook.refreshAllPivotTables
  }
 
I am prompting the script to run through PowerAutomate a couple of times a day. The file will appear to be modified (last modified date of file reflects when the script was last run through PowerAutomate) but the data in the Pivot table is not up to date from the data source.
 
If I open the file online will get a message to enable the queries so I don't know if this needs to be part of the script too?
 

 

Any thoughts? 
 
Thanks
excel
Office Scripts

8 Replies

  • DirkvH's avatar
    DirkvH
    Copper Contributor
    Nov 15, 2023

    PDTaylor 

    I used your code and it did not work
    I did not get an error message but the pivot table just did not refresh
     by adding () at the end and it works

     

    sample code

     

    function main(workbook: ExcelScript.Workbook) {
      // Refresh Pivot Tables
      workbook.refreshAllPivotTables()
    }
     
    I know it is a bit late but perhaps someone still has some use for this
    😉
    • Klaas Langhout's avatar
      Klaas Langhout
      Icon for Microsoft rankMicrosoft
      Oct 25, 2022
      Petra, any other suggestions for how with PowerAutomate/OfficeScripts to 'refresh' a query and pivot table ? Want to get to use PowerAutomate to send the latest info including a refresh.
  • Bisio's avatar
    Bisio
    Copper Contributor
    Aug 02, 2022
    Hi, I am having the same issue. Did you find any solution?