Operator '<' cannot be applied to types 'number' and 'Promise<void>' with an Excel Add-In

Question

I am making an Excel add-in and I want to convert the data of a spreadsheet in Excel into a 2D array in Javascript but I can't get data to convert and I have been unable to figure out how to fix it, how do I fix the promise so that the function returns a number. The issue also exists for the getData function.

This is on the taskpane.js for the Excel add-in. I have tried .then(), await before the variable, and let, but I couldn't get it to work.

import { get } from "http";

/*
 * Copyright (c) Microsoft Corporation. All rights reserved. Licensed under the MIT license.
 * See LICENSE in the project root for license information.
 */

import { get } from "http";

/*
 * Copyright (c) Microsoft Corporation. All rights reserved. Licensed under the MIT license.
 * See LICENSE in the project root for license information.
 */

Office.onReady(info => {
  // Determine if the user's version of Office supports all the Office.js APIs that are used in the tutorial.
  if (!Office.context.requirements.isSetSupported('ExcelApi', '1.7')) {
    console.log('Sorry. The tutorial add-in uses Excel.js APIs that are not available in your version of Office.');
  }

  // Assign event handlers and other initialization logic.
  document.getElementById("run").onclick = run;
  if (info.host === Office.HostType.Excel) {
    document.getElementById("sideload-msg").style.display = "none";
    document.getElementById("app-body").style.display = "flex";
  }
});

function Create2DArray(rows) {
  var arr = [];

  for (var i = 0; i < rows; i++) {
    arr[i] = [];
  }

  return arr;
}

function sortedData() {
  var rows = getRowCount();
  var arrImport = getData();
  var arrExport = Create2DArray(rows);
  for (var r = 0; r < rows; r++) {
    arrExport[r][0] = arrImport[r][1];//money
    arrExport[r][1] = arrImport[r][5];//Company
    arrExport[r][2] = arrImport[r][0];//date
  }
  return arrExport;
}

async function getRowCount() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    var rowCount = 0;

    for (var i = 0; i >= 0; i++) {
      var cell = sheet.getCell(i, 0);
      cell.load("address, values");
      await context.sync();
      if (cell.values[0][0] == "") {
        break;
      }
      else {
        rowCount++;
      }
    }
    await context.sync();
    return rowCount;
  });
}

async function getData() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    //let rowCount = await getRowCount();
    var rowCount = getRowCount();
    var arrExport = Create2DArray(rowCount);

    for (var r = 0; r < rowCount; r++) {
      for (var c = 0; c < 5; c++) {
        var cell = sheet.getCell(r, c);
        cell.load("address, values");
        await context.sync();
        arrExport[r][c] = cell.values[0][0];
      }
    }
    await context.sync();
    return arrExport;
  })
}

async function run() {
  await Excel.run(async (context) => {
      var currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
      rows = getRowCount();
  })
  .catch(function (error) {
      console.log("Error: " + error);
      if (error instanceof OfficeExtension.Error) {
          console.log("Debug info: " + JSON.stringify(error.debugInfo));
      }
  });
}

I want to get the number of rows from my excel spreadsheet that are used but I can't get it to work and I can't figure out async. The error is the title.

Answer 1

First issue, since getRowCount is async, it returns a Promise

Second issue, getRowCount doesn't actually return anything

async function getRowCount() {
    let returnValue;
    await Excel.run(async(context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        var rowCount = 0;

        for (var i = 0; i >= 0; i++) {
            var cell = sheet.getCell(i, 0);
            cell.load("address, values");
            await context.sync();
            if (cell.values[0][0] == "") {
                break;
            } else {
                rowCount++;
            }
        }
        await context.sync();
        returnValue = rowCount;
    });
    return returnValue;
}

There may be a better way to do the above, but I don't know the workings of this Excel.run etc, so but as long as Excel.run is returning a Promise - which I assume because you use await on it - then the above should work

and, usage in your code would be

async function getData() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    let rowCount = await getRowCount();
    var arrExport = Create2DArray(rowCount);

    for (var r = 0; r < rowCount; r++) {
    .
    .
    .

Should now work