Pass an array of JSON objects from Google Sheets to HTML service

Question

I have a spreadsheet with a number of hotel names along with addresses, phone numbers and contact names as follows:

I have a function which stores these hotels as JSON objects as follows:

var nameRange = [
  "D25",
  "D26",
  "D27",
  "D28",
  "D29",

  "D32",
  "D33",
  "D34",
  "D35",
  "D36",

  "D39",
  "D40",
  "D41",
  "D42",
  "D43",

  "D46",
  "D47",
  "D48",
  "D49",
  "D50",

  "D53",
  "D54",
  "D55",
  "D56",
  "D57",
]

var addressRange = [
  "G25",
  "G26",
  "G27",
  "G28",
  "G29",

  "G32",
  "G33",
  "G34",
  "G35",
  "G36",

  "G39",
  "G40",
  "G41",
  "G42",
  "G43",

  "G46",
  "G47",
  "G48",
  "G49",
  "G50",

  "G53",
  "G54",
  "G55",
  "G56",
  "G57",
]

var contactRange = [
  "J25",
  "J26",
  "J27",
  "J28",
  "J29",

  "J32",
  "J33",
  "J34",
  "J35",
  "J36",

  "J39",
  "J40",
  "J41",
  "J42",
  "J43",

  "J46",
  "J47",
  "J48",
  "J49",
  "J50",

  "J53",
  "J54",
  "J55",
  "J56",
  "J57",
]

var telRange = [
  "L25",
  "L26",
  "L27",
  "L28",
  "L29",

  "L32",
  "L33",
  "L34",
  "L35",
  "L36",

  "L39",
  "L40",
  "L41",
  "L42",
  "L43",

  "L46",
  "L47",
  "L48",
  "L49",
  "L50",

  "L53",
  "L54",
  "L55",
  "L56",
  "L57",
]

function findHotel() {
  var jsonArr = [];

  for (var i = 0; i < nameRange.length; i++) {
    jsonArr.push({
      name: ss.getRange(nameRange[i]).getValue(),
      address: ss.getRange(addressRange[i]).getValue(),
      contact: ss.getRange(contactRange[i]).getValue(),
      tel: ss.getRange(telRange[i]).getValue(),
    });
  }
  Logger.log(jsonArr);
};

I also have a side bar, which can be opened through a menu option, which needs to display the hotels in their current order. The sidebar is opened with the following script in my .gs file:

// open sidebar 
function openSortableSidebar(data) {
  var html = HtmlService.createTemplateFromFile('sortable');
  var sidebar_html = html.evaluate();
  SpreadsheetApp.getUi().showSidebar(sidebar_html);
}

// create custom menu - run openSortableSidebar 
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Menu')
    .addItem('Sort meeting order', 'openSortableSidebar')
    .addToUi();
}

And the HTML for my sidebar is as follows:

