Copy and paste a value if checkbox true-CodePudding

I was wondering if there is a script or formula to do the following. Basically the whole idea is to make an add to favorites checkbox and a delete from favorites checkbox, so a user will be able to add his favorite products from the product list sheet into a favorites list sheet and monitor only those.

Sample Sheet: https://docs.google.com/spreadsheets/d/1qNmkmNWLqhysgNBbvnSO1AEn_j01Su14doAJpRrPEjE

Add to favorites checkbox:

If Items!B3=True (tick checkbox) then copy Items!E1 and paste to Favorites!A2 if it's empty, if it's not empty paste to Favorites!A2 1 cell (eg. Favorites!A3, Favorites!A4 etc.) then make Favorites!B3=False (untick checkbox). Also check if copied value already exist then do not paste and make Favorites!B3=False (untick checkbox).

Remove from favorites checkbox:

If Items!B4=True (tick checkbox) then filter Items!E1 with Favorites!A:A and delete the matched value from Favorites!A:A, then make Items!B4=False (untick checkbox).

Some further explanation: https://puu.sh/ISxAD/6d38c87af3.png https://puu.sh/ISxFC/14bf4a6520.png

Thanks

CodePudding user response：

Here you go (updated):

function onEdit(e) {
  if (e.range.getSheet().getName() != 'Charts') return;
  if (e.range.columnStart != 7) return;
  if (e.range.rowStart == 1 && e.value == 'TRUE') add_item(e);
  if (e.range.rowStart == 2 && 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 favorites'); return}
  
  favs.push(item);
  favs = [header, ...favs.sort()].map(x => [x]);
  favs_sheet.getRange(1,1,favs.length,favs[0].length).setValues(favs);
  ss.toast(item   ' was added');
}

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 favorites'); return }
  
  favs = [header, ...favs.filter(x => x != item).sort()].map(x => [x]);
  favs_range.clear();
  favs_sheet.getRange(1,1,favs.length,favs[0].length).setValues(favs);
  ss.toast(item   ' was removed');
}

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