0

EDIT thanks for the assistance, I've updated the script as follows, however it still will not create a submenu from the Add-on menu.

// Hide empty rows in PRINT sheet
function onOpen(e) {
 var menu = SpreadsheetApp.getUi().createMenu('Print Ready');

 menu.addItem('Print Ready', 'PRINTREADY');
 menu.addToUi(); 

}
function PRINTREADY(){
  var shname='PRINT';//<<<<<<<<<<<<<<<change to appropriate sheet name
  var rw1=4, rw2=258;
  var s, nrws, data, i;
  nrws=rw2-rw1+1;
  s=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shname);
  data=s.getRange(rw1, 1, nrws, s.getLastColumn()).getValues();
  s.showRows(rw1, nrws);
  for (i in data) if (data[i].every(function(x) {return x=='';})) s.hideRows(1*i+rw1);
}

  // Sort PANTRY LIST A-Z
  var sh = SpreadsheetApp.getActiveSheet();
  if( sh.getName() == "PANTRY LIST" ) {
    var editedCell = sh.getActiveRange().getColumnIndex();
    if(editedCell == 1) { 
    var range = sh.getRange("PANTRY LIST!A3:Z");
    range.sort({column: 1});
  }
}

I'm trying to deploy a sheets addon for some restaurant menu costing spreadsheets I have written. For some of the functionality I needed to include scripts, by themselves they are fairly straightforward:

Script 1: HideEmptyRows - this script hides empty rows to make the recipe ready to print, it should only run on a recipe document with a sheet name PRINT.

Script 2: SortList - this script simply sorts the sheet A-Z onOpen and every 15 minutes, it only runs on the Pantry List document with a sheet name PANTRY LIST.

They run fine individually as scripts, but when I combine them into a sheets addon Script 1 doesn't generate a menu, and I get Range Not Found errors from Script 2.

Script 1

function onOpen(e) {
 var menu = SpreadsheetApp.getUi().createMenu('Print Ready')

 menu.addItem('Make my recipe ready to print', 'PRINTREADY')
 .addToUi(); 
}
function PRINTREADY(){
  var shname='PRINT';//<<<<<<<<<<<<<<<change to appropriate sheet name
  var rw1=4, rw2=258;
  var s, nrws, data, i;
  nrws=rw2-rw1+1;
  s=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shname);
  data=s.getRange(rw1, 1, nrws, s.getLastColumn()).getValues();
  s.showRows(rw1, nrws);
  for (i in data) if (data[i].every(function(x) {return x=='';})) s.hideRows(1*i+rw1);
}

Script 2

function onOpen(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var editedCell = sh.getActiveRange().getColumnIndex();

  if(editedCell == 1) { 
    var range = sh.getRange("PANTRY LIST!A3:Z");
    range.sort({column: 1});
  }
}

Does anyone know how to combine them into one script that I can deploy as an addon?

Thanks

aaron
  • 1
  • 1
  • 1
    I'm surprised the second works at all, as `onOpen` functions only run when the workbook is opened, and never in response to a cell being edited (unless you have installed a trigger for it to do that). Note that you can only have one function with a given name per Apps Script project, and if you have multiple, only one will be used. **If you do not make an actual attempt to integrate your scripts and ask here how to resolve the specific issues you encounter, your questions will only attract downvotes** – tehhowch Apr 10 '18 at 13:04
  • Also: https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea – tehhowch Apr 10 '18 at 13:06
  • If there are two scripts of the same name, then the last one is the script that will always be executed. That's why script 1 never generates a menu. If you want script 2 to run when there is an edit, then rename the script to something else, and "install" a trigger. Edit menu, current projects triggers. You can call script two from script one. Try it, and if you have trouble, edit your question. – Alan Wells Apr 10 '18 at 13:17
  • I have submitted an edit which maintains a good question layout (problem statement followed by your attempts to resolve it), and improves the formatting. Your sorting code is not contained within a function. – tehhowch Apr 10 '18 at 14:20

0 Answers0