Building the Game

Over the last two sessions, we built out the game functionality by programming two important parts of the game:

• Returning a random word from a list of words
• Defining a target area on the sheet to enter guesses

Further Reading

If you would like to know more about the concepts we covered, please read below:

• Google SpreadsheetApp Object: The program that allows us to access Google sheets.
• Google Sheets Range Object: Access and modify spreadsheet ranges.
• JavaScript Math Object: The JavaScript Math object allows you to perform mathematical tasks on numbers.
• Math.Random Function: This function returns a random number between 0 (inclusive) and 1 (exclusive).
• Math.Floor Function: This function returns a value rounded down to the nearest integer.
• JavaScript Comparison and Logical Operators: Use boolean operators in your code to control program flow: Equal to, not equal to, greater than, less than, AND, OR and conditional statement.
• JavaScript Booleans: Learn more about the role of True/False in programming.
• JavaScript If/Then: Control the program flow by making a choice based on a boolean.

Full Code

Get a random word

---

In order to use the following code, we need to paste a list of words into the "Words" sheet. The list must start in cell A1, but can be as wide or long as you want.

// This function gets a random word from the "Words" sheet
function getWord() {

  
  // retrieve the "Words" sheet

  var sht =

    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Words");
  // get the data from the sheet

  var rng = sht.getDataRange();

  // get the number of rows and columns

  var cols = rng.getNumColumns();

  var rows = rng.getNumRows();

  
  //if you want to see the coordinate of the word, uncomment below
  //var win = SpreadsheetApp.getUi();
  //win.prompt("Row: " + rows + "/Column: " + cols);

  
  // select a word from a random row and column

  var word = rng.getCell(Math.floor(Math.random() * rows) + 1, 

    Math.floor(Math.random() * cols) + 1).getValue();

  
  // return the word to the calling function

  return word;

}


Is a cell inside a named range?

---

Add a named range called "Guess Range" to the target guessing area in your sheet.

We call the editInRange from within the onEdit function.

function onEdit(e) {
...
  //if the cell is within the range, continue 
  //else exit this function. The edit range is passed
  //as the cell and the GuessRange is the named range

  if(!editInRange(e.range, 'GuessRange')) { return; }
...
}

//This function takes a cell and a named range and returns 
//true if the cell is contained in the range, false if not
function editInRange(eCell, targetName) {

  //eCell: the "edited" cell, passed by the edit event
  //targetName: the named range in the test below

  //Get the named range
  var targetRange =
     SpreadsheetApp.getActiveSheet().getRange(targetName);
  
  //Create an object to get the address of the cell
  var cell = {
    row : eCell.getRow(),
    col : eCell.getColumn()
  };
  
  //Create an object to get the top left and bottom right
  //addresses of the named range
  var target = {
    firstRow: targetRange.getRow(),
    lastRow: targetRange.getLastRow(),
    firstCol: targetRange.getColumn(),
    lastCol: targetRange.getLastColumn()
  };
  
  //returns a boolean (true/false) if the cell address is inside
  // the target
  var test = cell.row >= target.firstRow 
      && cell.row <= target.lastRow
      && cell.col >= target.firstCol
      && cell.col <= target.lastCol;
  
  //If you want to see the value, uncomment the line below
  //SpreadsheetApp.getUi().alert(test ? "True" : "False");

  //Return the boolean value (true/false) to the calling function
  return test;
}