Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

Scheduled data update on SharePoint

(0) ShareShare
ReportReport
Posted on by 10

Hi,

 

I have an Excel file (File 1) on SharePoint (to be used among SharePoint users) which needs to look up a table (Table 1) with data that come from SQL server (Database).  The Table 1 needs to be refreshed daily.  However, the access to this Database is restricted to very few people (including me).  Therefore, I cannot connect Power Query directly from File 1 to Database, since not all SharePoint users are allowed to access Database.

 

I am thinking of the following overall approach but I am still not sure how to get it done:

1. Create an Excel file (File 2) with the query results (Table 2) on my OneDrive.  File 2 thus has access to Database.

2. Refresh Table 2.

3. Open access of File 2 to File 1.

4. Refresh Table 1 from Table 2 via Power Query.

5. Have Power Automate to have #2 and #4 daily.

 

If the above approach is feasible, can you please advise what is the flow required for #5?

 

Thank you.

  • Re: Scheduled data update on SharePoint

    Hi @NercyCrab :

    It looks like your connection to the SQL Sever database is not working.

    You can click on the "..." in left and create a new connection.

    Best Regards,

    Bof

  • NercyCrab Profile Picture
    NercyCrab 10 on at
    Re: Scheduled data update on SharePoint

    Thank you very much.  This seems an easy and elegant solution. 

     

    However, I am getting an error message "connection not found" when I try to execute a SQL Server query in PA.  Any advice? 

  • Re: Scheduled data update on SharePoint

    Hi @NercyCrab :

    You can create a scheduled cloud flow, extract the data in the database every day, and then use the acquired data to create a CSV file and replace it with the original excel file in SharePoint.

    Best Regards,

    Bof

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard

Featured topics