To get work this week, we'll need the following links:
- Google Sheets Edit Event
- Google Sheets SpreadsheetApp object
- Google Sheets range.getBackground() method
- Google Sheets range.setBackgroundColor() method
- Google Sheets range.getSheet() method
The Lesson
We created a new spreadsheet and broke down the Space Man game into a list of tasks. The list includes the following:
- Draw the space man
- Choose the number of times you can be wrong (we chose 6)
- Choose a word
- Enter a letter and see if the letter is in the word
- See how long the word is / reveal the letters
- Place to enter guesses
- Know if you've won or lost
onEdit(e)
We learned that the onEdit function is a program that runs when a cell is changed on the spreadsheet. We also learned that the 'e' passed is an event object that can give us information about the sheet.
Step 1: Choosing colors for Space Man
function onEdit(e) {
var ui = SpreadsheetApp.getUi();
var c = e.range.getBackground();
ui.alert(c);
}
The program above allows you to collect colors from a cell. We used this program to collect the color values that we are going to use for our Space Man. In our case, we chose black (#000000), white (#ffffff), red (#ff0000) and green (#6aa84f).
Step 2: Draw the Space Man
We drew the Space Man on the spreadsheet.
Step 3: Make the Leg Appear and Disappear
function onEdit(e) {
var val = e.range.getValue();
if (val === 0) { e.range.getSheet().getRange("B23:D25").setBackgroundColor('#ffffff');
} else { e.range.getSheet().getRange('D23').setBackgroundColor('#000000'); e.range.getSheet().getRange('C24').setBackgroundColor('#000000'); e.range.getSheet().getRange('B25').setBackgroundColor('#000000'); }
}