Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Excel Datamodel scheduled refresh

I know that changes are coming that are going to affect Excel Scheduled refreshes.   I have a few questions on this since we use this feature a lot at our corporate location.   If all of the Exce...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Alan_ , so I'm not sure what the issue is, but the Power Automate task is failing on the refresh. It worked when I tested it, but on the schedule for the last 2 weeks, it has been failing:

     

     

     

    {
  "error": {
    "code": 504,
    "source": "flow-apim-msmanaged-na-northcentralus-01.azure-apim.net",
    "clientRequestId": "****"
    "message": "BadGateway",
    "innerError": {
      "status": 504,
      "message": "Request to Graph API has timed out.\r\nclientRequestId: ****",
      "error": {
        "message": "Request to Graph API has timed out."
      },
      "source": "excelonline-ncus.azconn-ncus-001.p.azurewebsites.net"

     

     

     

    The Service Refresh is working fine.  The Power Automate task runs after about an hour after the service refreshes the Dataset.  The flow has 2 retries.

     

    ** UPDATE ** Turns out the refresh Office script is timing out due to it taking longer than the 2 minutes limit that Power Automate has for Office scripts.  Changing the Timeout duration on the Run Script step does not fix this.

     

     

    So knowing this, this "solution" is no longer the solution we thought it was.  We need another way to do this.

     

    ** UPDATE 2 **

     

    Turns out that I got the Power Automate to finally work!  It takes about an hour and a few retries, but it finally refreshed successfully.  I had to set Fixed Intervals on the Run Script Action Step, but that seemed to work.  Will have to monitor the flow's performance next week. I upped the Count to 20. On the sucessful run it retried 5 times.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Alan_ , that's awesome!

For Step #3, Are you using the "Run Script From Sharepoint Library" or the "Run Script" to get that to work?  Flow something like this?  

    

 



What Office Script code are you using?

Is it this?

 

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

 

 

Alan_'s avatar
Alan_
Advocate II
2 years ago

I'm using Run Script and calling Refresh File:

With this, I can reference files in SharePoint libraries. I haven't tried Run Script from a SharePoint library but that might work too. 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Alan_ , Will this work with Datasources that are from SQL if published to a Power BI Dataset?

    • Alan_'s avatar
      Alan_
      Advocate II
      2 years ago

      Can't see why not. The datamodel is refreshed on the PowerBI service as normal ableit as a PowerBI dataset rather than an Excel Datamodel. 

      The Excel workbook refresh is just a refreshing in whatever the Power BI service has. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Alan_ , that makes sense.  Any reason why we need to schedule the Power BI Dataset refresh in Power Automate rather than scheduling the refresh in the Power BI Service and then perform the Power Automate Refresh script?  If you roughly know how long the refreshes take, then you can schedule the Dataset refreshes way early...and then run the refresh on the Excel files later.