onSelectionChange re-triggers itself when changing tab

Question

I've been trying to get a simple function to work for a couple of days but I can't see where I'm going wrong. I would really appreciate some help is possible please.

Background: I have a calendar tab with a table containing Stores down the side and Dates across the top.

When the user clicks between E7:AI200 I want to be able to:

Grab the Store Name from column 1 and the clicked row and populate B1 with the value.
Grab the Date from row 1 and the clicked column and populate C1 with the value.

This seems to work fine.

I also want the spreadsheet to automatically navigate to another tab called 'Filtered Events' when the cell between E7:AI200 clicked.

This also seems to work fine.

Issue: When I click back to the Calendar tab it seems to re-click the original cell and navigates me straight back to the 'Filtered Events' tab and I am stuck in a loop of continously going between tabs each time I try to get back to the Calendar.

function onSelectionChange(e) {

  var ss = SpreadsheetApp.getActive();
  if (ss.getActiveSheet().getName() == "Calendar") {
    var sheet = ss.getSheetByName("Calendar");
    
    var full_range = sheet.getRange("A1:AI200");
    var click_range = e.range;

    var row_lookup = click_range.getRow();
    var col_lookup = click_range.getColumn();

    var selected_store = full_range.getCell(row_lookup, 1).getValue();
    var selected_date = full_range.getCell(1, col_lookup).getValue();

    var populate_store = sheet.getRange("B1"); 
    var populate_date = sheet.getRange("C1");

    if (row_lookup > 6 && row_lookup < 250 && col_lookup < 36 && col_lookup > 4) { 

      populate_store.setValue(selected_store);
      populate_date.setValue(selected_date);

      var change_sheet = ss.getSheetByName("Filtered Events");
      change_sheet.activate();
    } 

    else {
      populate_store.setValue('');
      populate_date.setValue('');
    } 
  }
}

user_14265469 · Answer

Probably you can use PropertiesService to store the last used coordinates of selected cell and check them at the start of the script:

function onSelectionChange(e) {

  var ss = SpreadsheetApp.getActive();
  if (ss.getActiveSheet().getName() == "Calendar") {
    var sheet = ss.getSheetByName("Calendar");
    
    var full_range = sheet.getRange("A1:AI200");
    var click_range = e.range;

    var row_lookup = click_range.getRow();
    var col_lookup = click_range.getColumn();

    // try to get the last used cell coordinates
    var last_cell_row = PropertiesService.getScriptProperties().getProperty('last_cell_row');
    var last_cell_col = PropertiesService.getScriptProperties().getProperty('last_cell_col');

    // save current cell coordinates
    PropertiesService.getScriptProperties().setProperty('last_cell_row', row_lookup);
    PropertiesService.getScriptProperties().setProperty('last_cell_col', col_lookup);

    // if the coordinates are the same break the function
    if (row_lookup == last_cell_row && col_lookup == last_cell_col) return;

    // the rest of your code ...

I haven't tested it. It would be better if you provide some dummy data for testing, how your sheets look like.

onSelectionChange re-triggers itself when changing tab

1 Answers