1

I'm setting up a product tool in a google sheet, and the purpose is to only show up to 8 of about 91 columns. Each column is a product, each row is the "entitlements" or features of that product.

The sheet gives users the ability to pick from a dropdown (data validation) from the list of 91 products - up to 8 times. I'd like to then have users click 'compare' and it only shows those specific 8 products (columns) shown in the sheet.

I've written script to create a menu and automatically hide all columns on open. Where do I go from here to tie a 'Compare' button on the sheet to take all values chosen by the user, match them up to the right columns, and then show those columns?

function onOpen() {
  var menu = [{name: "Hide all Products", functionName: "hideAllColumns"}, {name: "Show all products", functionName: "showAllColumns"}]
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Product Tool", menu);
    var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  hideAllColumns();
}

function showAllColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.showColumns(2);
  sheet.showColumns(3);
  sheet.showColumns(4);
  sheet.showColumns(5);
  sheet.showColumns(6);
  sheet.showColumns(7);
  sheet.showColumns(8);
  sheet.showColumns(9);
  sheet.showColumns(10);
  sheet.showColumns(11);
  sheet.showColumns(12);
  sheet.showColumns(13);
  sheet.showColumns(14);
  sheet.showColumns(15);
  sheet.showColumns(16);
  sheet.showColumns(17);
  sheet.showColumns(18);
  sheet.showColumns(19);
  sheet.showColumns(20);
  sheet.showColumns(21);
  sheet.showColumns(22);
  sheet.showColumns(23);
  sheet.showColumns(24);
  sheet.showColumns(25);
  sheet.showColumns(26);
  sheet.showColumns(27);
  sheet.showColumns(28);
  sheet.showColumns(29);
  sheet.showColumns(30);
  sheet.showColumns(31);
  sheet.showColumns(32);
  sheet.showColumns(33);
  sheet.showColumns(34);
  sheet.showColumns(35);
  sheet.showColumns(36);
  sheet.showColumns(37);
  sheet.showColumns(38);
  sheet.showColumns(39);
  sheet.showColumns(40);
  sheet.showColumns(41);
  sheet.showColumns(42);
  sheet.showColumns(43);
  sheet.showColumns(44);
  sheet.showColumns(45);
  sheet.showColumns(46);
  sheet.showColumns(47);
  sheet.showColumns(48);
  sheet.showColumns(49);
  sheet.showColumns(50);
  sheet.showColumns(51);
  sheet.showColumns(52);
  sheet.showColumns(53);
  sheet.showColumns(54);
  sheet.showColumns(55);
  sheet.showColumns(56);
  sheet.showColumns(57);
  sheet.showColumns(58);
  sheet.showColumns(59);
  sheet.showColumns(60);
  sheet.showColumns(61);
  sheet.showColumns(62);
  sheet.showColumns(63);
  sheet.showColumns(64);
  sheet.showColumns(65);
  sheet.showColumns(66);
  sheet.showColumns(67);
  sheet.showColumns(68);
  sheet.showColumns(69);
  sheet.showColumns(70);
  sheet.showColumns(71);
  sheet.showColumns(72);
  sheet.showColumns(73);
  sheet.showColumns(74);
  sheet.showColumns(75);
  sheet.showColumns(76);
  sheet.showColumns(77);
  sheet.showColumns(78);
  sheet.showColumns(79);
  sheet.showColumns(80);
  sheet.showColumns(81);
  sheet.showColumns(82);
  sheet.showColumns(83);
  sheet.showColumns(84);
  sheet.showColumns(85);
  sheet.showColumns(86);
  sheet.showColumns(87);
  sheet.showColumns(88);
  sheet.showColumns(89);
  sheet.showColumns(90);
  sheet.showColumns(91);
  sheet.showColumns(92);
}

function hideAllColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.hideColumns(2);
  sheet.hideColumns(3);
  sheet.hideColumns(4);
  sheet.hideColumns(5);
  sheet.hideColumns(6);
  sheet.hideColumns(7);
  sheet.hideColumns(8);
  sheet.hideColumns(9);
  sheet.hideColumns(10);
  sheet.hideColumns(11);
  sheet.hideColumns(12);
  sheet.hideColumns(13);
  sheet.hideColumns(14);
  sheet.hideColumns(15);
  sheet.hideColumns(16);
  sheet.hideColumns(17);
  sheet.hideColumns(18);
  sheet.hideColumns(19);
  sheet.hideColumns(20);
  sheet.hideColumns(21);
  sheet.hideColumns(22);
  sheet.hideColumns(23);
  sheet.hideColumns(24);
  sheet.hideColumns(25);
  sheet.hideColumns(26);
  sheet.hideColumns(27);
  sheet.hideColumns(28);
  sheet.hideColumns(29);
  sheet.hideColumns(30);
  sheet.hideColumns(31);
  sheet.hideColumns(32);
  sheet.hideColumns(33);
  sheet.hideColumns(34);
  sheet.hideColumns(35);
  sheet.hideColumns(36);
  sheet.hideColumns(37);
  sheet.hideColumns(38);
  sheet.hideColumns(39);
  sheet.hideColumns(40);
  sheet.hideColumns(41);
  sheet.hideColumns(42);
  sheet.hideColumns(43);
  sheet.hideColumns(44);
  sheet.hideColumns(45);
  sheet.hideColumns(46);
  sheet.hideColumns(47);
  sheet.hideColumns(48);
  sheet.hideColumns(49);
  sheet.hideColumns(50);
  sheet.hideColumns(51);
  sheet.hideColumns(52);
  sheet.hideColumns(53);
  sheet.hideColumns(54);
  sheet.hideColumns(55);
  sheet.hideColumns(56);
  sheet.hideColumns(57);
  sheet.hideColumns(58);
  sheet.hideColumns(59);
  sheet.hideColumns(60);
  sheet.hideColumns(61);
  sheet.hideColumns(62);
  sheet.hideColumns(63);
  sheet.hideColumns(64);
  sheet.hideColumns(65);
  sheet.hideColumns(66);
  sheet.hideColumns(67);
  sheet.hideColumns(68);
  sheet.hideColumns(69);
  sheet.hideColumns(70);
  sheet.hideColumns(71);
  sheet.hideColumns(72);
  sheet.hideColumns(73);
  sheet.hideColumns(74);
  sheet.hideColumns(75);
  sheet.hideColumns(76);
  sheet.hideColumns(77);
  sheet.hideColumns(78);
  sheet.hideColumns(79);
  sheet.hideColumns(80);
  sheet.hideColumns(81);
  sheet.hideColumns(82);
  sheet.hideColumns(83);
  sheet.hideColumns(84);
  sheet.hideColumns(85);
  sheet.hideColumns(86);
  sheet.hideColumns(87);
  sheet.hideColumns(88);
  sheet.hideColumns(89);
  sheet.hideColumns(90);
  sheet.hideColumns(91);
  sheet.hideColumns(92);
}

Product Tool Demo Product Tool Demo Controls

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.