Making a Word Game w/ Google Sheets and Apps Script

There's a Word Game that's gone viral and tons of clones are coming out for it - even ones using Google Sheets! But... they're only using formulas instead of leveraging the power of Apps Script. In this video, we're going to build a Word Game in Google Sheets using a custom sidebar as our interface that lets players interact with a Game Board through the Sidebar.

Make sure to watch the video to follow along with the code:

There are 6 files in total that make up this project's final codebase:

gameInitializer.gs:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Games')
    .addItem('Word Game', 'gameSidebar')
    .addToUi();
}

function gameSidebar() {
  const html = HtmlService.createTemplateFromFile('wordGame').evaluate();
  html.setTitle('Word Game for Sheets');
  SpreadsheetApp.getUi().showSidebar(html);
}

function include(filename){
  return HtmlService.createHtmlOutputFromFile(filename)
    .getContent();
}

gameSetup.gs:

function createGameCanvas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.insertSheet();
  removeActiveGames()
  sheet.setName(`GameSheet`);
  sheet.deleteColumns(5,sheet.getMaxColumns() - 5)
  sheet.deleteRows(5,sheet.getMaxRows() - 6)
  sheet.setColumnWidths(1,5,50)
  sheet.setRowHeights(1,6,50)
  sheet.getRange(1,1,sheet.getMaxRows(), sheet.getMaxColumns()).setFontSize(28);
  const gameTable = sheet.getRange(1,1,sheet.getMaxRows(), sheet.getMaxColumns())
  const columnAlignments = setGameTableAlignments('vertical', 6,5)
  const rowAlignments = setGameTableAlignments('horizontal', 6,5)
  gameTable.setVerticalAlignments(columnAlignments)
  gameTable.setHorizontalAlignments(rowAlignments)
  return {
    loadingState: '',
    formContent: `<p>
        Enter a 5 letter word: <input name="word" id="userInput" type="text" />
      </p>
      <input id="submitButton" type="submit" value="Submit" />`
  }
}

function setGameTableAlignments(type, rowCount, columnCount) {
  let alignment;
  if(type == 'vertical') {
    alignment = 'middle'
  } else if (type == 'horizontal') {
    alignment = 'center'
  }
  const alignments = []
  for(let rows = 1; rows <= rowCount; rows++) {
    let row = []
    for(let columns = 1; columns <= columnCount; columns++) {
      row.push(alignment)
    }
    alignments.push(row);
  }
  return alignments
}

function removeActiveGames() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  if(ss.getSheetByName('GameSheet') !== null) {
    ss.deleteSheet(ss.getSheetByName('GameSheet'))
  }
}

function getWordOfTheDay() {
  const ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID);
  const sheet = ss.getSheetByName('YOUR_SHEET_NAME')
  
  //assumes your WordOfTheDay is located in cell A2
  const wordOfTheDay = sheet.getRange(2,1,1,1).getValue(); 
  
  return wordOfTheDay
}

gameHandler.gs:

function getUserInput(formObject) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('GameSheet');
  const wordOfTheDay = getWordOfTheDay();
  const input = formObject.word;
  const validation = validateUserInput(input)
  if(validation.status == 'ok') {
    importUserInput(sheet, input)
    evaluateUserInput(sheet, wordOfTheDay)
    validation.message = evaluateWinner(sheet, wordOfTheDay)
  }
  return validation
}

function validateUserInput(word) {
  let result = {}
  if(word.length == 5) {
    result.message = `You entered ${word}`,
    result.status = 'ok'
  } else {
    result.message = 'Error, please enter a 5 letter word',
    result.status = 'error'
  }
  return result
}

function importUserInput(sheet, value) {
  const range = sheet.getRange(sheet.getLastRow()+1,1,1,5);
  const wordArray = value.split('')
  range.setValues([wordArray])
}

function evaluateUserInput(sheet, wordOfTheDay) {
  wordOfTheDay = wordOfTheDay.split('');
  const latestEntry = sheet.getRange(sheet.getLastRow(),1,1,5)
  const userWord = latestEntry.getValues().flat();
  const colors = {
    match: '#008000',
    partialMatch: '#ffd966',
    noMatch: '#c2c2c2'
  }
  const evaluationArray = [['', '', '', '', '']]
  for(let position in userWord) {
    let matches = wordOfTheDay.filter(i => i == userWord[position])
    let matchIndex = wordOfTheDay.indexOf(matches[0])
    if(position == matchIndex) {
      wordOfTheDay.splice(matchIndex,1,'-')
      evaluationArray[0][position] = colors.match
    } else if (matches.length > 0) {
      wordOfTheDay.splice(matchIndex,1,'-')
      evaluationArray[0][position] = colors.partialMatch
    } else {
      evaluationArray[0][position] = colors.noMatch
    }
  }
  latestEntry.setBackgrounds(evaluationArray)
}

function evaluateWinner(sheet, wordOfTheDay) {
  const latestEntry = sheet.getRange(sheet.getLastRow(),1,1,5).getValues().join('').toString()
  let message
  if(latestEntry == wordOfTheDay.split('')) {
    message = 'WINNER'
  } else if (sheet.getLastRow() == sheet.getMaxRows()) {
    message = 'No more tries left - come back tomorrow!'
  } else {
    let triesLeft = sheet.getMaxRows() - sheet.getLastRow()
    message = `TRY AGAIN! You still have ${triesLeft} tries left`
  }
  return message
}

wordGame.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('style') ?>
  </head>
  <body>
    <table id="gameInitializer">
      <tr>
        <td>
          <input id="startNewGame" type="button" value="Start New Game" onclick="createNewGame()" />
        </td>
      </tr>
      <tr>
        <td id="gameCreateLoadingState">
        </td>
      </tr>
    </table>
    <form id="userInputForm" onsubmit="handlerFormSubmit(this)">
    </form>
    <p id="feedback"></p>
  </body>
  <?!= include('script') ?>
</html>

style.html:

<style>
  #gameInitializer tr td {
    padding: 5px 50%;
  }

  #userInputForm {
    padding: 5px 5px;
  }

  #startNewGame {
    padding: 4px 12px;
    border-radius: 8px;
  }

  #submitButton {
    padding: 4px 12px;
    border-radius: 8px;
  }

  #feedback {
    padding-top: 20px;
  }
</style>

script.html:

<script>
  function handlerFormSubmit(formObject) {
    google.script.run
      .withSuccessHandler(provideUserFeedback)
      .getUserInput(formObject)
  }
  function provideUserFeedback(value) {
    document.getElementById('feedback').innerHTML = value.message;
    document.getElementById('userInput').value = '';
    if(value.message == 'WINNER' || value.message == 'No more tries left - come back tomorrow!') {
      document.getElementById("submitButton").disabled = true;
    }
  }

  function createNewGame() {
    gameCreateLoadingState({
      loadingState: 'Loading...',
      formContent: ''
    })
    document.getElementById('userInputForm').innerHTML = '';
    google.script.run
      .withSuccessHandler(gameCreateLoadingState)
      .createGameCanvas();
  }
  function gameCreateLoadingState(value) {
    document.getElementById('gameCreateLoadingState').innerHTML = value.loadingState;
    document.getElementById('userInputForm').innerHTML = value.formContent;
    document.getElementById('feedback').innerHTML = '';
  }
</script>

🔔 Subscribe for more videos just like this: https://www.youtube.com/c/BootstrappingTools

🎉Are you interested in a Bootstrapping Tools Community? Let me know! https://forms.gle/4tzvffHiRqS7pVZY6 🎉

Making a Word Game w/ Google Sheets and Apps Script

Tags

You might also like...