Error when returning an array from google script to a function in website

Question

When update is called it runs a script called update as you can see here in the following code snipits:

within the HTML:

function updDate(date){
   console.log("update called");

      google.script.run.withSuccessHandler(myReturnFunction).updDate(date);
      //Crossover.libTest();
      console.log("crossover should have been called");

      }
      function myReturnFunction(garbage){

     console.log("Returned:  "+ garbage);

       /*for (var i = 0; i < whatGotReturned.length; i++) {
             document.getElementById("field_"+[i]).innerHTML = whatGotReturned[i];
           }*/
         }

Within the Google Script:

function updDate(date){

  var searchString = date;
  var s2 = SpreadsheetApp.openById("0AnDrS3YbyWoLdC15aktnMTluUkQ3dWhNTFlyQXBPOFE");
  var data = s2.getActiveSheet().getDataRange().getValues();


  var k;


  for (var i in data) {
    for (var j in data[i]) {
      if (i !=0){
        if(data[i][j] != ""){
          if(j == 4){
            var ssDate = Utilities.formatDate(data[i][j], "GMT", "MM/dd/yyyy");

            if(date == ssDate){
              k= i
              var p = data[i].length


            }//conclude if
          }//conclude if (j==4)

        }//conclude if(data[i][j] != "")
        }//conclude if (data[i][j] != "")
      } //conclude for (var j in data[i])
    }//conclude for (var i in data)


  //Logger.log("K is:  "+k)
  var valuesToSnd = new Array();

  var q = 1
  while (q <= p){


    //if(data[k][q] !=""){
      valuesToSnd[q] = data[k][q];
    //document.getElementById("field_"+[q]).innerHTML = data[k][q];

    //}


    q++

  }
  Logger.log("Values to Send:  "+valuesToSnd);
      return valuesToSnd;


  }

The google script will log the array like it should:

[14-08-26 08:47:03:950 EDT] Values to Send: ,BABS only at AFM,,BABS,Fri Aug 08 2014 00:00:00 GMT-0400 (EDT),No Unload Necissary,https://docs.google.com/forms/d/1yBiXtjTpXs85BNs5Lsj0zGQtDtxghm6-e2lRebB4saw/viewform?edit=ChM1NzYwNDkwNTY0Nzg2NjY5MTI4EJKj-OTR3ejNhwE,,https://docs.google.com/document/d/15f2HjJjNTxjlLZOkB2BOe8nY9ZlImmaAaSSnhViAa4I/edit,,Thu Aug 07 2014 15:30:00 GMT-0400 (EDT),Sat Dec 30 1899 14:30:00 GMT-0500 (EST),Sat Dec 30 1899 15:00:00 GMT-0500 (EST),Sat Dec 30 1899 18:45:00 GMT-0500 (EST),5 Ton 35 ,No,No,,,,,All Singers,Not needed,,Needed,Needed,,,,https://docs.google.com/spreadsheet/ccc?key=0AnDrS3YbyWoLdDdSel9RVVNVMGhDakwxMmdueGc4WGc&usp=drive_web#gid=4,,No,AFM,https://www.mediafire.com/folder/dp788cvtbexsf/,

Somewhere after the script runs the console returns this error:

Uncaught ScriptError: The script completed but the returned value is not a supported return type.

Is there something wrong with the code or is there a character in the array that the function doesn't "like"?

Thanks,

Loren

This works:

  var valuesToSnd = new Array();

  var q = 0
  while (q <= p){

    //if(q!=0){

      if(data[k][q] !=""){

        if (q==11){
          var trudeptTime = Utilities.formatDate(data[k][q], Session.getScriptTimeZone(), "HH:mm");
          valuesToSnd[q] = trudeptTime;
        } 
        else if(q==12){

          var lITime = Utilities.formatDate(data[k][q], Session.getScriptTimeZone(), "HH:mm");
          valuesToSnd[q] = lITime;
        }
        else if (q==13){
          var sCTime = Utilities.formatDate(data[k][q], Session.getScriptTimeZone(), "HH:mm");
          valuesToSnd[q] = sCTime;
        }else{

      valuesToSnd[q] = q+ ":  "+ data[k][q];
        }


      }else{

        //valuesToSnd[q]= q + "Nothing";
      }      
    /*}else{
      valuesToSnd[q]= q+ ":  "+ data[k][q];
    }*/
    q++

  }
  Logger.log("Values to Send:  "+valuesToSnd);
  return valuesToSnd;

  }

