Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script
onSelectionChange has been released at April 22, 2020. But this couldn't be used at the released day. But now, I could confirm that this got to be able to be used. So in order to test this event trigger, I prepared a simple sample script. This is a sample script for detecting the change tab on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script.
- Please copy and paste the following script to the container-bound script of Google Spreadsheet, and save the script.
- Please reopen the Google Spreadsheet.
- By this,
onOpenis run and the current sheet is put to PropertiesService. - Unfortunately, in the current stage, it seems that the event object of
onSelectionChangehas no information about the change of tab. So in order to detect the change of tab, I used the PropertiesService.
- By this,
- Then, please select a cell and cells on sheet.
- By this,
onSelectionChangeis run by the onSelectionChange event trigger, and put the A1Notation to the cell. - When the active tab is moved, the sample script detects this, and the information of the changed tab is put to the cell.
- By this,
function onOpen(e) {
const prop = PropertiesService.getScriptProperties();
const sheetName = e.range.getSheet().getSheetName();
prop.setProperty("previousSheet", sheetName);
}
function onSelectionChange(e) {
const prop = PropertiesService.getScriptProperties();
const previousSheet = prop.getProperty("previousSheet");
const range = e.range;
const a1Notation = range.getA1Notation();
const sheetName = range.getSheet().getSheetName();
if (sheetName != previousSheet) {
range.setValue(`Changed tab from ${previousSheet} to ${sheetName}. ${a1Notation}`);
// When the tab is changed, this script is run.
} else {
range.setValue(a1Notation);
}
prop.setProperty("previousSheet", sheetName);
}