This tutorial presents a solution for the following questions: How can I automatically transfer the grades from a prepared excel sheet (for my own records) into the webform of Angel (which sadly does not provide an import function).
The procedure is rather simple, but you will need some preparation to execute the macro I wrote. For this, you can simply follow the instructions:
- You need to prepare an excel sheet (or another spreadsheet) and save it *.csv file using the Save as option. Usually, it is a good idea to work mainly with the *.xlsx file that supports formulae and only save a copy as *.csv that you use to import the grades. When you update the *.xlsx file you can just overwrite the *.csv with the new version for importing new grades.
- In order to prepare your Excel sheet, please download this example Excel sheet. Feel free to adjust it, but make sure that the field in the first column and in the row of the first student (usually the second row) contains”datalistUsersGrades_ctl02_txtPercentage” (notice the 2). This number should then count upwards for the subsequent rows. The example Excel sheet contains a function that should automatically generate the correct names when you extend it. The expression refers to the ID of the HTML field in Angel which we want to fill with the student information.
- My macro is based on the browser plugin iMacros. This plugin is freely available for Chrome and Firefox. You need to install it before you can proceed.
- (Only for Chrome) Moreover, you need an additional feature of iMacros that allows you to access your *.csv file from your computer. This website lists the links that you need to add file access for Chrome. If you are using iMacros for Firefox you don’t need to do anything because file access is already integrated in the main plugin.
- Start your browser (Chrome or Firefox) and open iMacros by clicking on its symbol. The following window should appear (without 251 – Filling form.iim):
- In the bottom part, go into the section Manage and to Settings. In the appearing window copy the location of your Excel files on your computer into the fields labelled Macros directory path and Datasources directory path. You don’t need to save, it’s. The result could look like this:
- Download the following file and copy into the folder you just specified. This is the macro I wrote whose source code is extremely simple and a slightly adjusted version of an online available resource (just as a reference).
- After you copied the file name Filling form.iim should appear in the main window of iMacros, just as it does in the picture. If it doesn’t appear, you might need to restart your browser and iMacros.
- Now we are ready to copy the data from your excel sheet into an *.csv file where you should use “CSV (Comma delimited) (*.csv)” in Excel’s Save as Dialogue. Please make sure your excel file is saved as *.csv into the location used in iMacros. The file should look like this:
- Click right on the file Filling form.iim within iMacros and select Edit Macro. If you open the file you will find the following extremely simple source code (two letters that you might have to adjust later are in red and blue):
VERSION BUILD=8031994
‘Uses a Windows script to submit several datasets to a website, e. g. for filling an online database
TAB T=1
‘ Specify input file (if !COL variables are used, IIM automatically assume a CSV format of the input file
‘CSV = Comma Separated Values in each line of the file
SET !DATASOURCE filename.csv
‘Start at line 2 to skip the header in the file
SET !LOOP 2
‘Increase the current position in the file with each loop
SET !DATASOURCE_LINE {{!LOOP}}
‘ Fill web form
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:Form1 ATTR=ID:{{!COL1}} CONTENT={{!COLY}} - Change the filename (red in above source code) to the filename of your *.csv file whose grades you want to copy. Change the Y (blue in above source code) to the column containing the grades that you want to copy.
- Now open Angel in your browser and go to the course you would like to enter the grades for. Click on Manage and then on Gradebook. Usually, you will want to enter By Assignment, for which you need to click right on it and select Open in new window. This is very important in order to give access to iMacros (otherwise the gradebook will only appear in a html frame).
Don’t be surprised that after you open the gradebook in a new window the angel menu vanishes. This is exactly as it should be. - Now you can select assignment and section for which you want to fill in the grades. Make sure that the students are exactly listed as they are in your excel sheet. The result should look like this (names are censored:
- At this stage, we are ready to execute our program. Open iMacros, select the macro Filling form.iim and choose Play macro repeatedly a Max equal to the number of students. Then click Play loop and the grades should be transferred into Angel.
Final remarks
Let me stress that all these tips serve a single purpose: to take unnecessary and dull workload from you, so that you can focus on the important part of teaching, providing the best possible learning experience to the students! You might also be interested in how to organize your recitations groups effectively.
[…] Excel to Angel […]