This doesn't:

  var valuesToSnd = new Array();

  var q = 0
  while (q <= p){

    //if(q!=0){

      if(data[k][q] !=""){

        if (q==11){
          var trudeptTime = Utilities.formatDate(data[k][q], Session.getScriptTimeZone(), "HH:mm");
          valuesToSnd[q] = trudeptTime;
        } 
        else if(q==12){

          var lITime = Utilities.formatDate(data[k][q], Session.getScriptTimeZone(), "HH:mm");
          valuesToSnd[q] = lITime;
        }
        else if (q==13){
          var sCTime = Utilities.formatDate(data[k][q], Session.getScriptTimeZone(), "HH:mm");
          valuesToSnd[q] = sCTime;
        }else{

      valuesToSnd[q] = data[k][q];
        }


      }else{

        //valuesToSnd[q]= q + "Nothing";
      }      
    /*}else{
      valuesToSnd[q]= q+ ":  "+ data[k][q];
    }*/
    q++

  }
  Logger.log("Values to Send:  "+valuesToSnd);
  return valuesToSnd;

  }

Following some comments below here is a log following a tweak or two:

14-08-27 12:51:51:572 EDT] k:  26 q:  0Timestamp data:  Mon Jul 14 2014 11:08:15 GMT-0400 (EDT)
[14-08-27 12:51:51:573 EDT] k:  26 q:  1Gig Name data:  BABS only at AFM
[14-08-27 12:51:51:573 EDT] k:  26 q:  2Program Name data:  
[14-08-27 12:51:51:573 EDT] k:  26 q:  3Groups Involved data:  BABS
[14-08-27 12:51:51:574 EDT] k:  26 q:  4Gig Date data:  Fri Aug 08 2014 00:00:00 GMT-0400 (EDT)
[14-08-27 12:51:51:574 EDT] k:  26 q:  5Notes data:  No Unload Necissary
[14-08-27 12:51:51:574 EDT] k:  26 q:  6Gig Coordination Form Link data:  https://docs.google.com/forms/d/1yBiXtjTpXs85BNs5Lsj0zGQtDtxghm6-e2lRebB4saw/viewform?edit=ChM1NzYwNDkwNTY0Nzg2NjY5MTI4EJKj-OTR3ejNhwE
[14-08-27 12:51:51:575 EDT] k:  26 q:  7Announcer Name data:  
[14-08-27 12:51:51:575 EDT] k:  26 q:  8Program Notes Link data:  https://docs.google.com/document/d/15f2HjJjNTxjlLZOkB2BOe8nY9ZlImmaAaSSnhViAa4I/edit
[14-08-27 12:51:51:575 EDT] k:  26 q:  9Jump to page data:  
[14-08-27 12:51:51:576 EDT] k:  26 q:  10Truck Load data:  Thu Aug 07 2014 15:30:00 GMT-0400 (EDT)
[14-08-27 12:51:51:576 EDT] k:  26 q:  11Truck Departure Time data:  Sat Dec 30 1899 14:30:00 GMT-0500 (EST)
[14-08-27 12:51:51:577 EDT] k:  26 q:  12Load In Time data:  Sat Dec 30 1899 15:00:00 GMT-0500 (EST)
[14-08-27 12:51:51:578 EDT] k:  26 q:  13Sound Check Time data:  Sat Dec 30 1899 18:45:00 GMT-0500 (EST)
[14-08-27 12:51:51:579 EDT] k:  26 q:  14Trucks to be used data:  5 Ton 35 
[14-08-27 12:51:51:579 EDT] k:  26 q:  15LRS Support Required data:  No
[14-08-27 12:51:51:579 EDT] k:  26 q:  16LRS Request submitted data:  No
[14-08-27 12:51:51:580 EDT] k:  26 q:  17Date Request submitted to Band VCO/VNCO data:  
[14-08-27 12:51:51:580 EDT] k:  26 q:  18LRS Confirmation Number data:  
[14-08-27 12:51:51:581 EDT] k:  26 q:  19Unload Truck data:  
[14-08-27 12:51:51:581 EDT] k:  26 q:  20Jump to page data:  
[14-08-27 12:51:51:581 EDT] k:  26 q:  21BABS Crew Members data:  All Singers
[14-08-27 12:51:51:581 EDT] k:  26 q:  22BABB Crew Members data:  Not needed
[14-08-27 12:51:51:582 EDT] k:  26 q:  23Jump to page data:  
[14-08-27 12:51:51:582 EDT] k:  26 q:  24Input List data:  Needed
[14-08-27 12:51:51:583 EDT] k:  26 q:  25Pack List data:  Needed
[14-08-27 12:51:51:583 EDT] k:  26 q:  26Pack list Link (If Applicable) data:  
[14-08-27 12:51:51:583 EDT] k:  26 q:  27Singers Standing arrangment data:  
[14-08-27 12:51:51:583 EDT] k:  26 q:  28Jump to page data:  
[14-08-27 12:51:51:584 EDT] k:  26 q:  29Input List Link (if Applicable) data:  https://docs.google.com/spreadsheet/ccc?key=0AnDrS3YbyWoLdDdSel9RVVNVMGhDakwxMmdueGc4WGc&usp=drive_web#gid=4
[14-08-27 12:51:51:584 EDT] k:  26 q:  30 data:  
[14-08-27 12:51:51:584 EDT] k:  26 q:  31Singers Stands required data:  No
[14-08-27 12:51:51:585 EDT] k:  26 q:  32Gig Location data:  AFM
[14-08-27 12:51:51:585 EDT] k:  26 q:  33Media Link data:  https://www.mediafire.com/folder/dp788cvtbexsf/

