cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jendebett
Regular Visitor

data connection refresh excel with flow

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?

48 REPLIES 48
ChristianAbata
Most Valuable Professional
Most Valuable Professional

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



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me:
Youtube: Christian Abata
Facebook: Power Automate LA
Power Automate examples: Power Automate examples

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?

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



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me:
Youtube: Christian Abata
Facebook: Power Automate LA
Power Automate examples: Power Automate examples

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 

 

4-22-2020 4-58-44 PM.jpg

 

 

 

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
Regular Visitor

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 ?

 ThanksMy Flow.png

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
Not applicable

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

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
Regular Visitor

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
Not applicable

anyone with an update on this?

@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?

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?

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. 

 

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.

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.

 

External data.PNG

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?

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. 

Can you post a picture of your flow?

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (1,839)