✨Tutorial: Easy Student Consent Management in Microsoft 365

Welcome to AutomatED: the newsletter on how to teach better with tech.

Each week, I share what I have learned — and am learning — about AI and tech in the university classroom. What works, what doesn't, and why.

In this fortnight’s Premium edition, I present a Tutorial for efficiently managing student consent in Microsoft 365. This is an easy-to-implement, effective, and generalizable way to gather and tabulate your students’ consent (or lack thereof) for your use of recording software in class or in professor-student meetings or for your use of AI tools with their work.

🖼️ The Big Picture

In my recent ✨Guide to Ethically Using AI with Student Data, I present seven options:

• 🛑 Option 1: Don’t Run Student Data Through AI

• 🦺 Option 2: Limit to “Completely Safe” Categories

• 🏰 Option 3: Stay Within Your Ecosystem

• 🗣️ Option 4: Change the Consent Paradigm

• 🥷 Option 5: Pseudonymize or Anonymize

• 💻 Option 6: Use Local LLMs

• 🔀 Option 7: A Combination of the Above

In this Tutorial, I explain how to reduce the cost and inconvenience of recording student consent in the Microsoft ecosystem while improving transparency and communication with your students. This sort of solution is needed if you take Option 4 in the aforementioned Guide.

But why might you take Option 4 and attempt to change the consent paradigm with your students?

Here is an extended excerpt from the Guide of some of the considerations:

With these considerations and alternative Options in mind, let’s consider one simple way to make large scale consent acquisition, management, and recording much simpler, at least within the Microsoft ecosystem.

📝 Step 1 - Create Form(s)
for Collecting Consent

The first step in simplifying the process of acquiring student consent is to create a structured and clear method for gathering their permissions. Utilizing Microsoft Forms provides an efficient and user-friendly way to achieve this. With Microsoft Forms, you can design customized forms that specifically cater to your needs, ensuring that all necessary information is collected in an organized manner. This not only streamlines the process but also enhances transparency and trust with your students by clearly communicating what they are consenting to.

Creating these forms involves setting up fields to capture essential details, such as the student's name and email address, and presenting the consent options in a straightforward format. Additionally, as I will show below in the subsequent Steps, the responses can be easily managed and recorded within the Microsoft ecosystem, making it convenient for future reference and compliance purposes.

Let’s get started! Here is how to proceed:

1. Sign in to Microsoft Forms using your institutional Microsoft 365 account.

2. Create a new form:

   • Navigate to forms.office.com.

   • Click the "New Form" button.

   • Title the form (e.g., "Class 123: Professor-Student Meetings Consent Form").

3. Add a description at the top that discloses what you are planning to do with the relevant student data. This description should outline what you are requesting that the student consent to.

Obviously, the Form that your description ought to take is complicated and context-sensitive. In this Tutorial, my focus isn’t on the details of an ethically good and legally compliant description. However, I will flag a few of the complications here, using an excerpt from my ✨Guide to Ethically Using AI with Student Data. Note that this is an excerpt focused on the specific case of using student data with AI tools, but the points generalize to recordings and other cases where student consent is needed:

5. Add the first question to capture the student's name:

   • Click "Create your first question" (the plus sign).

   • Choose "Text" as the question type.

   • Label it "Your Name".

   • Make it a required question.

6. Add the second question to capture the student's email address:

   • Click "Add new question".

   • Choose "Text" as the question type.

   • Label it "Email Address".

   • Make it a required question.

7. Add the third question for consent:

   • Click "Add new question".

   • Choose "Choice" as the question type.

   • Label it as you like (e.g., "Do you consent to the above?").

   • Provide three options: "Yes", "No", and "Not sure".

   • Make it a required question.

8. Preview and test your form:

   • Select "Preview" to see how your Form will look on both computer and mobile devices.

   • Test out your Form by answering the questions in Preview mode and then select "Submit".

   • To keep editing your Form, select "Back".

Here is what you will end up with:

9. Repeat this process for all the cases where you expect to need to acquire student consent for this class.

🔗 Step 2 - Link Form(s) Responses to “Source” Spreadsheet(s) and then to a “Destination” Spreadsheet

