Crossword puzzles with Google Sheets
Introduction
In previous lessons, we learned how to create Word Search and Word Jumble puzzles. The links to those lessons are available below.
Word search puzzles: https://digitalmaestro.org/articles/word-search-puzzles-with-google-docs
Word jumble puzzles: https://digitalmaestro.org/articles/word-jumbles-with-google-sheets-and-docs
My students enjoyed word searches and word jumbles. Crossword puzzles too. These are all fun activities that engage students with the vocabulary. I gave them these assignments for homework or as filler activities.
This lesson focuses on the creation of crossword puzzle exercises. We are using Google Sheets to create and distribute the crossword puzzle.
I will show you how to create a template so you can easily create more puzzles and distribute them quickly.
Use the link below to get a copy of the crossword puzzle template.
Template overview
I have done most of the work for you so we can focus on constructing the crossword puzzle.
The grid is where we construct the crossword puzzle.
The answers table is where students fill in the puzzle answers.
The clues table is for students that need help. The clues are optional. The clue words are here for reference while constructing the puzzle.
Code assistance
To construct the puzzle we need a formula with several parameters. The formula is available below. Let me explain what the formula does.
Three functions are part of the formula. I will begin with the inter most function and work outward.
The REGEXREPLACE function takes the word entered by students and adds a comma between each letter. This comma is used by the next function. The SPLIT function looks for the comma between each letter. It uses the comma to separate each letter and place it on a different cell.
The IFERROR function corrects a warning. The other functions report a warning when there is nothing to replace or split. The formula will have nothing to spit until students enter a word. The IFERROR function hides the error message.
The formula needs a word to split. The location of the word is referenced in a cell. The formula is looking in cell S4. It's right after the ampersand(&) in the formula. This cell reference is updated for each word.
I am NOT going to make you type this formula for each word in the puzzle. I have some helpful code to help with that process.
=iferror(SPLIT(REGEXREPLACE("" & S4,"(\w)", "$1,"), ","),"")
Helpful code
The template includes code to help construct the puzzle.
Go to the menu and click Tools. Select the Script editor option.
A tab opens to the right of the sheet. The tab contains code. I have developed this code to make the process easier. The code is not working now because it has been commented out. The two forward slashes(//) at the beginning of each line are comment characters. The slashes tell Google Apps Script not to run the code.
Comment code is used to provide information about code. The first lines of the code are comments from me about the code below.
We need to enable the code. Select the code from line 8 to line 25.
Go to the menu and click Edit. Select the Toggle comment option.
The code is uncommented. The code has words of different colors. This indicates that the editor recognizes the words as code.
Click the Save button.
We can’t run the code yet. We need to give the code permission to make changes to the spreadsheet.
Click the function selector. Choose the first function. This function loads a Crossword menu when the spreadsheet opens. I created this menu to make things easier.
Click the Run button.
An alert box opens. Click the Review permissions button.
You are prompted to select the Google account for the spreadsheet. Click on your account.
A message opens to warn us that the app isn’t verified. Verified apps are available through the Google Apps store. The script here isn’t verified through Google. This is why you are getting this message.
I go through the code in more detail at the bottom of the page.
I demonstrated the code and what it does. You can verify that it does what I outlined. As long as you are using the template from the link on my site. The code is not meant to cause any harm. The code is available at the bottom of this document. Please verify it before proceeding with the next step.
Click the Advanced link.
Click the link that reads "Go to puzzle(unsafe)".
An authorization page opens with information about the script access. The top portion identifies what the code might be able to do. The puzzle code will edit the contents of this spreadsheet only.
It will NOT edit, create, or delete any other spreadsheets on your Google Drive.
Click the Allow button.
The authorization page closes. You are returned to the Google Sheets crossword puzzle template. The Crossword menu option is added. It appears next to the Help menu option.
Click the Crossword menu. The menu has two options. They are Across and Down. Don’t select any of the menu options yet.
Type the word Saturn in the answers table. This will be number 1-across.
Click once on cell D4. It can be any cell you want. As long as there is enough room for the letters of the word to go across or down.
Go to the Crossword menu. Select the Across option.
Each function in the script needs to be verified before it is allowed to make changes to the current sheet once. You only need to verify each once. Click the continue button. Follow the same steps from earlier to authorize the script.
The script needed your authorization before it did anything on the sheet. The script doesn’t run the first time. It runs only after you provide authorization.
Go back to the Crossword menu and select Across.
The word Saturn appears in the grid. The letter S begins on the cell we selected. The rest of the letters appear to the right. One letter in each cell.
The code from the script appears in the Formula bar.
The cell reference in the formula is orange and points to cell S4. Cell S4 contains the word Saturn.
Type the word Venus in the table for 1 Down.
Go to cell E6. Click once a cell below the word Saturn. Go to the Crossword menu and select Down.
The first two are easy. I set them up to be easy. I set the cell reference for S4 and U4 by default. The rest aren’t much harder.
Type the word Jupiter going across for number 2.
Click on an empty cell in the grid. Make sure there is enough space for the word to fit within the grid going across.
Go to the Crossword menu and select Across. The word Saturn appears in the puzzle. The formula automatically points to cell S4.
Go to the Formula bar. Change the reference from S4 to S5. Press the Return key or click outside the Formula bar.
The word updates to match the word for 2 across.
One more together. Type Earth for 2 Down. Click on cell C5. Use the Crossword menu and select Down. Change the cell reference in the Formula bar. The cell reference is U5.
Adding more words is just this simple.
Review
Before we go on I would like to take a step back. I want to preview how the puzzle will eventually work. Erase the words in the Answers table.
The words in the puzzle grid are removed automatically.
Type the word Saturn in the Answer table for 1 Across. Press the Return key or click on another cell. The word Saturn appears in the puzzle grid.
Type Jupiter in 2 Across. Type the wordS for Down into the table. Those words appear in the puzzle too.
Fill out the puzzle with the rest of the words.
The Clues
Crossword puzzles need clues. They also need numbers. Click on the letter S for Saturn.
Go to the menu and click Insert.
Select the Note option. The Note option has a Shortcut key option. The combination is Shift+F2. I recommend using this combination. It helps things go faster.
A note box opens next to the cell.
Type “1 Across: This planet has rings. It is the farthest planet that can be seen with the naked eye.”
Click on the cell for the letter E of Earth. Insert a note and type the information below.
2 Down: This is our home planet. The Big Blue marble.
Go to the letter V for Venus. Enter this information in a note.
1 Down: This is the second planet from the sun. It is the hottest planet in the solar system.
Use this note for Jupiter.
2 Across: This is the largest planet in the solar system. Come up with your own clues for the rest of the planets. I have my clues available below if you want to use them.
Preview so far
Erase the words in the table. Move your mouse over a cell with the black triangle.
Enter the word for each clue in the Answer Table.
Use the rest of the words. Place them in the answer box. I only formatted the first three rows. I didn’t want you to think the puzzle entries had to be uniform. There are more words across than down.
Select the numbers and words for across and down.
Click the Border format tool. Click the color picker and choose dark gray 1.
Click the all borders option.
Visuals
The puzzle by itself is plain. Regular crossword puzzles outline where the words go and how many letters are needed. That’s what we’ll do next.
Select the cells for the letters in the word Saturn.
Go to the menu and click Format. Select the Conditional formatting option.
The conditional formatting panel opens on the right. The formatting will be applied to the selected range. The formatting is applied if the cells are not empty. That is the condition that must be met. The selected cells are not empty. The cells for the word Saturn are green. That is the default cell fill condition.
The color format is set in the formatting style section.
Click the color background palette tool. Select the light green color. This is the color I prefer. Chose one that you want to use. Use light colors so the text is easy to read.
This isn’t the only range of cells we want to format. We want all the cells with words to change to green when students enter the answer. Click the range selection icon.
Click the Add another range button.
The range input box is ready for us to enter a range. We can enter the range manually or use the mouse to select the range. Using the mouse is much easier.
Select the cells in the word Earth.
The range information is added to the input field.
Click the Add another range button. Add another set of cells to the range. Repeat this process until all the words are selected. Click the OK button to finish selecting the ranges. The ranges are combined and listed in the range field.
The formatting is applied to the selected cells. This is a preview before we save the conditional formatting rule.
Click the Done button to save the conditional formatting rule.
We want to apply formatting to the cells for words that are empty. This shows where the words go, the orientation, and the number of spaces for the word.
There is another way to select the cells for conditional formatting. Begin by selecting one word.
The next step requires the use of a modifier key. Some people like to use modifier keys, others don't. Chrome and Windows users hold the Alt key down. Mac users hold the Command key down. Select the next range of cells while holding the modifier key.
I selected Saturn and Jupiter using the modifier key. Both sets of cells are selected. Keep holding the modifier key and select the other cells.
The selected cells appear in a darker shade of green. This is due to the combination of the blue selection color and the green cell color.
Go to the conditional formatting rules panel. Click the Add another rule button.
The selected cells are added to the range list. We need to change the condition for the rule.
Click the condition selector. Select the condition — for when the cell is empty.
Click the cell color palette tool. Choose a light yellow. Click the Done button.
We have two rules that apply to the same group of cells. One for when the cells are filled. One for when the cells are empty.
I like to include one more color condition option. Let’s look at an example. The answer for 2 down is Earth. What if a student answers Mars instead of Earth. A yellow cell points out that a letter is missing.
What if the student enters a longer word like Mercury. The letters extend outside the colored grid. The next conditional formatting will alert students to the mistake.
Select all the cells in the grid. Include the ones we already color-coded.
Add another rule. Set the conditional color to red. Leave the formatting rule set at “is not empty”. Click the Done button.
The letters in Mercury that extend beyond the cell range are highlighted with red.
Conditional formatting works in layers. The condition for green filled in cells is before the condition for red filled in cells. This is why the cells for the letters in the word are not red.
We are done with the conditional formatting panel. Go ahead and close it.
Student distribution
This is your teacher's master. We need a copy for the students. Click File in the menu and select Make a copy.
Use “Solar System Crossword Puzzle - Student” for the file name. Click the OK button.
Erase the answers in the answer table. Distribute the puzzle to students.
One more thing
I recommend adding instructions above the puzzle. A helpful reminder to students for where the answers are placed.
Puzzle script
The first five lines provide information about the code. The code uses functions to execute instructions. The first function is called onOpen. The instructions in the function attach the Crossword menu to the spreadsheet each time the spreadsheet opens. It also adds the Across and Down menu items.
Each menu item refers to the functions across and down. The first Across is the menu name. The across in lower case references the across function. The same is true for the down menu item.
The across function gets the current spreadsheet and the current cell. It sets the formula inside the cell. The formula is within the open and close parenthesis. The same instructions are used for the down function. The formula for the down function includes the transpose function. It changes the letters from going across to going down.
// The code below is used to enter the long formula into cells.// One formula is for words that go across.// The other formula is for words that go down.// The code creates a menu option.// The menu options include across and down.function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Crossword') .addItem('Across', 'across') .addSeparator() .addItem('Down', 'down') .addToUi();}function across() { var spreadsheet = SpreadsheetApp.getActive();spreadsheet.getCurrentCell().setFormula('=iferror(SPLIT(REGEXREPLACE("" & S4,"(\w)", "$1,"), ","),"")');};function down() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getCurrentCell().setFormula('=iferror(TRANSPOSE(SPLIT(REGEXREPLACE("" & U4,"(\w)", "$1,"), ",")),"")');}