data connection refresh excel with flow

[jendebett]

Hello,

I have an excel workbook saved in a document library on sharepoint. The excel file contains a table with data from a .txt file.

every day a new .txt file is emailed, and I have used flow to save the file in sharpoint.

I have the data connection set to refresh in the background, and refresh on opening.

so every day I open excel, allow the table to refresh, save and close.

i'd really like to have flow do this data refresh without opening excel... is this possible?

[ChristianAbata]

mmm using defaut PA actions not, but you can use UI flows to make this open excel proces automated.

[jendebett]

how do i use flow to open and refresh the data connection on the excel file?

I already have the data source (txt file) saving daily automatically

now i need to refresh the excel file daily and save.

that way when it saves i can have flow email it. is this possible?

[ChristianAbata]

no my friend by using PA you can't open a file. Just with UI 

[jendebett]

is it possible to refresh the data without opening the file? I would prefer to not set up a ui flow so it can still run if im not logged into my computer.

i tried this but it doesnt work

when a file is created or modified in a folder (this is where the text file is)

list rows present in a table (this is in the excel file)

apply to each> update a row 

[paulburnett224]

1) Save the excel xlsx file to one drive or SharePoint and make sure you do not keep a local copy that is syncing. If you are keeping a local copy of the report it will prevent the refresh because it causes a lock: see step 4: https://support.microsoft.com/en-us/office/excel-file-is-locked-for-editing-6fa93887-2c2c-45f0-abcc-...

2) in excel online, open the xlsx file recommend going to file\info and protect the sheet. This set the sheet to view mode when opening, as an added precaution.

3) in excel online (this only works in online and the file has to be an xlsx and not a xlsm file), click on "Automate" and "code editor."

4) put this code in:

function main(workbook: ExcelScript.Workbook) {

// Refresh all data connections

let selectedSheet = workbook.getActiveWorksheet();

selectedSheet.refreshAllPivotTables();

}

5) make sure you checkmark the "share with others in the workbook" in the script so power automate can see the script.

6) in power automate, do the excel online (business), trigger script connector. Select your script and done!

[ELEIBLE]

Hi @paulburnett224 . Couple questions for you. If you have two connection queries, will your code above refresh both queries ? The data from each query is located on its own spreadsheet within the workbook. I added the code using the automate tab and i also allowed the code to be shared so that it's picked up by my flow. Please see my flow attached. When my flow sends me the attached report, the queries within the report still have not been updated. Can you let me know any issues you see or any watch outs i should be concerned with as i'm trying to figure out why it's not refreshing ?

 Thanks

[SJacobs]

A few points: 

The documentation on running a script in excel from power automate is here:

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#run-script-(preview)

You'll be using an action, not a trigger.

The sample code you provided is to refresh a pivot table  (selectedSheet.refreshAllPivotTables();)

This solution did not work for me, as my data connection is to a source that requires authentication to perform the refresh.

[Anonymous]

S, did you ever solve the issue of refreshing Excel? I have a similar project.

[ChaitanyaTagare]

Running above office script manually, refreshes the pivot table and data. But when I am trying to trigger this script from power automate flow, data not getting refreshed. Please suggest if any configurations required or is there any limitations while running the script from power automate.

[echu128]

I am experiencing the same problem where the script does nothing, even though I recorded the script with macro. Does anyone know if this is a limitation or am I missing something?

[Anonymous]

anyone with an update on this?

[SirHC]

@paulburnett224, thanks for this information. I need something similar. It seems it would only update All Pivot tables from the active worksheet. is there a way to have All Tables in the workbook updated?

[eatcrispycreme2]

Same issues here, I'm wanting to automatically update an excel query that's pulling in outside data but can't seem to do it with flow. Anyone have any updates?

[driekes]

I have a similar issue. My XL file in the app has updatet data. I close the file, and this syncs with the onedrive client.

When using this  to be sent in an email using a flow, I get non-updated data. 

[teespolyglot]

Flow seems to have some sort of in-built limitation where it refers to a historical copy of the file for a certain time period. I recently constructed a flow to update an excel and email the output to our engineers, but when they submitted the form, it would email the previous person's input. 

I was able to resolve my issue by, at the start of the flow, creating a new file from a template, inputting the data in that sheet, sending that copy and then deleting the copy at the end of the flow.

I'm not sure if this is a possibility in your flow? Prior to figuring that out, I was only able to guarantee it would give the correct output if you set a delay of around 2 mins before emailing the file.

[driekes]

It would be nice to have some statement from MS on this. I have quite a simple flow. Creating new files is not something I want to do, to avoid making things overcomplex. 

My excel file refers to another .xlsm file (This file also has external data connections). As you can see in the formula in the picture. When I open this file in Excel Online (It contains a table from which I create a html table to mail around), the external data connections have been disabled. Maybe this could be the reason for my issue. 

I have no idea how to accept external data in that excel file automatically. Could it be a sharepoint setting?

When I open this excel online and click 'Enable Content', run my flow, I do get the updated data I want.

I just have this .xlsx file that refers to a .xlsm file, because a the .xlsm file does not seem to work in Powerautomate.

[teespolyglot]

The main thing I took from your first message was that the file you were sending was not updated. Was the file in fact updated, but the data being sent was not?

[driekes]

The file is updated in the excel app. It sync's with onedrive (to sharepoint) . When sending the file from the flow (by getting it from sharepoint), it was not updated.

I have to manually open the file in excell online, accept external data connections, run the flow, and then the updated data is mailed around. Look a security setting in Excel online that prevent the file on SP to get updated. I do not get this message in the excel app. I configured trustcentre in the excel app, to renew dataconnections automatically. 

[teespolyglot]

Can you post a picture of your flow?