Office Scripts sample scenario: Grade calculator

In this scenario, you're an instructor tallying every student's end-of-term grades. You've been entering the scores for their assignments and tests as you go. Now, it is time to determine the students' fates.

You'll develop a script that totals the grades for each point category. It will then assign a letter grade to each student based on the total. To help ensure accuracy, you'll add a couple checks to see if any individual scores are too low or high. If a student's score is less than zero or more than the possible point value, the script will flag the cell with a red fill and not total that student's points. This will be a clear indication of which records you need to double-check. You'll also add some basic formatting to the grades so you can quickly view the top and bottom of the class.

Scripting skills covered

• Cell formatting
• Error checking
• Regular expressions
• Conditional formatting

Setup instructions

1. Download sample workbook to your OneDrive.

   Download the sample workbook

2. Open the workbook in Excel.

3. Under the Automate tab, select New Script and paste the following script into the editor.

   function main(workbook: ExcelScript.Workbook) {
     // Get the worksheet and validate the data.
     let studentsRange = workbook.getActiveWorksheet().getUsedRange();
     if (studentsRange.getColumnCount() !== 6) {
       throw new Error(`The required columns are not present. Expected column headers: "Student ID | Assignment score | Mid-term | Final | Total | Grade"`);
     }
   
     let studentData = studentsRange.getValues();
   
     // Clear the total and grade columns.
     studentsRange.getColumn(4).getCell(1, 0).getAbsoluteResizedRange(studentData.length - 1, 2).clear();
   
     // Clear all conditional formatting.
     workbook.getActiveWorksheet().getUsedRange().clearAllConditionalFormats();
   
     // Use regular expressions to read the max score from the assignment, mid-term, and final scores columns.
     let maxScores: string[] = [];
     const assignmentMaxMatches = (studentData[0][1] as string).match(/\d+/);
     const midtermMaxMatches = (studentData[0][2] as string).match(/\d+/);
     const finalMaxMatches = (studentData[0][3] as string).match(/\d+/);
   
     // Check the matches happened before proceeding.
     if (!(assignmentMaxMatches && midtermMaxMatches && finalMaxMatches)) {
       throw new Error(`The scores are not present in the column headers. Expected format: "Assignments (n)|Mid-term (n)|Final (n)"`);
     }
   
     // Use the first (and only) match from the regular expressions as the max scores.
     maxScores = [assignmentMaxMatches[0], midtermMaxMatches[0], finalMaxMatches[0]];
   
     // Set conditional formatting for each of the assignment, mid-term, and final scores columns.
     maxScores.forEach((score, i) => {
       let range = studentsRange.getColumn(i + 1).getCell(0, 0).getRowsBelow(studentData.length - 1);
       setCellValueConditionalFormatting(
         score,
         range,
         "#9C0006",
         "#FFC7CE",
         ExcelScript.ConditionalCellValueOperator.greaterThan
       )
     });
   
     // Store the current range information to avoid calling the workbook in the loop.
     let studentsRangeFormulas = studentsRange.getColumn(4).getFormulasR1C1();
     let studentsRangeValues = studentsRange.getColumn(5).getValues();
   
     /* Iterate over each of the student rows and compute the total score and letter grade.
     * Note that iterator starts at index 1 to skip first (header) row.
     */
     for (let i = 1; i < studentData.length; i++) {
       // If any of the scores are invalid, skip processing it.
       if (studentData[i][1] > maxScores[0] ||
         studentData[i][2] > maxScores[1] ||
         studentData[i][3] > maxScores[2]) {
         continue;
       }
       const total = (studentData[i][1] as number) + (studentData[i][2] as number) + (studentData[i][3] as number);
       let grade: string;
       switch (true) {
         case total < 60:
           grade = "F";
           break;
         case total < 70:
           grade = "D";
           break;
         case total < 80:
           grade = "C";
           break;
         case total < 90:
           grade = "B";
           break;
         default:
           grade = "A";
           break;
       }
   
       // Set total score formula.
       studentsRangeFormulas[i][0] = '=RC[-2]+RC[-1]';
       // Set grade cell.
       studentsRangeValues[i][0] = grade;
     }
   
     // Set the formulas and values outside the loop.
     studentsRange.getColumn(4).setFormulasR1C1(studentsRangeFormulas);
     studentsRange.getColumn(5).setValues(studentsRangeValues);
   
     // Put a conditional formatting on the grade column.
     let totalRange = studentsRange.getColumn(5).getCell(0, 0).getRowsBelow(studentData.length - 1);
     setCellValueConditionalFormatting(
       "A",
       totalRange,
       "#001600",
       "#C6EFCE",
       ExcelScript.ConditionalCellValueOperator.equalTo
     );
     ["D", "F"].forEach((grade) => {
       setCellValueConditionalFormatting(
         grade,
         totalRange,
         "#443300",
         "#FFEE22",
         ExcelScript.ConditionalCellValueOperator.equalTo
       );
     })
     // Center the grade column.
     studentsRange.getColumn(5).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
   }
   
   /**
    * Helper function to apply conditional formatting.
    * @param value Cell value to use in conditional formatting formula1.
    * @param range Target range.
    * @param fontColor Font color to use.
    * @param fillColor Fill color to use.
    * @param operator Operator to use in conditional formatting.
    */
   function setCellValueConditionalFormatting(
     value: string,
     range: ExcelScript.Range,
     fontColor: string,
     fillColor: string,
     operator: ExcelScript.ConditionalCellValueOperator) {
     // Determine the formula1 based on the type of value parameter.
     let formula1: string;
     if (isNaN(Number(value))) {
       // For cell value equalTo rule, use this format: formula1: "=\"A\"",
       formula1 = `=\"${value}\"`;
     } else {
       // For number input (greater-than or less-than rules), just append '='.
       formula1 = `=${value}`;
     }
   
     // Apply conditional formatting.
     let conditionalFormatting: ExcelScript.ConditionalFormat;
     conditionalFormatting = range.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue);
     conditionalFormatting.getCellValue().getFormat().getFont().setColor(fontColor);
     conditionalFormatting.getCellValue().getFormat().getFill().setColor(fillColor);
     conditionalFormatting.getCellValue().setRule({ formula1, operator });
   }
   

4. Rename the script to Grade Calculator and save it.

Run the script

Run the Grade Calculator script on the only worksheet. The script totals the grades and assigns each student a letter grade. If any individual grades have more points than the assignment or test is worth, then the offending grade is marked red and the total is not calculated. Also, any 'A' grades are highlighted in green, while 'D' and 'F' grades are highlighted in yellow.

Before you run the script

After running the script