<div id="timesColumn" class="row no-gutter">
  <div class="col-xs-3">
    <div class="times day">Day</div>
    <input type="text" value="09:00" class="times">
    <input type="text" value="11:00" class="times">
    <input type="text" value="13:00" class="times">
    <input type="text" value="15:00" class="times">
    <input type="text" value="17:00" class="times">
    <div class="times day">Day</div>
    <input type="text" value="09:00" class="times">
    <input type="text" value="11:00" class="times">
    <input type="text" value="13:00" class="times">
    <input type="text" value="15:00" class="times">
    <input type="text" value="17:00" class="times">
    <div class="times day">Day</div>
    <input type="text" value="09:00" class="times">
    <input type="text" value="11:00" class="times">
    <input type="text" value="13:00" class="times">
    <input type="text" value="15:00" class="times">
    <input type="text" value="17:00" class="times">
    <div class="times day">Day</div>
    <input type="text" value="09:00" class="times">
    <input type="text" value="11:00" class="times">
    <input type="text" value="13:00" class="times">
    <input type="text" value="15:00" class="times">
    <input type="text" value="17:00" class="times">
    <div class="times day">Day</div>
    <input type="text" value="09:00" class="times">
    <input type="text" value="11:00" class="times">
    <input type="text" value="13:00" class="times">
    <input type="text" value="15:00" class="times">
    <input type="text" value="17:00" class="times">
  </div>
  <!-- hotel list -->
  <div id="hotelColumn" class="col-xs-9">
    <form id="sortable" class="ui-state">
      <div class="sortableItem day">Monday</div>
      <div class="sortableItem swapable ui-state-default" id="result1"></div>
      <div class="sortableItem swapable ui-state-default" id="result2"></div>
      <div class="sortableItem swapable ui-state-default" id="result3"></div>
      <div class="sortableItem swapable ui-state-default" id="result4"></div>
      <div class="sortableItem swapable ui-state-default" id="result5"></div>
      <div class="sortableItem day">Tuesday</div>
      <div class="sortableItem day hidden"></div>
      <div class="sortableItem swapable ui-state-default" id="result8"></div>
      <div class="sortableItem swapable ui-state-default" id="result9"></div>
      <div class="sortableItem swapable ui-state-default" id="result10"></div>
      <div class="sortableItem swapable ui-state-default" id="result11"></div>
      <div class="sortableItem swapable ui-state-default" id="result12"></div>
      <div class="sortableItem day">Wednesday</div>
      <div class="sortableItem day hidden"></div>
      <div class="sortableItem swapable ui-state-default" id="result15"></div>
      <div class="sortableItem swapable ui-state-default" id="result16"></div>
      <div class="sortableItem swapable ui-state-default" id="result17"></div>
      <div class="sortableItem swapable ui-state-default" id="result18"></div>
      <div class="sortableItem swapable ui-state-default" id="result19"></div>
      <div class="sortableItem day">Thursday</div>
      <div class="sortableItem day hidden"></div>
      <div class="sortableItem swapable ui-state-default" id="result22"></div>
      <div class="sortableItem swapable ui-state-default" id="result23"></div>
      <div class="sortableItem swapable ui-state-default" id="result24"></div>
      <div class="sortableItem swapable ui-state-default" id="result25"></div>
      <div class="sortableItem swapable ui-state-default" id="result26"></div>
      <div class="sortableItem day">Friday</div>
      <div class="sortableItem day hidden"></div>
      <div class="sortableItem swapable ui-state-default" id="result29"></div>
      <div class="sortableItem swapable ui-state-default" id="result30"></div>
      <div class="sortableItem swapable ui-state-default" id="result31"></div>
      <div class="sortableItem swapable ui-state-default" id="result32"></div>
      <div class="sortableItem swapable ui-state-default" id="result33"></div>

So essentially, when I open the sidebar the hotel names need to display within the given divs in the correct order. My issue is with passing the JSON objects through so that they are accessible within the HTML service.

I had this working so that I could pass the hotel names over. However, the point of the sidebar is that the user can change the order of the hotels and will eventually be able to press submit and pass this back to the Google Sheet so that the order is changed on the sheet itself.

I therefore need a way to pass the objects to the HTML so that the hotel names keep the same address, contact and telephone values for later use.

You don't need an `HtmlTemplate` step. Write your HTML such that you call a server side `getHotels()` function, and in the async success handler, store the hotel JSON in a local client variable (and also use the json to fill in your UI elements, e.g. the step you currently do in your template evaluation). Then when the user changes orders, you can update the client JSON, and pass it back to the apps script server. Read the documentation and best practices for client-server communication. — tehhowch, Jul 19 '18 at 19:23
Ah yes, this is great. Sorted it now using the google.script.run.withSuccessHandler. Thanks a lot for your help! — DevB1, Jul 19 '18 at 21:30

score 3 · Accepted Answer · edited Jul 20 '18 at 09:19

Sorted this in the end with the help of @tehhowch.

I amended my server side function to return the JSON array:

function findHotel() {
  var jsonArr = [];

  for (var i = 0; i < nameRange.length; i++) {
    jsonArr.push({
      name: ss.getRange(nameRange[i]).getValue(),
      address: ss.getRange(addressRange[i]).getValue(),
      contact: ss.getRange(contactRange[i]).getValue(),
      tel: ss.getRange(telRange[i]).getValue(),
    });
  }
  return jsonArr;
};

Then on the client side I wrote the following function:

function onSuccess(test) {
  var idArray = [1, 2, 3, 4, 5, 8, 9, 10, 11, 12, 15, 16, 17, 18, 19, 22, 23, 24, 25, 26, 29, 30, 31, 32, 33];
  var hotelArray = test;
  for (var i = 0; i < idArray.length; i++) {
    $("#result" + (idArray[i])).html(test[i].name);
  }
}

google.script.run.withSuccessHandler(onSuccess).findHotel();

The idArray is used to access the various 'result' IDs and I now can not only print the hotel names in the required place but I also now have full access to the arrays created on the server side.

Pass an array of JSON objects from Google Sheets to HTML service

1 Answers1

Linked