SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data on App Script web app

Question

I am attempting to create an unordered list on my app script web app by pulling an array from a range on a google sheet.

I have a function in a gs file that works properly when used within the google sheet itself:

function listTest(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var invSheet = sheet.getSheetByName('Inventory');
var values = invSheet.getRange(2, 3, 25).getValues();
return JSON.stringify(values);
}

I have a javascript function that I am trying to implement based on the answer given here: Create a <ul> and fill it based on a passed array

my code is pretty much exactly this, minus the options variable:

function makeUL(array) {
// Create the list element:
var list = document.createElement('ul');

for(var i = 0; i < array.length; i++) {
    // Create the list item:
    var item = document.createElement('li');

    // Set its contents:
    item.appendChild(document.createTextNode(array[i]));

    // Add it to the list:
    list.appendChild(item);
}

// Finally, return the constructed list:
return list;
}

I ran a simplified version of it with just one list item, and it works:

function makeList() {
var list = document.createElement('ul');
var item = document.createElement('li');
item.appendChild(document.createTextNode("This is a test."));
list.appendChild(item);
return list;
}
document.getElementById("testDiv").appendChild(makeList());

However, when I try to pull the JSON array into the function to create the unordered list using the method in the link above, I get the error message: SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

My attempt at a function in my html to do this is:

    function createList() {
    var myJSON = google.script.run.listTest();
    var array = JSON.parse(myJSON);
    document.getElementById("testDiv").appendChild(makeUL(array));
    }
    createList();

I started off not using the JSON.stringify method in my listTest function. I was just ending with:

return values;

I was then getting the error 'array is undefined'. I'm think JSON is the way to go with this, but I'm stuck. Any help is appreciated.

What type of value does the data you are stringifying hold, can you post a console.log output for it? — Ossaija Thankgod, Feb 18 '18 at 03:25
Thanks for replying, @Thankgod. It is a range of data in a google sheet, column C2:C26. When it is stringyfied it becomes [[cell1].[cell2],[cell3],...etc]. I am not sure how to post a console.log output for it. — Matt W, Feb 18 '18 at 03:34
On your listTest function don't do JSON.stringify(values), just return the values. unless you are sending the data over the network there is no need to use JSON here — Ossaija Thankgod, Feb 18 '18 at 03:42
I am trying to send the contents of that values array to my web app for use as an array in another function. I think I have to use json in this case, right? — Matt W, Feb 18 '18 at 04:12

Chris · Accepted Answer · 2018-02-18T04:55:47.940

Check the HTML Documentation for google.script.run. When you call google.script.run.listTest(); it doesn't actually return anything. You have to use the Success Handler which will receive returned data as a parameter. This is why your array is undefined.

EDIT: Updated This hasn't been completely tested:

function listTest(){
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var invSheet = sheet.getSheetByName('Inventory');
    var values = invSheet.getRange(2, 3, 25).getValues();
    return values;
}

function createList() {
    google.script.run.withSuccessHandler(onSuccess)
        .listTest(); 
}

function onSuccess(dataFromListTest){
    document.getElementById("testDiv").appendChild(makeUL(dataFromListTest));
}

function makeUL(array) {
    var list = document.createElement('ul');

    for(var i = 0; i < array.length; i++) {
        var item = document.createElement('li');
        item.appendChild(document.createTextNode(array[i]));
        list.appendChild(item);
    }

    return list;
}

See a different, basic example here: Gist - Using withSuccessHandler to Return Data

EDIT: Here is another example of an HTML File that calls a script back in the main code and puts that into a list in a div. This has been tested.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      google.script.run.withSuccessHandler(onSuccess)
          .getSomeData(); //getSomeData() returns [["Cell 1"],["Cell 2"],["Cell 3"],["Cell 4"],["Cell 5"]] TO onSucess() AS A PARAMETER!

      function onSuccess(data) { 
        var div = document.getElementById('output');
        var list = document.createElement('ul');                 // Create a <li> node

        for(var i = 0; i < data.length; i++){
            var item = document.createElement('li');
            item.appendChild(document.createTextNode(data[i])); 
            list.appendChild(item);
        }
        div.appendChild(list);

      }
    </script>
  </head>
  <body>
    <div id="output">
    </div>
  </body>
</html>

See the full example here on Gist with the main code.

OK that makes sense. I read the Success Handler link, and I changed my code out for yours.Still no go though. I am no longer getting the error message I initially posted about, but I am still not getting the list on my web app. Any idea what else it could be? @Chris — Matt W, Feb 18 '18 at 04:02
Can you post the code that you are using for making the list using the data from the spreadsheet? (i.e. `makeUL()` that should be in your html file). — Chris, Feb 18 '18 at 04:15
Can you explain the `(function (data){ myJSON = data; })` portion? is data a built-in variable? I'm not understanding that part. Is it that the result of the `.listTest()` is passing its result as a parameter to the success handler? — Matt W, Feb 18 '18 at 04:26
Yes, the Success Handler takes a function and passes the results of the function you wanted to call to the success function as a parameter. — Chris, Feb 18 '18 at 04:37
@MattW I've update my answer with an example of what you are doing. The array doesn't need to be Stringified or parsed. Also see the first code block of my answer as I've update that to reflect your code. — Chris, Feb 18 '18 at 04:44
that worked perfectly! Thank you so much for your time. You are my App Script guru! — Matt W, Feb 18 '18 at 04:53

SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data on App Script web app

1 Answers1