I want to store data samples from an excel sheet into an array in javascript

I am using sheetJS in order to manipulate excel sheets. My goal is to extract the value of a cell and store it in an array as raw data for later statistical analysis and graphing.

Here is what the function looks like:

function getSheetData()
{
    
    let rawData = [];

    /* set up XMLHttpRequest */
    var url = "test.xlsx";
    var oReq = new XMLHttpRequest();
    oReq.open("GET", url, true);
    oReq.responseType = "arraybuffer";
    oReq.send();
    oReq.onload = function (e) {
        var arraybuffer = oReq.response;
        /* convert data to binary string */
        var data = new Uint8Array(arraybuffer);
        var arr = new Array();
        for (var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
        var bstr = arr.join("");
        
        /* Call XLSX */
        var workbook = XLSX.read(bstr, {
            type: "binary"
        });

        /* DO SOMETHING WITH workbook HERE */
        var sheet_name_list = workbook.SheetNames;
        // var worksheet;                  

        sheet_name_list.forEach(function(y) { /* iterate through sheets */
            var worksheet = workbook.Sheets[y];
        
            for (z in worksheet) {
                /* all keys that do not begin with "!" correspond to cell addresses */
                if(z[0] === '!') continue;
                    // console.log(z + " = " + JSON.stringify(worksheet[z].v));
                    rawData.push(worksheet[z].v);
               
            }
            
        });
        
        /* Get worksheet */
        // console.log(XLSX.utils.sheet_to_json(worksheet, {
        //     raw: true
        // }));



        console.log("raw data = " + rawData);
    }
   
// console.log(rawData);  
return rawData;
    
}

The console.log defined as ‘raw data’ shows all the numbers in one array just how I need it. However, the array named “rawData” returns as undefined by the end of the function.

I am calling the function here:

window.onload = function()
{
    const data = getSheetData();

    const BenfordTable = calculateBenford(data);

    printAsTable(BenfordTable);
    printAsGraph(BenfordTable);


}

I get data as an empty array

I have included a picture of the browser window

screenshot of console results in google chrome

Answer

data is an empty array because getSheetData() is an asynchronous function – that is to say, you are making an XMLHttpRequest call from within it. If you put console logs within your onload handler and right before your return statement, you will see that the latter runs first. The issue is that when your function returns, the call to the server will not have yet returned.

There are a few different ways of writing asynchronous code, but I think you should start off by passing a callback function to getSheetData() which will be called from within your onload handler. This callback function will be what handles rawData.

Here’s roughly how you might do this. I have omitted some of the existing code for brevity, but obviously you will need it.

function getSheetData(callback)
{
    
    let rawData = [];

    //  ...other code

    oReq.onload = function (e) {
        var arraybuffer = oReq.response;

        //  ...other code

        callback(rawData); //  <-- add this
        
    }
   
    // no need to return anything!
    //  return rawData;
    
}


window.onload = function()
{
    getSheetData(function (data) {
       const BenfordTable = calculateBenford(data);

       printAsTable(BenfordTable);
       printAsGraph(BenfordTable);

    });
}

There are other things you could use to write such code, such as Promises, but that’s probably something else to look into. We’re also not doing any error handling here, which is also an important concept. The main takeaway here is that you are only handling the rawData once the call to the server has completed.