Grades Books for Departments - Microsoft 365 Education

Grade Books for Departments

Introduction

I explain on page ` "Grade Book" that Grades Books for personal use of an individual teacher can be created in a SharePoint site of a special Team created for this particular task. Since the unique member of this team is a teacher, there is only one problem of manually creating permissions for students. Here I explain how this process can be made automatic. In turn, this new method allows one to collect all Grade Books in the Share Point site of Department's Team.

The last possibility is important. It is standard now in Microsoft 365 Education to buy licenses A5 for directors, Heads of Departments, etc. The main reason for this is that any user with A5 license gets access to Power BI Pro. This program represents graphically statistical reports in the cloud. Recently, Microsoft included into Power BI Pro a possibility to read data directly from Microsoft Teams. Since my Grade Books are nothing but Microsoft Lists, this new feature allows the Heads of Departments to collect current information on all classes on the weekly basis without any efforts.

Learn more about what we do

Construction of a Power Automate

It is shown in "Grade Books" how one can make an Excel FileName.xlsx Table imported directly from a Team using Team PowerShell and an Excel Automate. If we need several Grade Books then we first create a template manually and then just create other books from this one.

This Excel Table is cooked up for my Microsoft List. Columns, A and U are type "Number" with zero digits after the comma. Columns, B, C and T are of text type. Other columns have type "Number" with two digits after the comma. Columns AT1, AT2, Total will correspond to calculated columns in the list. Please, notice the Excel formulas do not transfer to Microsoft Lists by Power Automate. You should do this manually in the List using Microsoft Lists formulas instead. To make this list visible to Power Automate we put it to a directory in OneDrive and open it with Excel Online. Excel Online can be opened in https://office.com.

If column A includes characters # instead of numbers we just again assign to the cells of this column type "Number" with zero digits after the comma. We save this file from Excel Online with another name, say FileNameOnline.xlsx. It is not absolutely necessary. However, if something goes wrong, we do not lose the initial file.

To create a new list in the chosen SharePoint site (in this case called as "Exams") we open it from Teams and click on the "Site content" on the left pane. This trick works only for modern SharePoint sites. Another option working also for Microsoft's template sites is to press the setting button (see 2).

Here is the case of a Department site constructed from a template in Microsoft Lookbook.

https://lookbook.microsoft.com

I press the blue button at the top right and chose "Site contents" in the drop-down menu.

In both cases above we reach lists and to create one I press "+New" button and then select "List in the drop-down menu.

I press "+ Blank list" in the new window and follow the instructions. It is obligatory to give a name to a list. In this case I write - Grade book template, in the corresponding form's field to use it for easy constructions of other lists in a future.

The list already has the column - "Title." It is impossible to change its type. In a future, this column may be used to connect tables exported from the list. I add columns following the names of the columns in the Excel Table. To add a column, I just press the check mark to the right of "Add column."

The following actions are self-explaining.

I create columns "FirstName" and "LastName." After that I create number columns:

HW1

HW1, Q1, Q2, MT. Now I create the first calculated field AT1.

AT1

AT1. Finally, I add MT.

AT1

AT1 calculated column is finished.

AT2

Total

ID.

The Microsoft List is finished.

I create a new list using the template.

A new list.

I pick the template.

A new list is ready.

I open the desktop office. Here is the list of all programs I need. This time I chose the Power Automate and press the icon to start the creation of my Power Automate.

I press "+ Create".

I press "Instant cloud flow".

The name of the flow must be in field 1. I press the trigger (see 2). Finally, I press "Create" (see 3).

I press next step.

In the search field I type "Excel" and press "Excel Online (Business)".

I press "List rows present in a table."

The fields in the form are filled by pressing the check marks.

OneDrive for Business is the value, which must be in "Location".

The value in "Document Library" is OneDrive.

Using the slider I find the top directory which contains the Excel Table. I click on the arrow on the right to enter the folder.

I open NSE2002Fall

I open FurtherCalculus.

I select the Excel file.

I select the name of the Table.

Table1

Now I press "+ New step" and in the search field type "Apply".

I press "Apply to each".

To fill in the field I press "Add dynamic content" and chose "Value (List of Items)".

I Press "Add an action."

I type "Share" in the search field and press "SharePoint."

Among actions I chose "Create item (SharePoint)."

I press check marks 1 and 2 to find the correct dynamic content.

I select the line underlined in red which indicates the site with the list I already created.

I select the name of the list I created and press "Show advanced options" if they will not open automatically.

I fill in only those fields which has data in the columns of the Excel file. Then the correct items appear automatically if I put the cursor in the field of such type.

Title

FirstName, LastName.

I chose "Item" and press "Add an action."

I type "Share" again and press "SharePoint."

I select action "Grant access to an item or a folder."

This form is filled dynamically as above.

To fill field "Id" I press "Add dynamics content" and choose "SharePoint ID (List item id. Use this value for specifying the item to act on...)."

In "Recipient" field I insert dynamically "Email".

I chose "Can view" to insert it in the Role field. This means that the user with the Email inserted above can only view this row.

In the message field I insert the dynamic FirstName and Last Name followed by the message. At this moment I put "No" in "Notify" field since otherwise students will receive emails. In the real class one should chose "Yes".

I press "Save" button. The system formally checks the script and reports that "You flow is ready to go. We recommend you test it." See the text in green at the very top of the window.

I press "Test" at the right top corner, mark "Manually" and press the "Test" button at the bottom.

The flow starts, the Power Automate signs up to Excel Online and SharePoint and recommends to press "Continue."

I press "Done."

The system reports the success. This means that the list has been created.

The list is ready. There is only one member in this site. To check if the role was assigned to students I chose the first line and press "i" in the green circle.

I press "Manage access" (see 2).

The system reports that the role was assigned to the first row. In this case only the teacher is the owner and can see and edit all grades, whereas students can only read their personal grades.

In case of the department site we cannot apply this method since in this case other teachers can see and edit all grades. To correct this I hook the vertical arrow in the middle and press the "+" sign to insert a new action.

I again typed "Share" in the search field and found action "Stop sharing an item or a file." After that I just filled in the fields of the form. The test shows that the automate works.

The system shows that in this case only two people have access to the first row, namely the teacher and the student.

The final step is to change the style. The size of the font is changed to medium and bold, the odd rows are colored in green and the even in yellow. The Grade Book is ready.

The first action in this Power Automate is called: "List rows present in a table." It has a default limit of 256 rows. The following reference explains how one can increase this number:

https://powerusers.microsoft.com/t5/Connecting-To-Data/Excel-Business-List-rows-present-in-a-table-256-row-limit-any/td-p/116355

However, one can split the Excel file into a number of smaller ones and name them as File1.xlsx, File2,xlsx, etc. You should only follow the rule that they are obtained from the initial file by cutting them in the order of the rows. Then you run the Power Automate for File1.xlsx. After that rename it as FileA.xlsx, say. Rename File2.xslx as File1.xslx and run the Power Automate again. Then the rows of the second file will be inserted into the Microsoft List after the rows of the first file. Continue by induction until the rows of all files will be copied into the Microsoft List.