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('');
}
}
}
CodePudding user response:
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.