I have the following script which right now works with checkboxes (onEdit).

Current Script Usage:

1. When you click the first checkbox which is located in 9th column and 2nd row of Charts Sheet, it adds an item located to Charts!B4 into Watchlist sheet.
2. When you click the second checkbox which is located in 9th column and 3rd row of Charts Sheet, it removes that item if it already exists in Watchlist sheet.

function onEdit(e) {
  if (e.range.getSheet().getName() != 'Charts') return;
  if (e.range.columnStart != 9) return;
  if (e.range.rowStart == 2 && e.value == 'TRUE') add_item(e);
  if (e.range.rowStart == 3 && e.value == 'TRUE') remove_item(e);
}

function add_item(e) {
  var [ss, item, favs_sheet, favs_range, favs, header] = get_variables(e);
  if (favs.includes(item)) { ss.toast(item   ' is already in watchlist'); return}
  
  favs.push(item);
  favs = [header, ...favs.sort()].map(x => [x]);
  favs_sheet.getRange(1,2,favs.length,favs[0].length).setValues(favs);
  ss.toast(item   ' was added to watchlist');
}

function remove_item(e) {
  var [ss, item, favs_sheet, favs_range, favs, header] = get_variables(e);
  if (!favs.includes(item)) { ss.toast(item   ' was not found among watchlist'); return }
  
  favs = [header, ...favs.filter(x => x != item).sort()].map(x => [x]);
  favs_range.clearContent();
  favs_sheet.getRange(1,2,favs.length,favs[0].length).setValues(favs);
  ss.toast(item   ' was removed from watchlist');
}

function get_variables(e) {
  e.range.uncheck();
  var ss = e.source;
  var item = e.range.getSheet().getRange('B4').getValue();
  var favs_sheet = ss.getSheetByName('Watchlist');
  var favs_range = favs_sheet.getRange('B:B');
  var favs = favs_range.getValues().flat().filter(String);
  var header = favs.shift();
  return [ss, item, favs_sheet, favs_range, favs, header]
}

What I want to achieve:

I would like to change the script in order to work with images instead of checkboxes. So for example when you click on Image1 the script will add the item to watchlist sheet. If you click on Image2 the script will remove the item from watchlist sheet if it's already exist.

Thanks

CodePudding user response：

Here you go:

function add_item() {
  var [ss, item, favs_sheet, favs_range, favs] = get_variables();
  if (favs.includes(item)) { ss.toast(item   ' is already in Favorites'); return}
  
  favs.push(item);
  favs = favs.sort().map(x => [x]);
  favs_sheet.getRange(2,1,favs.length,favs[0].length).setValues(favs);
  ss.toast(item   ' was added');
}

function remove_item() {
  var [ss, item, favs_sheet, favs_range, favs] = get_variables();
  if (!favs.includes(item)) { ss.toast(item   ' was not found in Favorites'); return }
  
  favs = favs.filter(x => x != item).sort().map(x => [x]);
  favs_range.clear();
  favs_sheet.getRange(2,1,favs.length,favs[0].length).setValues(favs);
  ss.toast(item   ' was removed');
}

function get_variables() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var item = sheet.getRange('E1').getValue();
  var favs_sheet = ss.getSheetByName('Favorites');
  var favs_range = favs_sheet.getRange('A2:A'   favs_sheet.getLastRow());
  var favs = favs_range.getValues().flat();
  return [ss, item, favs_sheet, favs_range, favs];
}

So you will have two functions add_item() and remove_item(). You can assign any of them to image or drawing.

I assigned the functions to the text buttons (drawings) but it can be done about the same way with images.

I think the code can be universal for buttons and for onEdit() at the same time. It can be done. But I think it doesn't make much sense.

CodePudding user response：

Thank you Yuri! Once again you're the goat!