Google Apps Script is changing my Array into a string. Why?

Question

I have a fairly simple call to my code.gs file for some data that lives in a sheet. I am calling this function from a html file to populate a drop down list. So:

Code.gs

function getTheStuffINeed() {

  // Get data from sheet
  var ss =  SpreadsheetApp.openById("ss_id");
  var sheet = ss.getSheetByName("sheetname");
  var myStuff = sheet.getRange(2, 1, sheet.getLastRow()-1, 3).getValues();

  Logger.log(Array.isArray(myStuff));  // true. 
  Logger.log(myStuff); // [[id1, aVal, aSecondVal], [id2, AnotherVal, anotherSecondVal]] 

  return myStuff ; // An array of arrays
}

index.html

<script>
  // get data
  var myStuff= <?= getTheStuffINeed() ?>;  

  console.log(Array.isArray(myStuff)); // false
  console.log(myStuff); // id1, aVal, aSecondVal, id2, AnotherVal, anotherSecondVal, etc
</script>

When my result hits the html page it is no longer an array of arrays, rather a single string of comma separated values.

Can someone please explain what is happening here, and how to fix? Thank you.

Harold · Accepted Answer · 2016-08-16 11:53:06Z

2

here is a fix
on the client you need to write:

var myStuff= JSON.parse(<?= getTheStuffINeed() ?>);

on the server:

  return JSON.stringify(myStuff) ; // An array of arrays

answered Aug 16, 2016 at 11:53

Harold

3,3671 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3075569 · Accepted Answer · 2016-08-16 16:44:50Z

1

When using the HTML Service: Templated HTML, the code you write is not executed directly, the server evaluates the template and returns an HtmlOutput object, that the script can serve to the user.

Printing scriptlets execute code and append new content to the HtmlOutput using contextual escaping to avoid accidentally allowing a cross site scripting (XSS) bug, so the result is a safe String with no markup that can cause unexpected code execution.

You can:

Convert the array to a JSON string using the JSON.stringify() method and then parse the String as JSON with the JSON.parse() method as mentioned by Harold.
Populating the select asynchronously:

index.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>
// The code in this function runs when the page is loaded.
$(function() {
  google.script.run.withSuccessHandler(populateStuff)
      .getTheStuffINeed();
});

function populateStuff(stuff) {
  var options = '';
  for (var i = 0; i < stuff.length; i++){
    options += '<option id="'+stuff[i][0]+'" value="'+ stuff[i][1] + '">' + stuff[i][2] + '</option>';
  }
  $('#select').append(options);
}
</script>

Create and populate the select in the template (NOT following the best practices).

index.html

<select>
  <?
  var myStuff= getTheStuffINeed(); 
    for (var i = 0; i < myStuff.length; i++) { ?>
      <option id="<?= myStuff[i][0] ?>" value="<?= myStuff[i][1] ?>"><?= myStuff[i][2] ?></option>
  <? } ?>
</select>

answered Aug 16, 2016 at 16:44

user3075569

2 Comments

spryce Over a year ago

Brilliant. The async touch is going to work perfect for some other tasks I have also. Thanks for the detailed explaination.

user3075569 Over a year ago

Happy to help (: For more information about the asynchronous client-side JavaScript API take a look at the official documentation.

Collectives™ on Stack Overflow

Google Apps Script is changing my Array into a string. Why?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related