Google Sheets – How to Create Multiple Menu Items

google sheetsgoogle-apps-script

I have two scripts I'm making:

Shorten URLs within a Google Sheet
Printing a single sheet and sending this as a PDF to a specified email

I have successfully created the first script and added it as a menu item in my Google Sheet, but notice that when I started working on the second script, the first menu item has disappeared!

Is there a way to have both scripts show as menu items please?

Best Answer

Rubén pointed out an example using getUi method, and here is a different one, using addMenu method. "Custom" is the menu name that will appear in the navigation bars, and the names in the list below will appear under it, and call the corresponding functions.

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [
    {name: "Delete and Shift Up",       functionName: "deleteAndShiftUp"}, 
    {name: "Insert New Content Above",  functionName: "insertContentAbove"},
    {name: "Insert Blank Cells Above",  functionName: "insertAbove"},
  ]);
}

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Google-sheets – Trouble renaming a pdf file created in Apps script

Something like this will rename the file pulling from cells a1:b1

var file=DriveApp.getFileById('1ipTs_BEwXqurQUfcZ_QO1E110vw3Zv_06BQ7bLbdTkY');
      var range = SpreadsheetApp.getActive().getRange('A1:B1');
      var first = range.getCell(1, 1);
      var last = range.getCell(1,2);
      var name = first.getValue() + ' ' + last.getValue();

      file.setName(name);

Best Answer

Related Solutions

Google-sheets – Fetch cell row and column indices in Google Sheets

Code

Explained

Screenshot

Example

Google-sheets – Trouble renaming a pdf file created in Apps script

Related Topic