Using Last Row with GetRangeByIndexes

I'm trying to learn best practices for creating a range in JS. I'm trying to avoid the mistake I did when learning VBA which was that I used the format Range("A1:A2") for a long time before realizing the it was better to use Range(Cells(1,1),Cells(2,1)) because integers are generally easier to work with.

I found getRangeByIndexes for JS, and its not perfect as I have to do math on number of rows if the first row isn't 0. I'd prefer to use integers to set first and last cell in range, but thats another story.

Currently I can select the range if I hard code in the numbers, but now I'm working on adding in the "LastRow" function to make the range dynamic and I can't get it to work. I also struggled to get it to print out to the console.log, but I decided to just try to work with the range vs print it out. I'm currently editing taskpane.js and very new to this.

Here is my code so far:

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

/* global console, document, Excel, Office */

// The initialize function must be run each time a new page is loaded
Office.initialize = () => {
  document.getElementById("sideload-msg").style.display = "none";
  document.getElementById("app-body").style.display = "flex";
  document.getElementById("run").onclick = run;
};

export async function run() {
  try {
    await Excel.run(async (context) => {
      var ws = context.workbook.worksheets.getItem("Sheet1");
      ws.activate();

      var lrow = ws.getUsedRange().getLastRow();
      lrow.load("rowindex");
      context.sync();
      var range = ws.getRangeByIndexes(0, 0, 4, 5); //This Works
      //var range = ws.getRangeByIndexes(0, 0, lrow.rowIndex, 5); //This does nothing, but no errors either, just nothing.
      range.select();
      await context.sync();
      console.log("END");
    });
  } catch (error) {
    console.error(error);
  }
}

If I replace lrow.rowindex with 4 it works as expected. Can anyone advise what I'm doing wrong and if this is the best way to generate a range w/ numbers (is there a way to do first/last cell?)

CodePudding user response：

I believe you have to call await context.sync() before you can use any properties you load. You're trying to use a loaded property (rowIndex) before the context.sync() call. So that's why I think it's not working.

If you update your code from this:

  var lrow = ws.getUsedRange().getLastRow();
  lrow.load("rowindex");
  var range = ws.getRangeByIndexes(0, 0, 4, 5); //This Works
  //var range = ws.getRangeByIndexes(0, 0, lrow.rowIndex, 5); //This does nothing, but no errors either, just nothing.
  range.select();
  await context.sync();

To this:

  var lrow = ws.getUsedRange().getLastRow();
  lrow.load("rowindex");
  await context.sync();
  //var range = ws.getRangeByIndexes(0, 0, 4, 5); //This Works
  var range = ws.getRangeByIndexes(0, 0, lrow.rowIndex, 5); //This does nothing, but no errors either, just nothing.
  range.select();
  await context.sync();

That should fix things.

CodePudding user response：

You definitely need the "await" on your first "context.sync()" statment. I added that and it works fine as long as there is data on sheet 1. If lrow.rowIndex evalautes to 0, it will cause an error because getRangesByIndexes needs at least one row to select.

Here's the code as I ran it.

async function run() {
  try {
      await Excel.run(async (context) => {
      var ws = context.workbook.worksheets.getItem("Sheet1");
      ws.activate();

      var lrow = ws.getUsedRange().getLastRow();
      lrow.load("rowindex");
      await context.sync();
  
      var range = ws.getRangeByIndexes(0, 0, lrow.rowIndex, 5); 
  
      range.select();
      await context.sync();
      console.log("END");
    });
  } catch (error) {
    console.error(error);
  }
}

I took of the "export" directive because the environment I'm working in complained about it.

In case you are interested, I'm using an add-in called the "JavaScript Automation Development Environment (JADE)" from the add-in store. It's meant for automating code in a workbook, not building an add-in, but it is really simple for testing things like this. Disclaimer: I wrote JADE.