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?)
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.
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.
This is def related to context.sync. I'm still learnign about promise/return/async etc. I switched over to Visual Studio and got a better idea from there project files, this is my adjustment to the default function for the taskpane in there project files.
Note: The nested function finishes off the rest of the function.
function setColor() {
Excel.run(function (context) {
//Start Func
var ws = context.workbook.worksheets.getActiveWorksheet();
var lrow = ws.getUsedRange().getLastRow();
lrow.load("rowindex");
// Run the queued-up command, and return a promise to indicate task completion
return context.sync()
.then(function () {
//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();
})
//End Func
return context.sync();
}).catch(function (error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
}
})();