Change a script from onEdit (checkbox) to image assignment

Question

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

Current Script Usage:

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.
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

Then what is your question? 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. — TheWizEd
– TheWizEd, Commented May 3, 2022 at 18:02
Yes I want to convert the script you see above, to work with buttons instead of checkboxes. — MrFrank
– MrFrank, Commented May 3, 2022 at 18:08
@TheWizEd no, there is no any onClick event that one could to assign to a checkbox or image in the same way. The onEdit() trigger works with checkboxes. Image calls a function. It works quite different and the code needs some not obvious tweaks. — Yuri Khristich
– Yuri Khristich, Commented May 3, 2022 at 18:17

Yuri Khristich · Accepted Answer · 2022-06-08 23:03:10Z

2

Here you go:

function add_item() {
  var [ss, item, favs_sheet, favs_range, favs] = get_variables();

  // make the condition case insensitive:
  var item_lower = item.toLowerCase();
  var favs_lower = favs.map(x => x.toLowerCase());
  if (favs_lower.includes(item_lower)) { 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();

  // make the condition case insensitive:
  var item_lower = item.toLowerCase();
  var favs_lower = favs.map(x => x.toLowerCase());
  if (!favs_lower.includes(item_lower)) { ss.toast(item + ' was not found in Favorites'); return }
  
  favs = favs.filter(x => x.toLowerCase() != item_lower).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.

Here is my sheet.

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.

edited Jun 8, 2022 at 23:03

answered May 3, 2022 at 18:14

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

MrFrank Over a year ago

Hey again, I found a bug on the script. It's not case sensitive, so for example if Item1 is added on the watchlist and you try to add item1 it can not detect it because "i" is not capitalized. Any workaround? Thanks

Yuri Khristich Over a year ago

Hi, I just added the four new lines to make the conditions case insensitive. But I haven't tried it. Let me know if it doesn't work.

MrFrank Over a year ago

Just tried that, it removes everything from favorites sheet and doesn't add the new item.

Yuri Khristich Over a year ago

Try my sheet by the link in the answer. I just tried it and the buttons work fine. They add and remove the items as intended and ignore case. Probably your file has something that breaks my code.

MrFrank Over a year ago

Okay I will try and see what's going on. In the meantime can you check this? stackoverflow.com/posts/comments/128165094?noredirect=1 Thanks

|

Collectives™ on Stack Overflow

Change a script from onEdit (checkbox) to image assignment

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related