- Object variables
- An object variable that contain arrays
- An object variable that contains a function
The full, final commented code is included below. Simply paste it into a Google Sheet script editor, make sure one of the sheets is named Main and the code will execute.
Further reading
Sometimes our participants ask for additional homework. Below are some links to help understand the code below.
- Google Event Objects: Our function is kicked off by editing the spreadsheet using the onEdit function, which runs when you edit the spreadsheet. The e is for Event. Read this page to learn more about the Edit object and what it returns, including the objects we use: range and source.
- Google Range Object: The range object can do a lot! In this case, we get the value of one cell and use that value to draw our space man. (Look for the code that says e.range.getValue();)
- Google Spreadsheet Object: The spreadsheet object is called 'source' in our code below. The sheet is the source of our event and that is why it has that name.
- JavaScript Objects: Learn more about declaring JavaScript objects, including the difference between properties (values) and methods (functions).
- JavaScript Arrays: What is an array and how do you use them? How does the array concat function work?
- JavasScript Switch Statement: Learn how to make decisions based on different conditions.
Full Code
// This function captures an edit event on the workbook. // The 'e' is for 'event'
function onEdit(e) {
// get the new value of the cell
var val = e.range.getValue();
// get the sheet that you will be changing
var sht = e.source.getSheetByName('Main');
// create a color object
var color = {
white : '#ffffff',
black : '#000000',
red : '#ff0000',
green : '#6aa84f'
};
// define the astronaut cells using an object of arrays
// this object also has a method called suit and requires
// parenthesis to call (astronaut.suit())
var astronaut = {
leftLeg : ['B25', 'C24', 'D23'],
rightLeg : ['H25', 'G24', 'F23'],
leftArm : ['B13', 'C14', 'D15'],
rightArm : ['H13', 'G14', 'F15'],
body : ['E10:E22'],
head : ['D3:D9', 'F3:F9', 'E3', 'E9'],
visor : ['E4:E8'],
suit : function() {
// return the entire suit (but not the visor)
return this.body.concat(this.leftLeg, this.rightLeg,
this.leftArm, this.rightArm,
this.head);
}
};
// create a shortcut function to set the background color
var setColor = function(rng, col) {
sht.getRangeList(rng).setBackground(col);
};
// change the sheet based on the value of the change
switch (val) {
case -1: // A win! Restore the suit and change
// the visor to green
setColor(astronaut.suit(), color.black);
setColor(astronaut.visor, color.green);
break;
case 1: // Wrong guess, remove a limb
setColor(astronaut.leftLeg, color.white);
break;
case 2: // Wrong guess, remove a limb
setColor(astronaut.rightLeg, color.white);
break;
case 3: // Wrong guess, remove a limb
setColor(astronaut.leftArm, color.white);
break;
case 4: // Wrong guess, remove a limb
setColor(astronaut.rightArm, color.white);
break;
case 5: // Wrong guess, remove a limb
setColor(astronaut.body, color.white);
break;
case 6: // Wrong guess, remove a limb
setColor(astronaut.head, color.white);
break;
case 7: // Wrong guess, clear the visor
setColor(astronaut.visor, color.white);
break;
default:
// Reset the suit and visor
setColor(astronaut.suit(), color.black);
setColor(astronaut.visor, color.red);
};
}