Abstract
This report demonstrates the onSelectionChange simple trigger in Google Sheets by creating a slide puzzle. Selecting cells triggers script execution, enabling interactive gameplay.
Introduction
At Google Sheets, simple triggers can be used. When simple triggers are used, users can automatically run Google Apps Script based on actions in the spreadsheet. Among the simple triggers is onSelectionChange. This can automatically run a script when a cell is selected. In this report, as a simple sample script demonstrating the use of onSelectionChange, I created a slide puzzle in Google Sheets. The goal of the slide puzzle is to align the numbers by sliding them. Here, onSelectionChange is used to detect the selected number and slide it. The aim of this report is to learn about the simple trigger onSelectionChange.
Usage
Please do the following flow.
1. Prepare sample spreadsheet
Please create a standalone script in the Google Apps Script editor. Copy and paste the following script into the editor, and run the function createSampleSpreadsheet. This will create a sample spreadsheet for testing the slide puzzle in the root folder of your Google Drive.
Then, please open the created spreadsheet. You will see a spreadsheet identical to the above demonstration.
/**
* ### Description
* Create a sample Spreadsheet to the root folder.
*/
function createSampleSpreadsheet() {
const spreadsheetName = "sample spreadsheet";
const ss = SpreadsheetApp.create(spreadsheetName, 10, 8);
const sheet = ss.getSheets()[0].setName("Sheet1");
const values = [
["Slide puzzle", null],
["START GAME", null],
["Puzzle range", "C5:G9"],
["Status", null],
];
sheet.getRange("A1:B4").setValues(values);
sheet.getRange("B2").insertCheckboxes();
sheet.getRange("A1").setFontSize(30).setFontColor("#ffffff");
sheet.getRange("A2:B4").setBackground("#fce5cd");
sheet.getRange("A1:H1").merge().setBackground("#1155cc");
sheet
.getRange("C5:G9")
.setFontSize(30)
.setHorizontalAlignment("center")
.setVerticalAlignment("middle")
.setBackground("#efefef")
.setBorder(
true,
true,
true,
true,
true,
true,
"#000000",
SpreadsheetApp.BorderStyle.SOLID
);
sheet.setColumnWidths(3, 6, 55).setRowHeights(5, 6, 55);
}
2. Main script
Please open the script editor of the created spreadsheet, and copy and paste the following script to the script editor, and save the script.
/**
* Slide Puzzle in Google Sheets
* Author: Kanshi Tanaike
*/
/**
* ### Description
* Convert Class Range object to Gridrange as an array.
*
* @param {SpreadsheetApp.Range} r Range.
* @return {Array} Gridrange.
*/
function getRowsCols_(r) {
const startRow = r.getRow();
const endRow = r.getNumRows() + startRow;
const startCol = r.getColumn();
const endCol = r.getNumColumns() + startCol;
return [startRow, endRow, startCol, endCol];
}
/**
* ### Description
* Shuffle range by creating the number values.
*
* @param {SpreadsheetApp.Range} range Range.
* @return {void}
*/
function shuffle_(range) {
const rows = range.getNumRows();
const cols = range.getNumColumns();
const array = [...Array(rows * cols)].map((_, i, a) =>
a.length - 1 == i ? "" : i + 1
);
array.sort(() => Math.random() - 0.5); // Ref: https://stackoverflow.com/q/2450954
const values = [...Array(Math.ceil(array.length / cols))].map((_) =>
array.splice(0, cols)
); // Ref: https://github.com/tanaikech/UtlApp?tab=readme-ov-file#splitarray
range.setValues(values);
}
/**
* ### Description
* Simple trigger of OnEdit.
* Please directly run this function by the script editor.
*
* @param {Object} e Event object of the simple trigger of OnEdit.
* @return {void}
*/
function onEdit(e) {
const { range } = e;
const sheet = range.getSheet();
const p = PropertiesService.getScriptProperties();
const a1Notation = range.getA1Notation();
if (a1Notation == "B2") {
const puzzleRange = sheet.getRange("B3").getDisplayValue();
let status = null;
if (range.isChecked()) {
p.setProperty("status", "start");
status = "start";
shuffle_(sheet.getRange(puzzleRange));
} else {
p.deleteProperty("status");
}
sheet.getRange("B4").setValue(status);
p.setProperty("count", "0");
p.setProperty("puzzleRange", puzzleRange);
}
}
/**
* ### Description
* Simple trigger of OnSelectionChange.
* Please directly run this function by the script editor.
*
* @param {Object} e Event object of the simple trigger of OnSelectionChange.
* @return {void}
*/
function onSelectionChange(e) {
const { source } = e;
const p = PropertiesService.getScriptProperties();
const { status, count, puzzleRange, previousRange } = p.getProperties();
if (status != "start") return;
if (!puzzleRange) {
Browser.msgBox("Please set the puzzle range to the cell B4.");
return;
}
const sheet = source.getActiveSheet();
const activeRange = sheet.getActiveRange();
const statusRange = sheet.getRange("B4");
const puzzleRangeObj = sheet.getRange(puzzleRange);
const [crStartRow, crEndRow, crStartCol, crEndCol] =
getRowsCols_(activeRange);
const [pzrStartRow, pzrEndRow, pzrStartCol, pzrEndCol] =
getRowsCols_(puzzleRangeObj);
statusRange.setValue("Running...");
if (
crStartRow < pzrStartRow ||
crEndRow > pzrEndRow ||
crStartCol < pzrStartCol ||
crEndCol > pzrEndCol
) {
p.deleteProperty("previousRange");
statusRange.setValue("Out of range");
SpreadsheetApp.flush();
Utilities.sleep(2000);
} else {
if (previousRange && activeRange.isBlank()) {
const previousRangeObj = sheet.getRange(previousRange);
const [prStartRow, , prStartCol] = getRowsCols_(previousRangeObj);
const check = [
crStartRow - 1 == prStartRow && crStartCol == prStartCol,
crStartRow + 1 == prStartRow && crStartCol == prStartCol,
crStartRow == prStartRow && crStartCol - 1 == prStartCol,
crStartRow == prStartRow && crStartCol + 1 == prStartCol,
].some((k) => k);
if (check) {
const c = Number(count) + 1;
previousRangeObj.copyTo(activeRange);
previousRangeObj.clearContent();
const puzzleRangeValues = puzzleRangeObj.getValues().flat().join(",");
const goal = [
...Array(
puzzleRangeObj.getNumRows() * puzzleRangeObj.getNumColumns()
),
]
.map((_, i, a) => (a.length - 1 == i ? "" : i + 1))
.join(",");
if (puzzleRangeValues == goal) {
p.deleteProperty("status");
p.setProperty("count", "0");
sheet.getRange("B2").uncheck();
statusRange.setValue(null);
Browser.msgBox(`Finished. Total movements were ${c}.`);
return;
} else {
p.setProperty("count", c);
}
}
}
}
p.setProperty("previousRange", activeRange.getA1Notation());
statusRange.setValue("Next");
}
3. Reopen spreadsheet
To correctly use the simple triggers, please close the spreadsheet and reopen it.
4. Testing
First, please see the demonstration video at the top of this post. When you check the checkbox in “B2”, numbers are randomly placed into the cells “C5:G9”. When you click each cell, you can arrange the numbers by sliding them. When the numbers are lined up in order, the puzzle is finished.
When you change the range in cell “B3” and check the checkbox “B2”, a new puzzle is created and you can play it.