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

Link new Excel sheets to App automatically

Hi all,

 

I am currently working on a Planning Application, to make the process of daily production planning smoother. 

 

My current inputs are: 

  1. Dispatch Plan
  2. Work in Progress
  3. Material Availability 

The Dispatch Plan & WIP is generated & saved on the application.

 

My problem is the Material Availability. At the moment this information is sent to us daily in the form of an Excel sheet. 

 

My question is... Is there a way that I can link this automatically to my application, from whatever folder/server I choose to store these excel sheets? 

 

So say I am planning today for tomorrow's production. The app will automatically (based on the date) select the dashboard issued today. 

 

Thanks in advance. 

3 REPLIES 3
Super User III
Super User III

@Zelda 

You asked "is there a way that I can link the material availability spreadsheets automatically to my application from whatever folder/server I choose to store these excel sheets?"  My response is: "you can accomplish your goal just not in this way".  The reason is every Excel spreadsheet used by your app requires a connection.  Connections must be added manually in PowerApps Studio.  Therefore, we must find another way.

 

I have two ideas that I feel could be well suited for your project.

 

#1  Save an Excel Workbook OneDrive having Master Parts list.  Each day the new Material Availability could be populated with new information.  The Excel files sent as a Daily Worksheet could be stored in another location as backup.

 

#2  Create a SharePoint list called Material Availability having the same columns as the Excel Workbook.  Require the Workbook to be submitted through a PowerApp instead of via email.  Then build a Flow to automate the transfer of data from the Excel workbook to the SharePoint list.  See this link for an example of what the flow might look like (although it would require some modification and a different trigger) https://powerusers.microsoft.com/t5/Building-Flows/Create-SharePoint-list-items-from-Excel-rows/m-p/...

 

An added advantage of using the SP List is you could add a new column called 'Report Date' and track what the availability of parts was for every submission date.  Your app could employ a simple Filter though to retrieve only the most current records.

 

I hope these ideas provide some inspiration for your app!

 

---

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Hi @mdevaney  thanks for the help. 

 

I have since decided that because the process is so manual (the guy obtains the info and manually adds it to the excel sheet daily), I'll rather have the guy fill in the material availability on my app.

 

With this I have another question. Is there a way that I can add a column (daily) and then with forms see what is in that column. 

 

So, to explain it a bit more....

 

I want the planner to be able to view previous plans, edit & view today's plan, & create tomorrows plan when selecting NEW PLAN button. 

The NEW PLAN button, will check tomorrows date & see if there is a column in my SP list which is called "Tomorrow's Date" & if not create a new column named "Tomorrow's Date". 

 

If the planner wants to view previous plans, they can select the date maybe on a date selector (or drop down list with column names) what date they want to view, this then refers to the column names and displays that particular column's information.  

@Zelda 

"Is there a way that I can add a column (daily) and then with forms see what is in that column.  

I want the planner to be able to view previous plans, edit & view today's plan, & create tomorrows plan when selecting NEW PLAN button.  The NEW PLAN button, will check tomorrows date & see if there is a column in my SP list which is called "Tomorrow's Date" & if not create a new column named "Tomorrow's Date".

 

Yes, there definitely is a way to add a daily column and then perform a FILTER.  I'm having a tough time responding to your question though because it is so broad.  Maybe if you can define a more specific problem I can help with it.  In my opinion the best way to figure these things out is to dive right in and start experimenting.  If you get stuck just start a new thread on the forums and someone will come to help you!

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (82,289)