google.script.run returns undefined array

Question

I am trying to return an array from my server side function getPrepList(). When accessing from the client side 'prepList[]' returns 'undefined'. This is just a snippet of the full code.gs file.

//< code.gs >//

function getPrepList(){
   var sheet = SpreadsheetApp.getActiveSpreadsheet();
   var doPrepRange = sheet.getRange('F:F');
   var itemNameRange = sheet.getRange('A:A');
   var prepList = new Array();

    for(var i = 1; i < getFirstEmptyRow(); i++){
      if(doPrepRange.getCell(i,1).getValue() == true){
        prepList.push(itemNameRange.getCell(i, 1).getValue());
      };
    };
    Logger.log(prepList);
   return(prepList);
 };


function doGet(){
  return HtmlService.createHtmlOutputFromFile('index');
};

// < index.html > //

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1><div align="center">PREP LIST MAKER v 2.0</h1></div>

    <button align="center" onClick="createCountSheet()">Nightly Count Sheet</button><br />
    <button align="center" onClick="showPrepList()">Get Prep List</button><br />

    <script>

    console.info("before execution");
    function createCountSheet(){
      google.script.run
        .generateCountSheet();
     };

     google.script.run 
       .withSuccessHandler(showPrepList)
       .getPrepList();

    function showPrepList(prepList){
      console.log(prepList);
    };

     </script>
  </body>
</html>

And what types of data do you expect to find in the return value? How much data? Have you reviewed the allowed types? — tehhowch
– tehhowch, Commented Nov 28, 2018 at 18:58
I am returning all string primatives which are allowed per Google's documentation. The return size depends on the how many rows in the decalred ranges are returned 'true'. — wijohnst
– wijohnst, Commented Nov 28, 2018 at 19:34
Consider stringifying large data before sending, and then parsing after receiving: return JSON.stringify(myData); .... myData = JSON.parse(receivedInput); Also note that your server code is horrendously slow due to repeated use of the Spreadsheet Service (you call getValue and getCell in a loop when you could just work with an in-memory array). — tehhowch
– tehhowch, Commented Nov 28, 2018 at 20:42

TheMaster · Accepted Answer · 2018-11-29 01:12:48Z

1

If you're calling showPrepList(prepList) from a button,

<button align="center" onClick="showPrepList()">Get Prep List</button><br />

prepList will be undefined, because you aren't passing any argument as prepList.

Try,

<button align="center" onClick="showPrepListFromSheet()">Get Prep List</button><br />

<script>

console.info("before execution");
 function showPrepListFromSheet(){
 google.script.run 
   .withSuccessHandler(showPrepList)
   .getPrepList();
function showPrepList(prepList){
  console.log(prepList);
};
}</script>

To read:

answered Nov 29, 2018 at 1:12

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

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

Comments

Cooper · Accepted Answer · 2018-11-29 02:25:24Z

1

Try it this way:

function getPrepList(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();;
  var rg=sh.getDataRange();
  var vals=rg.getValues();
  var prepList=[];
  for(var i=1;i<vals.length;i++){
    if(vals[i][5]){
      prepList.push(vals[i][0]);
    }
  }
  return prepList;
}

It's not clear to me what sheet you wanted so I just picked the active one. I assumed you have a header row so I started the loop at one instead of zero. By the way I did not test this so you may have to tweak it a little.

edited Nov 29, 2018 at 2:25

answered Nov 28, 2018 at 19:40

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

2 Comments

wijohnst Over a year ago

Thank you for taking the time to answer. I am not having a problem with anything in ' code.gs'. The getPrepList function, when running server side, returns the correct values. The problem I have been having is with retrieving the array client side via 'google.script.run'. My intuition (and very limited expereince) is that this is some kind of "async" issue that I tried to remedy with the 'withSuccessHandler' callback function in 'index.html'.

Cooper Over a year ago

There are restrictions on what you can passed between client and server you might wanna check them in the guide section

Collectives™ on Stack Overflow

google.script.run returns undefined array

2 Answers 2

To read:

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

To read:

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related