cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheShaunSaw
New Member

Create excel file to check whether a file in folder is uploaded

Hi everyone,

I am a teacher using Microsoft Teams and I ask my students to upload their daily work in their named folders on Sharepoint. I want to check whether they have uploaded the work or not. So I want to create an Excel Sheet having names of students in the first column and text as 'Yes' or 'No' in the second column beside their name to check whether they have uploaded their file in the folder or not in the last 24 hours or not. I have tried making a flow but I am not understanding how to make it, can anyone please help me with this?

1 REPLY 1
Expiscornovus
Super User
Super User

Hi @TheShaunSaw,

 

One approach could be to list the rows present in the excel and for each value in the student column do a get files (properties only) query. In this query we can check the student email and if it is created in the last 24 hours via a filter query expression.

 

Below is an example of that. I am using this excel sheet in this example.

 

studentexcelsheet.png

 

1. Add a List rows present in a table action. Configure it to use the Excel sheet with the overview of students and the yes/no column.

uploadedoverview.png

 

2. Add an apply to each action. Use the value field of the List rows present in a table for the Select an output from previous steps field

 

3. Add a Get Files (properties only) action within the apply to each action. Configure it to use the upload folder location. Use the following expression for the filter query:

(Author/EMail eq '@{items('Apply_to_each')?['Student']}') and (Created ge '@{addDays(utcNow(), -1)}')

 

dropfolderstudents.png 

 

4. Add an update a row action within the apply to each. Configure it to use the same excel sheet and use the following expression for the Uploaded column.

This expression will check if the previous action has any results, in other words if a student has uploaded something.

if(equals(length(outputs('Get_files_(properties_only)')?['body/value']), 0), 'No', 'Yes')

 

updaterow_folderstudents.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (2,724)