Node.js Function returning undefined?

Question

Trying to find a value in an excel file using the XLSX library:

The function works, it finds the value, however the output is undefined, even though the debugging say the value is found.

Here's the function:

var getValsFromExcel = function(sheet,idcol, valcol, val){
  var workbook = new Excel.Workbook(); 
  workbook.xlsx.readFile(__dirname + '/assets/gu.xlsx')
      .then(function() {
          var worksheet = workbook.getWorksheet(sheet);
          worksheet.eachRow({ includeEmpty: false }, function(row, rowNumber) {
            console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
            console.log(row.values[idcol]);
            console.log('checking ' + row.values[idcol] + ' = ' + val + ' ' + (row.values[idcol] == val))
            if (row.values[idcol] == val){
              console.log('Value found! its ' + row.values[valcol])
              //getValsFromExcel = row.values[valcol];
              return row.values[valcol];
            }
          });
      });
    }  

  var ans = getValsFromExcel('yesno',3, 4, tobj["respondent_consent"]);
  console.log('Q1 answer = ' + ans);

Here's the console output:

Q1 answer = undefined
Row 1 = [null,"UID","Delete(Y/N)","field: yesno_key_value","field: yesno_display_text"]
field: yesno_key_value
checking field: yesno_key_value = yes false
Row 2 = [null,"5b45fe42f7fe481d8442d5e94b894b45","N","yes","Si"]
yes
checking yes = yes true
Value found! its Si
Row 3 = [null,"b65ba5a1a3814a87b4571e8d477307aa","N","no","No"]
no
checking no = yes false

farvilain · Accepted Answer · 2020-11-28T11:44:03.717

getValsFromExcel asynchronous, here is the correction:

var getValsFromExcel = function(sheet,idcol, valcol, val){
  var workbook = new Excel.Workbook(); 
  return workbook.xlsx.readFile(__dirname + '/assets/gu.xlsx')
  .then(function() {
      var worksheet = workbook.getWorksheet(sheet);
      let answer = null;
      worksheet.eachRow({ includeEmpty: false }, function(row, rowNumber) {
        console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
        console.log(row.values[idcol]);
        console.log('checking ' + row.values[idcol] + ' = ' + val + ' ' + (row.values[idcol] == val))
        if (row.values[idcol] == val){
          console.log('Value found! its ' + row.values[valcol])
          //getValsFromExcel = row.values[valcol];
          answ = row.values[valcol];
          return;
        }
      });
      return answer;
  });
}  

getValsFromExcel('yesno',3, 4, tobj["respondent_consent"])
.then( answer => console.log('Q1 answer = ' + ans) );

Sorry I just corrected the first mistake, I'll try to understand what it is supposed to do and fix the second (returning the answer) — farvilain, Nov 28 '20 at 11:42

Matthew Brooks · Answer 2 · 2020-11-28T13:19:40.410

There are a couple things going on here. First, you're not returning any value from getValsFromExcel, so doing var ans = getValsFromExcel() will always be undefined (the default return value of any function).

But even if you do return workbook.xlsx.readFile(__dirname + '/assets/gu.xlsx').then() // ... you won't get the value you have in console.log('Value found! its ' + row.values[valcol]), because workbook.xlsx.readFile returns a Promise.

If you're on a recent version of Node, you can add that return before your readFile call, and then do this

async function main() {
  var ans = await getValsFromExcel()
  console.log(ans)
}

main()

Edit: sorry for the half answer initially. I don't like the SO editor and apparently I smashed a key combo that prematurely saved it.

Here's another solution showing this without async/await. Remember: async/await is just Promises with special syntax, but it works the same.

getValsFromExcel(/* args */)
  .then((value) => {
    console.log(value)
  })

This is the same thing. We either have to await the Promise, or we have to chain a then with a callback that will be invoked with the value you return in your Promise chain in the getValsFromExcel function.

There were a number of bugs in the original code... here's a further breakdown for completeness' sake:

const Excel = require("exceljs")

var getValsFromExcel = function (sheet, idcol, valcol, val) {
  var workbook = new Excel.Workbook()
  return workbook.xlsx
    .readFile(__dirname + "/assets/gu.xlsx")
    .then(function () {
      var worksheet = workbook.getWorksheet(sheet)
      // let's initialize with some value.
      // undefined would work as well, but we can be explicit if it's not found
      // and make it null.
      let result = null

      worksheet.eachRow({ includeEmpty: false }, function (row, rowNumber) {
        if (row.values[idcol] == val) {
          // ok now we reassign result to the value we want
          result = row.values[valcol]
        }
      })
      // In order to have access to the value in the next `then`
      // of your Promise chain, you _must_ return it.
      return result
    })
    .then((value) => {
      console.log("Value = " + value)
    })
}

// alternate version using async/await

var getValsFromExcel = async function (sheet, idcol, valcol, val) {
  var workbook = new Excel.Workbook()
  // wait for the asynchronous code to resolve
  await workbook.xlsx.readFile(__dirname + "/assets/gu.xlsx")
  // after this point, workbook has been mutated and now contains the file's data

  var worksheet = workbook.getWorksheet(sheet)

  let result = null

  worksheet.eachRow({ includeEmpty: false }, function (row, rowNumber) {
    if (row.values[idcol] == val) {
      result = row.values[valcol]
    }
  })

  console.log("Value = " + result)
}

getValsFromExcel("Sheet1", 2, 2, "Dulce")

https://www.brcline.com/blog/how-to-write-an-excel-file-in-nodejs#:~:text=%20How%20to%20Write%20Data%20to%20An%20Excel,example%20doesn%E2%80%99t%20really%20have%20too%20much...%20More%20 — R0b0tn1k, Nov 28 '20 at 12:52
Great, and are you using 3.9.0 as specified in that tutorial, or are you using the latest version, which is 4.2.0? Or something else? — Matthew Brooks, Nov 28 '20 at 13:01

Node.js Function returning undefined?

2 Answers2