Searched for a named range via script

Question

I'm having a difficult time trying to piece together a script for the following:

Essentially I'm trying to create a menu UI that would allow the user to input a named range in a prompt dialog and then have the script search, go to and highlight that range in the workbook if it exists. Much like selecting a range from the named ranges sidebar container.

My issue with using the named ranges sidebar container to find an existing range is that I have a 100+ named ranges that I have to scroll through because the named range sidebar container doesn't appear to sort alphabetically.

Let me know if this makes sense or needs additional clarity. I deeply appreciate any help or a pointer in the right direction!

EDIT: After research and a little trial and error, here's what I've put together. Currently it activates the range on a sheet based on a prompt input.

However, I'm currently trying to extend the script so that I can search for a named range from a different sheet and navigate to it. I'm struggling with the looping logic.

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Search Named Ranges')
      .addItem('Search', 'showPrompt')
      .addToUi();
}

function showPrompt() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  var result = ui.prompt(
      'Search for a named range',
      'Enter Named Range:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.

  var button = result.getSelectedButton();
  var RangeToSearch = result.getResponseText();

  if (button == ui.Button.OK) {
    // User clicked "OK".
    // User searched for a named range

    sh.setActiveSelection(RangeToSearch) 
   // IF RANGE ISN'T ON ACTIVESHEET, NEXT SHEET

  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.
  }
}

Welcome. This site are for specific questions about programming. Was you able to create the menu to open the prompt dialog? Do you know how to activate a range? — Rubén

Cooper Cooper · Accepted Answer · 2017-10-22T15:12:39

This will allow you to enter a range name. If it exists, it will find and navigate to it.

function findNameRange()
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var nrs=ss.getNamedRanges();
  if(nrs.length>0)
  {
    var resp=SpreadsheetApp.getUi().prompt('Name of Range to Search for:', 'Enter Name of Named Range to Search for.', SpreadsheetApp.getUi().ButtonSet.OK);
    var target=resp.getResponseText();
    if(target)
    {
      for(var i=0;i<nrs.length;i++)
      {
        if(target==nrs[i].getName())
        {
          var rg=nrs[i].getRange();
          rg.activate();
          break;
        }
      }
    }
  }
  else
  {
    SpreadsheetApp.getUi().alert('No Named Ranges');
  }
}

Searched for a named range via script

1 Answers