I'm trying to pass variables between a chain of ".then()" calls like so:

    Excel.run(function (context) {
      var currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
      var table = currentWorksheet.tables.getItem("NewTable");
      table.rows.load('count')
      return context.sync()
          .then(function () {
              var rowCount = table.rows.count;
              console.log("There are "   rowCount   " rows in the table.");
              var firstColumn = table.columns.getItem(1);
              firstColumn.load("values");
              return context.sync();
          })
          .then(function () {
              var firstColumnValues = firstColumn.values;
              var summary = {};
              for (var i = 0; i < firstColumnValues.length; i  ) {
                  var value = firstColumnValues[i][0];
                  if (summary[value]) {
                      summary[value]  ;
                  } else {
                      summary[value] = 1;
                  }
              }
              console.log(summary);
          })
          .catch(function (error) {
              console.log("Error: "   error);
              if (error instanceof OfficeExtension.Error) {
                  console.log("Debug info: "   JSON.stringify(error.debugInfo));
              }
          });
  })
  .catch(function (error) {
      console.log("Error: "   error);
      if (error instanceof OfficeExtension.Error) {
          console.log("Debug info: "   JSON.stringify(error.debugInfo));
      }
  });

But I get an error:

Error: ReferenceError: firstColumn is not defined

How can I pass variables in a chain of ".then()" calls?

CodePudding user response：

i think you have your then in the the wrong place

the second then looks like it should be attached to the second sync not the first

Excel.run(function(context) {
    var currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
    var table = currentWorksheet.tables.getItem("NewTable");
    table.rows.load("count");
    return context.sync().then(function() {
        var rowCount = table.rows.count;
        console.log("There are "   rowCount   " rows in the table.");
        var firstColumn = table.columns.getItem(1);
        firstColumn.load("values");
        return context
            .sync()
            .then(function() {
                var firstColumnValues = firstColumn.values;
                var summary = {};
                for (var i = 0; i < firstColumnValues.length; i  ) {
                    var value = firstColumnValues[i][0];
                    if (summary[value]) {
                        summary[value]  ;
                    } else {
                        summary[value] = 1;
                    }
                }
                console.log(summary);
            })
            .catch(function(error) {
                console.log("Error: "   error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: "   JSON.stringify(error.debugInfo));
                }
            });
    });
}).catch(function(error) {
    console.log("Error: "   error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: "   JSON.stringify(error.debugInfo));
    }
});

however i think you would be greatly helped by switching to the async/await syntax, also notice that the use of var for variable declaration is frowned on these days and is a hold over from the early days of javascript before it had the concept of scoping so can lead to some very odd side effects, you should use const and let (const for a variable that will be set once and read many or let for a variable that can be set and read many times)

Excel.run(async function(context) {
    const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
    const table = currentWorksheet.tables.getItem("NewTable");
    table.rows.load("count");
    await context.sync().catch(function(error) {
        console.log("Error: "   error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: "   JSON.stringify(error.debugInfo));
        }
    });
    const rowCount = table.rows.count;
    console.log("There are "   rowCount   " rows in the table.");
    const firstColumn = table.columns.getItem(1);
    firstColumn.load("values");
    await context.sync().catch(function(error) {
        console.log("Error: "   error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: "   JSON.stringify(error.debugInfo));
        }
    });
    const firstColumnValues = firstColumn.values;
    const summary = {};
    for (let i = 0; i < firstColumnValues.length; i  ) {
        const value = firstColumnValues[i][0];
        if (summary[value]) {
            summary[value]  ;
        } else {
            summary[value] = 1;
        }
    }
    console.log(summary);
});