The second step in our process involves linking the responses from the Microsoft Forms created in Step 1 to Excel for easier management and analysis.

We will create "source” spreadsheets for each Form to store individual responses and then consolidate these into a single "destination" spreadsheet. By linking each Form’s responses to a dedicated "source" spreadsheet, you ensure that all data is organized and accessible in a structured manner. Subsequently, the "destination" spreadsheet will aggregate these responses, allowing you to see at a glance which students have given consent and which have not.

For instance, in my toy example, we end up with a “destination” spreadsheet that looks like this, dynamically filled based on student responses:

This method not only simplifies the process of tracking consent but also helps in maintaining a high level of organization and clarity, crucial for both compliance and communication purposes.

Let’s continue. Here are the sub-steps:

1. Create a "source" workbook:

   • In the Microsoft Form, navigate to the "Responses" tab.

   • Click the "Open results in Excel" button to create a new Excel workbook in your OneDrive (this will contain your "source" spreadsheet) and then click “Continue.”

   • Name the "source" workbook appropriately (e.g., "Professor-Student Meetings Consent Workbook") and name the first spreadsheet in it — the “source” spreadsheet for this Form — correspondingly (“Professor-Student Meetings Consent Spreadsheet”).

2. Repeat for each of the Forms you created in Step 1:

   • Follow the same process to create a separate Excel workbook for each Form.

The next step is to create a “destination” workbook that gathers all of the responses from the "source” spreadsheet(s).

3. Create a new “destination” workbook from scratch (blank for now):

   • Open Excel on your desktop (see above for Note on this subject).

   • Create a new blank workbook and name it (e.g., "Class 123 Consent Aggregation Workbook").

   • As above, name the first spreadsheet in it — the “destination” spreadsheet — correspondingly (“Class 123 Consent Aggregation Spreadsheet”).

4. Format the “source” spreadsheets in an organized and consistent manner:

   • In each spreadsheet, order the columns to reflect the order of the questions, if they aren’t in the appropriate order already.

   • Bold their names if you like.

Here is how my “source” spreadsheets look (note that there are built-in fields for “Id”, “Start time”, “Completion time”, “Email”, and “Name” — these are either supplied by the user’s account login or the Form itself, and I will discuss them below):

5. Link each of the “source” spreadsheets to the “destination” spreadsheet:

   • The best strategy to do this is with Excel’s Power Query functionality.

   • To use it, navigate in your "destination" workbook to the "Data" tab on the ribbon.

   • Click on "Get Data" > "From Other Sources" > "Blank Query" (or launch Power Query Editor directly).

   • In the Power Query Editor, click on "New Source" > “File” > “Excel Workbook”.

   • Navigate to the first "source" workbook stored in your OneDrive and import its “source” spreadsheet.

   • Next, click on "Close & Load To" under the "Home" tab on your ribbon.

   • Select the “Table” radio button and “New worksheet”.

   • The data from the “source” spreadsheet will now be loaded into your "destination" workbook as a new spreadsheet.

   • In your “destination” workbook, delete the “Query” spreadsheet that gets automatically added alongside the new spreadsheet.

   • Repeat for any other “source” spreadsheets you have — add them as new spreadsheets in your “destination” workbook via Power Query, deleting each “Query” spreadsheet that gets automatically along the way.

6. Set each Power Query to refresh at a regular interval:

   • Select a cell (any cell) within one of the “source” spreadsheets that you have added as new spreadsheets within your “destination” workbook.

   • Go to “Data” in the top ribbon.

   • Click the “Refresh All” dropdown > “Connection Properties”.

   • Adjust the settings to your liking. I prefer “Refresh every 15 minutes” and “Refresh data when opening the file” (both). (You can also always manually refresh all, as needed.)

   • Press “OK”.

Now you need to connect the spreadsheets’ contents. Within the “destination” workbook, you need a formula that draws the Form responses from the “source” spreadsheets into the “destination” spreadsheet.

In particular, you need a formula that can handle the fact that students will respond to the consent Forms in an unpredictable order.

However, you know who your students are — what their names are and what their email addresses are — and you can use this knowledge with a formula to “look” for their names when they appear in the “source” spreadsheets. Here’s how…

Subscribe to Premium to read the rest.