Automatically detect when the user selects a new Range in Google Sheets

In this tutorial, I will show you how to automatically detect when the user selects a new Range in Google Sheets using Apps Script.

Here is an example of an application that I recently built using this concept:

The above screencast shows the game Tic Tac Toe implemented in a Google Sheets spreadsheet using Apps Script. You'll notice that the players simply click on a cell to play the game and they aren't entering anything at all. Their click selects a new range and this is automatically detected by Apps Script. The script then checks whether the play was valid or not and modifies the state of the game accordingly.

Prerequisites

This tutorial assumes that you are familiar with:

• Basics coding concepts in Apps Script such as creating and running scripts.

• Triggers in Google Sheets.

The selection change trigger

If you're familiar with programming languages JavaScript, you may have used what are known as "Handler functions". These are functions that get called whenever the user takes some specific action like pressing a key on their keyboard or clicking a button on the web page etc.

In Google Sheets, we have the concept of Triggers. A Trigger is a feature that runs a script automatically whenever the user takes a specific action or based on some time schedule. However, up until recently, there was no way to run a script whenever the user selects a different cell or range. That changed when the onSelectionChange() trigger arrived on the scene recently. It is only available as a simpler trigger.

Using this trigger is simple, all you need to do is create a function called onSelectionChange() in your script and it will get called whenever the user selects a different range in the spreadsheet.

function onSelectionChange() {
  SpreadsheetApp.getActive().toast("You selected a new range!");
}

That's it! It really is that simple. Here is a screencast of the selection change trigger in action:

You can find out which range was selected by using an event object that is automatically passed to the onSelectionChange() function. The code below using this event object to figure out whether the new selection is a single cell, a single row, a single column or multiple rows and columns?

function onSelectionChange(e) {
 var rowStart = e.range.rowStart;
 var rowEnd = e.range.rowEnd;
 var columnStart = e.range.columnStart;
 var columnEnd = e.range.columnEnd;

 if(rowStart == rowEnd && columnStart == columnEnd) {
   SpreadsheetApp.getActiveSheet().getRange(1,1).setValue("Single cell");
 } else if(rowStart == rowEnd && columnStart != columnEnd) {
   SpreadsheetApp.getActiveSheet().getRange(1,1).setValue("Single row");
 } else if(rowStart != rowEnd && columnStart == columnEnd) {
   SpreadsheetApp.getActiveSheet().getRange(1,1).setValue("Single column");
 } else if(rowStart != rowEnd && columnStart != columnEnd) {
   SpreadsheetApp.getActiveSheet().getRange(1,1).setValue("Multiple rows & columns");
 }
}

Conclusion

In this tutorial I showed you how to automatically run a script whenever the user selects a new range in Google Sheets. I also showed you how to figure out which range was selected and whether this range is a single cell, a single row, a single column or multiple rows and columns.

Hope you found this post helpful and thanks for reading!