1

Excel has a little box on the upper left corner that displays the name of the cell. Does Google Sheets have a similar capability? I've named cell B5 "hours" and cell B6 "hrlyrate" so I want to be able to see the name when I click in the cell. I don't want to have to look at the entire list of names.

  • I was liking Google much better than Excel until I ran into this, this really makes it not feasible to use names. – Mikee Jan 13 at 16:31
1

Google Sheets doesn't include a built-in feature that only displays the range name of the active cell.

By the other hand, at this time,

  • the Google Apps Script triggers doesn't include on that is fired when a user click on a cell
  • We could use the toast method to show a message on the bottom right corner of displayed sheet, but it isn't permanent.
  • the custom user defined interfaces are limited to menus, dialogs and sidebars, it's not possible to add a custom message to the spreadsheet toolbar.

If you are open to use a sidebar or a dialog, you could use client-side code to continuously poll your spreadsheet to check which is the active cell and then apply an algorithm if the active cell has assigned an name. The basic application of this on a spreadsheet is descripted on the answer to How do I make a Sidebar display values from cells?

By the way, I applied the poll technique on my add-on, Visor de Registros

Sample Code

If you are new to extending Google Sheets through Google Apps Script, please read first https://developers.google.com/apps-script/guides/sheets

Code.gs

/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Cell name')
    .addItem('Show sidebar', 'showSidebar')
    .addToUi();
}

/**
 * Opens a sidebar 
 */
function showSidebar(){

  // Sidebar document structure
  var html = '<div>'
  + '<p>Cell Name '
  + '<span id="cell-name">Loading...</span>'
  + '</p>'
  + '</div>'
  + '<div id="sidebar-status"></div>';

  // Sidebar JavaScript
  var javaScript = HtmlService.createHtmlOutputFromFile('SidebarJavaScript');

  // User interface
  var sidebar = HtmlService.createHtmlOutput()
                           .append(html)
                           .append(javaScript.getContent())
                           .setTitle('Cell Name');

  // Display user interface
  SpreadsheetApp.getUi().showSidebar(sidebar);
}

/**
 * If the active cell is a named range, returns its name,
 * otherwise returns No named cell
 *
 */
function getName(){
  var cell = SpreadsheetApp.getCurrentCell();
  var namedRanges = SpreadsheetApp.getActive().getNamedRanges();
  for(var i = 0;i< namedRanges.length; i++){
    var range = namedRanges[i].getRange();
    if(cell.getA1Notation() === range.getA1Notation()) return namedRanges[i].getName();
  }
  return 'No named cell';
}

SidebarJavaScript.html

Adapted from the Mogsdad's answer cited above.

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script>
  /**
   * Run initializations on sidebar load.
   */
  $(function() {
    // Assign handler functions to sidebar elements here, if needed.

    // Call the server here to retrieve any information needed to build
    // the dialog, if necessary.

    // Start polling for updates        
    poll();
  });

  /**
   * Poll a server-side function at the given interval, to have
   * results passed to a successHandler callback.
   *
   * https://stackoverflow.com/a/24773178/1677912
   *
   * @param {Number} interval   (optional) Time in ms between polls.
   *                            Default is 2s (2000ms)
   */
  function poll(interval) {
    interval = interval || 1000;
    setTimeout(function() {
      google.script.run
        .withSuccessHandler(showCellName)
        .withFailureHandler(
          function(msg, element) {
            showStatus(msg);
            element.disabled = false;
          })
        .getName();
    }, interval);
  };

  function showCellName(name){
    $("#cell-name").replaceWith('<span id="cell-name">'+ name +'</span>');

    poll();
  }

  /**
   * Displays the given status message in the sidebar.
   *
   * @param {String} msg The status message to display.
   * @param {String} classId The message type (class id) that the message
   *   should be displayed as.
   */
  function showStatus(msg, classId) {
    $('#sidebar-status').removeClass().html(msg);
    if (classId) {
      $('#sidebar-status').addClass(classId);
    }
  }

</script>
0

do you refer to this?

if so, then yes, otherwise not

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.