How do I make a Sidebar display values from cells?

Question

I have a Google Sheet that I'm using as a database of clients. There are quite a lot of variables (columns) that I need to store per entry, so I thought that it would be easier to view each entry if I could have a Sidebar display all of the values of the active row.

Example: If I click cell C1, it will display the values from C1, C2, C3 and C4 in the sidebar.

Is this possible? I've gotten as far as getting the Sidebar to display but I cannot figure out how to get a value from a cell and print it to the Sidebar.

Thank you in advance, I've reached a dead end with my (very) limited intellect!

Answer 1

Something like this?

This add-on uses the poller idea from How to poll a Google Doc from an add-on to call a server function getRecord(). That function grabs the row of data that is currently selected, and returns it to the showRecord() callback on the client (JavaScript) side, which handles the presentation in the sidebar.

It's not complete - watch for TODO comments. I wonder if this would be worth further developing and publishing?

Code.gs

/**
 * @OnlyCurrentDoc  Limits the script to only accessing the current spreadsheet.
 */

var SIDEBAR_TITLE = 'Record Viewer';

/**
 * 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()
      .createAddonMenu()
      .addItem('View records', 'showSidebar')
      .addToUi();
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Opens a sidebar. The sidebar structure is described in the Sidebar.html
 * project file.
 */
function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('Sidebar')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle(SIDEBAR_TITLE);
  SpreadsheetApp.getUi().showSidebar(ui);
}


/**
 * Returns the active row.
 *
 * @return {Object[]} The headers & values of all cells in row.
 */
function getRecord() {
  // Retrieve and return the information requested by the sidebar.
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var rowNum = sheet.getActiveCell().getRow();
  if (rowNum > data.length) return [];
  var record = [];
  for (var col=0;col<headers.length;col++) {
    var cellval = data[rowNum-1][col];
    // Dates must be passed as strings - use a fixed format for now
    if (typeof cellval == "object") {
      cellval = Utilities.formatDate(cellval, Session.getScriptTimeZone() , "M/d/yyyy");
    }
    // TODO: Format all cell values using SheetConverter library
    record.push({ heading: headers[col],cellval:cellval });
  }
  return record;
}

Sidebar.html

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<!-- Below is the HTML code that defines the sidebar element structure. -->
<div class="sidebar branding-below">
  <p>
  This sidebar displays all cells in a row, as a "record".
  </p>
  <!-- The div-table class is used to make a group of divs behave like a table. -->
  <div class="block div-table" id="sidebar-record-block">
  </div>
  <div class="block" id="sidebar-button-bar">
  </div>
  <div id="sidebar-status"></div>
</div>

<!-- Enter sidebar bottom-branding below. -->
<div class="sidebar bottom">
  <img alt="Add-on logo" class="logo" width="25"
      src="https://googledrive.com/host/0B0G1UdyJGrY6XzdjQWF4a1JYY1k/apps-script_2x.png">
  <span class="gray branding-text">Record Viewer by Mogsdad</span>
</div>

<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('SidebarJavaScript').getContent(); ?>

SidebarJavascript.html

<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(showRecord)
        .withFailureHandler(
          function(msg, element) {
            showStatus(msg, $('#button-bar'));
            element.disabled = false;
          })
        .getRecord();
    }, interval);
  };

  /**
   * Callback function to display a "record", or row of the spreadsheet.
   *
   * @param {object[]}  Array of field headings & cell values
   */
  function showRecord(record) {
    if (record.length) {
      for (var i = 0; i < record.length; i++) {
        // build field name on the fly, formatted field-1234
        var str = '' + i;
        var fieldId = 'field-' + ('0000' + str).substring(str.length)

        // If this field # doesn't already exist on the page, create it
        if (!$('#'+fieldId).length) {
          var newField = $($.parseHTML('<div id="'+fieldId+'"></div>'));
          $('#sidebar-record-block').append(newField);
        }

        // Replace content of the field div with new record
        $('#'+fieldId).replaceWith('<div id="'+fieldId+'" class="div-table-row"></div>');
        $('#'+fieldId).append($('<div class="div-table-th">' + record[i].heading + '</div>'))
                      .append('<div class="div-table-td">' + record[i].cellval + '</div>');
      }
    }

    // TODO: hide any existing fields that are beyond the current record length

    //Setup the next poll
    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>

Stylesheet.html

<!-- This CSS package applies Google styling; it should always be included. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">

<style>
label {
  font-weight: bold;
}

.branding-below {
  bottom: 54px;
  top: 0;
}

.branding-text {
  left: 7px;
  position: relative;
  top: 3px;
}

.logo {
  vertical-align: middle;
}

.width-100 {
  width: 100%;
  box-sizing: border-box;
  -webkit-box-sizing : border-box;‌
  -moz-box-sizing : border-box;
}

#sidebar-value-block,
#dialog-elements {
  background-color: #eee;
  border-color: #eee;
  border-width: 5px;
  border-style: solid;
}

#sidebar-button-bar,
#dialog-button-bar {
  margin-bottom: 10px;
}

.div-table{
  display:table;
  width:auto;
/*  background-color:#eee;
  border:1px solid  #666666;*/
  border-spacing:5px;
}
.div-table-row{
  display:table-row;
  width:auto;
  clear:both;
}
.div-table-td, .div-table-th {
  display:table-cell;         
  width:200px;         
  background-color:rgb(230, 230, 230);  
}
.div-table-th {
  /*float:left;*/
  font-weight: bold;
}
.div-table-td {
  /*float:right;*/
}
</style>