In this session I will cover the basics of self-graded questions on Google Forms, easily grading free response questions on sheets, conditional formatting grading, and some super simple coding to get Google Sheets to count the number of colored cells. Everything I share in this session will have a corresponding explainer video posted on this website dedicated to teaching with technology.
Make your Form Grade Itself
|
How to make a form and have it grade itself. This is mostly used with multiple choice questions but it could also be used with some short answer questions.
|
Grading Form Results by question and providing feedback
This may not be the fastest way to grade a form but it does provide the students with valuable feed back and is faster than doing the same work on a worksheet.
|
|
Grading Form Results in Google Sheets - Intermediate Level
|
In this video I explain how to use conditional formatting in Google sheets to grade responses from a google form. What this does is mark all of the answers that are incorrect as red. I then just count up all of the red squares and calculate the students grade.
|
Grading Form Results in Google Sheets - Expert Level
In this video I explain how to quickly create an add-on that counts all of the squares marked red by the conditional formatting. This saves time because first all of the wrong answers were marked red then the add-on quickly counts all of them. This is like automatically counting up all of the red x's on a worksheet.
|
|
Code for "count colored cells" Add-on
/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/
function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();
var rangeA1Notation = formula.match(/\((.*)\,/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();
var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();
var count = 0;
for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
count=count+1;
return count;
}
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/
function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();
var rangeA1Notation = formula.match(/\((.*)\,/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();
var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();
var count = 0;
for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
count=count+1;
return count;
}