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.

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.

• javascript
• jquery
• html
• arrays
• google-apps-script

Share Improve this question Follow edited May 28, 2014 at 12:03 Cerbrus 73k1919 gold badges136136 silver badges150150 bronze badges asked May 28, 2014 at 11:59 Marcel NabMarcel Nab 3111 silver badge22 bronze badges 3

• 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 Commented May 28, 2014 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 Commented May 28, 2014 at 12:35
• Possible duplicate of Chart data exported to an Apps Script webapp is null – tehhowch Commented Mar 11, 2019 at 13:15

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 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.

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);
});