How do I make a Sidebar display values from cells?

by

Something like this?

screenshot

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="https://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>

More Related Contents:

  1. Moving google apps script to v8 file upload stopped working from sidebar
  2. How to automatically import data from uploaded CSV or XLS file into Google Sheets
  3. Create Google Calendar Events from Spreadsheet but prevent duplicates
  4. Google Apps Script: How to set “Use column A as labels” in chart embedded in spreadsheet?
  5. Google Spreadsheet SCRIPT Check if edited cell is in a specific range
  6. button click is only working on Windows & not working on Android mobile sheet
  7. Custom functions intermittently hang with “Error – Loading Data”
  8. Retrieving Google Sheets cell border style programmatically
  9. Determining the last row in a single column
  10. Cannot find method getRange(number,number)?
  11. How do I add formulas to Google Sheets using Google Apps Script?
  12. Deleting rows in google sheets using Google Apps Script
  13. Delete a row in Google Spreadsheets if value of cell in said row is 0 or blank
  14. Adding Buttons To Google Sheets and Set value to Cells on clicking
  15. Google apps script to email google spreadsheet excel version [duplicate]
  16. Custom function throws a “You do not have the permission required to setValue” error
  17. Margins parameters for Google spreadsheet export as PDF
  18. Script to convert .XLSX to Google Sheet and move converted file
  19. Hidden field in a Google Form
  20. Measurement of execution time of built-in functions for Spreadsheet
  21. onEdit() doesn’t catch all changes
  22. Google Spreadheets Scripts: check if cell is empty
  23. Custom Functions with add-ons?
  24. How to prefill Google form checkboxes?
  25. Converting .xls to google spreadsheet in google apps script
  26. TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
  27. Google spreadsheet “Cannot call method “getRange” of null”
  28. .setFormula() and received error missing ) argument list
  29. How can I merge multiple tabs in a Google Spreadsheet using Google App Script?
  30. Passing cell references to spreadsheet functions

Leave a Comment