3

We have a problem. The code we have now works, but we can't get the array to return on the html page. We are sending a number from the html page, and then the returnData function occurs. While it goes, it goes through loopsheetGewoon, and then it returns an array. The array is returned to returnData, and the function ends with the returnal of the array.

The problem is, that when we try to get the array to the html page so we can get individual data from the spreadsheet, it returns undefined. We have also tried passinga javascript value to google apps script code, but that didn't work.

function loopSheetGewoon(data, studentNr){
    var SpreadSheetKeyA = "1h8fDwCUUPHRdmTHu-5gMyqU294ENZxCZcHCNCuN6r_Y";
    var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getActiveSheet();
    var array = [];
    for (var y = 0; y < data.length; ++y ) {
        var datum = Utilities.formatDate(data[y][0], "CET", "dd-MM-yyyy hh:mm");
        var nummer = data[y][2];
        if(studentNr.equals(nummer)){
            for (var x = 0; x < 35; x++ ) {
                array.push(data[y][x]);
            }
            return array;        
        }    
    }
}

Below is the function that is initiated.

function returnData(stuNr){
    var SpreadSheetKeyA = "1h8fDwCUUPHRdmTHu-5gMyqU294ENZxCZcHCNCuN6r_Y";
    var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getActiveSheet();
    var data = sheet1.getDataRange().getValues();
    var array = [];
    array = loopSheetGewoon(data, stuNr);
    Logger.log(array);
    return array;
}

Below is the function we are running from the html page. When a button is clicked, it sends a number to the returnData function ( Which is stored as a string value). It should now fill up the variable d with the array, however it keeps returning undefined.

$("#oph").click(function ophalen(){
    var s = $("#nummer").val();
    var displayEl = document.getElementById("nummer");
    var d = [];
    alert(s);
    d = google.script.run.returnData(s);

    //for( var y = 0; y < d.length(); ++y){
        //var naam = data[y][1];
        //var studentnr = data[y][2];
        //var document = data[y][32];
    //}
    alert(d);
    var div = document.getElementById('block');
    div.innerHTML = naam;
});

So I am not sure what we are doing wrong, since the function itself works fine, up to the point until it returns to the html page.

Cerbrus
  • 70,800
  • 18
  • 132
  • 147
Marcel Nab
  • 31
  • 1
  • 2
  • Should not `studentNr.equals(nummer)` raise exception?.. Javascript String objects have no `equals` function. Also, do you get a meaningful value in `data` variable in your `returnData` function? – pckill May 28 '14 at 12:30
  • @pckill I get no exception there, unless the spreadsheet by itself saves it to an int value, but that seems weird. The data variable is just all data in the current spreadsheet, which it passes on to the next function along with the number. Th function which executes after works fine, because `Logger.log(array)` shows me the array I want before I return the array. – Marcel Nab May 28 '14 at 12:35
  • Possible duplicate of [Chart data exported to an Apps Script webapp is null](https://stackoverflow.com/questions/50686187/chart-data-exported-to-an-apps-script-webapp-is-null) – tehhowch Mar 11 '19 at 13:15

2 Answers2

7

Unfortunately, one of the documented limitations of google.script.run (still valid as of March 2019) is that you can't pass a Date object in any way, including as part of an array.

You can either call getDisplayValues() instead of getValues() on a range to only fetch an array of Strings to begin with, or you can convert Date objects to Strings by processing the getValues() array.

Vishnar Tadeleratha
  • 321
  • 3
  • 4
0

google.script.run: Return void — this method is asynchronous and does not return directly; however, the server-side function can can return a value to the client as a parameter passed to a success handler; also, return types are subject to the same restrictions as parameter types, except that a form element is not a legal return type

You should be doing it like this:

var onSuccess = function(d){
    //for( var y = 0; y < d.length(); ++y){
        //var naam = data[y][1];
        //var studentnr = data[y][2];
        //var document = data[y][32];
    //}
    alert(d);
    var div = document.getElementById('block');
    div.innerHTML = naam;
}

$("#oph").click(function ophalen(){
    var s = $("#nummer").val();
    var displayEl = document.getElementById("nummer");
    var d = [];
    alert(s);
    google.script.run.withSuccessHandler(onSuccess).returnData(s);
});
pckill
  • 3,709
  • 36
  • 48
  • Doesn't seem to be working. The Onclick happens, the functions are running but it doesn't seem to alert. If you put a variable ` var g =google.script.run.withSuccesHandler(onSucces).returnData(s);`, and you alert it, it will return undefined. I'm not sure why it isn't working though. – Marcel Nab May 28 '14 at 13:03
  • Quoting my quote - "this method is asynchronous". Your `g` variable is expected to be undefined. Can you check the network tab in chrome developer tools to see what is the response of the server? – pckill May 28 '14 at 13:12