You originally had a condition in your code: if(data[k][q] !=""){ I'd put a condition back in, and push a dummy value into the array. if(data[k][q] !=""){valuesToSnd[q] = "Empty";} else {valuesToSnd[q] = data[k][q];}; — Alan Wells
– Alan Wells, Commented Aug 26, 2014 at 17:47
@SandyGood I thought that your suggestion would work but Alas it did not. — Loren Zimmer
– Loren Zimmer, Commented Aug 26, 2014 at 17:53
I'm wondering if you hard code what you think the array should be, and try that, and see what the result is, it might give you another clue. Eliminate what the problem can't be to narrow it down. — Alan Wells
– Alan Wells, Commented Aug 26, 2014 at 17:56
@SandyGood something is definately up with the array. See above for the code that works and what doesn't. When I simply add the number of the row to the front of the value the code works. When I take the row number away it doesn't. — Loren Zimmer
– Loren Zimmer, Commented Aug 27, 2014 at 13:33
I see now that my original suggestion was backwards. Your code that works, makes sure that there is always some data in the array element. Even if data[k][q] evaluates to something other than a value, at least the element gets a value of q:. Try putting a Logger.log(k + " : " + q + " : " + data[k][q]); statement in the while loop, to determine if there is some unexpected return. — Alan Wells
– Alan Wells, Commented Aug 27, 2014 at 15:33

aochiu · Accepted Answer · 2016-02-15 15:30:47Z

4

I think date / time values are causing the error.

Consider changing all your date/time to string or JSON:

if (data[k][q] instanceof Date) data[k][q] = data[k][q].toJSON();

This seemed to work for me.

answered Feb 15, 2016 at 15:30

aochiu

413 bronze badges

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

1 Comment

WhiteHotLoveTiger Over a year ago

How do I convert my .toJSON() date back to a date?

Collectives™ on Stack Overflow

Error when returning an array from google script to a function in website

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related