0

I'm trying to display the contents the of a Google Sheet in a sidebar via HTML. I have a Google Apps Script function that returns a multi-dimensional array consisting of values in the rows and columns in the spreadsheet:

/**
 * Gets comment from target sheets data file
 */
function getComments() {
  //get target data sheet and construct comment block
  var ss = GLOBAL_DataFile.getSheetByName("Comments");
  //populate array of comments stored in data file
  var commentsRowsArray=[];
  var commentsColsArray=[];

  //loop through all the rows in the range
  for (var rows = 2; rows < ss.getDataRange().getLastRow(); rows++) 
  {
    var formattedDate = ss.getRange(rows, 3).getValue();
    var formattedUser = ss.getRange(rows, 4).getValue();
    var formattedText = ss.getRange(rows, 5).getValue();

    if (commentBlockText != "") {
      commentBlockText = commentBlockText
        + formattedDate
        + '\n' + formattedUser
        + '\n' + formattedText
        + '\n\n';
    } 
    else {
      commentBlockText = formattedDate
        + '\n' + formattedUser
        + '\n' + formattedText
        + '\n\n';
    }

    //loop through each column
    for (var cols = 1; cols < ss.getDataRange().getLastColumn(); cols++) {
      commentsColsArray[cols - 1] = ss.getRange(rows, cols).getValue();
    }
    commentsRowsArray[rows - 2] = commentsColsArray;
  }

  //var result = GLOBAL_UI.alert(commentBlockText);
  return commentsRowsArray;
}

In the html file, I'd like to display some of the contents of this array (in like an HTML table with CSS formatting). After reading answers to similar posts on here, it seems one way to do this is to use innerHTML to print to a div container in the HTML:

<html>
  <head>
    <!-- (OPTIONAL) Make your sidebar look consistent with Google Sheets with the below CSS https://developers.google.com/apps-script/add-ons/css -->  
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <!-- The CSS package above applies Google styling to buttons and other elements. -->
    <script>
      // Grab the output element
      var commentsList = document.getElementById('CommentsList');

      //https://stackoverflow.com/questions/16117290/display-the-result-of-a-javascript-function-in-a-div-element
      function printCommentsToHtml() {
        commentsList.innerHTML = "";
        //this is the google apps script that reads the values from a google spreadsheet and returns a bidimensional array.
        var comments = getComments(); 
        var singleInstance = [];
        for (var x = 0; x < comments.length(); x++) {
          singleInstance = comments[x];
          for (i = 0; i < singleInstance.length(); i++) {
            commentsList.innerHTML = commentsList.innerHTML + singleInstance[i] + ' /n ';
          }
          commentsList.innerHTML = commentsList.innerHTML + '-------------';
        }
      }
    </script>    
  </head>
  <body onload='printCommentsToHtml();'>

    <!-- executing scriplet 'getcomments' to serve up comments in html 
      Line breaks in templated HTML scriptlets; use <?=...?> tags to display results from javascript function
      https://stackoverflow.com/questions/28511406/google-apps-script-line-breaks-in-templated-html-scriptlets -->

    <div id="CommentsList"></div>
  </body>
</html>

Any idea what I'm doing wrong? I'm a newbie so may be missing something obvious. I did consider briefly using JQuery for this but I think there will be a steep learning curve before I can use that.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
AKD
  • 21
  • 2
  • 1
    First glance, you are overwriting the innerHTML for every iteration of the loop. Build the html in a variable and then assign the innerHTML after the loop. – daddygames Apr 10 '18 at 20:29
  • You don't show the Apps Script code you use to render the sidebar, so it isn't clear if you're evaluating an `HtmlTemplate` or directly constructing an `HtmlOutput`. I'd recommend using the asynchronous `google.script.run` pattern. Some reading for you: https://developers.google.com/apps-script/guides/dialogs#custom_sidebars https://developers.google.com/apps-script/guides/html/communication – tehhowch Apr 10 '18 at 20:56

0 Answers0