Dates are not being passed in from Google Scripts to JavaScript

Question

I have a problem where my Google Script function is not passing in date values to my JavaScript. Text and numerical values however are being passed in. The Google Scripts function searches my google sheets document to find a row of values based on a value that is passed into it. It then takes the data, puts it into an array and ships it over to my JavaScript function. The JavaScript then assigns the values from the array to my HTML document.

Here is my JavaScript:

  function callDataRetriever(){
    var number = document.getElementById("number").value;
    google.script.run.withSuccessHandler(dataRetriever).retreiveData(number);
  }

  function dataRetriever(data){
    document.getElementById("location").value = data[0]; //This works
    document.getElementById("dateOpened").value = data[1]; //This does not work. Stops the function from continuing its task.        
    document.getElementById("value1").value = data[2]; //Without the date everything here down works
    document.getElementById("value2").value = data[3];
    document.getElementById("value2").value = data[4];
    document.getElementById("value4").value = data[5];
    //...
  }

Here is my Google Scripts:

function retreiveData(number){
  var url = "urlToSpreadsheet";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  var data = ws.getRange(1,1, ws.getLastRow(), ws.getLastColumn()).getValues();
  var dataValues = [];
  var filterData = data.filter(
    function(r){
      if(r[2] == number){
        var i = 3;
        while(i < 29){
          dataValues.push(r[i]);  
          i++;
        }
      }
    }
  )
  return dataValues;
}

In my Logs this is how it looks:

It is grabbing the date correctly however once passed into my JavaScript the function ceases to continue.

UPDATE: Edited code based on doubleunary's suggestion. Now getting an error that I do not fully understand:

Try using .getDisplayValues() at the server-side and see if that works? Also check out this link. — JPV
– JPV, Commented May 14, 2021 at 13:55

doubleunary · Accepted Answer · 2021-05-14 17:45:21Z

3

You cannot pass a Date object but will have to serialize it before sending. From the documentation:

Legal parameters and return values are JavaScript primitives like a Number, Boolean, String, or null, as well as JavaScript objects and arrays that are composed of primitives, objects and arrays. A form element within the page is also legal as a parameter, but it must be the function’s only parameter, and it is not legal as a return value. Requests fail if you attempt to pass a Date, Function, DOM element besides a form, or other prohibited type, including prohibited types inside objects or arrays. Objects that create circular references will also fail, and undefined fields within arrays become null.

Try using Utilities.formatDate(myDate, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), 'yyyy-MM-dd') in the server side and new Date(data[1]) in the client side.

edited May 14, 2021 at 17:45

answered May 14, 2021 at 14:29

doubleunary

22k13 gold badges38 silver badges85 bronze badges

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

2 Comments

Ramy CoreMT Over a year ago

I attempted this and it did not work, did I do it wrong? (See my edit in the post above)

doubleunary Over a year ago

The formatDate() call is missing the timezone parameter.

Ramy CoreMT · Accepted Answer · 2021-05-14 17:28:54Z

0

It seemed all I needed to do was add document.getElementById("dateValue").valueAsDate = new Date(data[1]); in my JavaScript and leave my Google-Script alone.

answered May 14, 2021 at 17:28

Ramy CoreMT

831 gold badge1 silver badge9 bronze badges

Collectives™ on Stack Overflow

Dates are not being passed in from Google Scripts to JavaScript

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related