CODEWITHSUNDEEP
javascriptexceloffice-js
Avi
Avi

Reputation: 2170

Excel office-js API - Get ranges of specific sheets

The Excel document's sheets are the days of the week (Sunday-Friday) - which need to be iterated over.
My question is: How?

My code attempts (which all resulted as a failure):

Excel.run(function(context) {
    const currentSheets = context.workbook.worksheets;
    const worksheetDays = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
    for (let i = 0; i < worksheetDays.length; i++) {
        sheetRanges[worksheetDays[i]] = currentSheets.load("values");
    }
    return context.sync().then(function() {
        console.log(sheetRanges.Sunday.values); // null
    });
});

Next attempt:

Excel.run(function(context) {
    const worksheetDays = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
    const sheetRanges = {};
    const currentSheets = context.workbook.worksheets;
    for (let i = 0; i < worksheetDays.length; i++) {
         sheetRanges[worksheetDays[i]] = currentSheets.getItem(worksheetDays[i]).load("values");
    }
    return context.sync().then(function() {
        console.log(sheetRanges.Sunday.values); // null
    });
});

Next:

Excel.run(function(context) {
    const worksheetDays = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
    const sheetRanges = {};
    const currentSheets = context.workbook.worksheets;
    for (let i = 0; i < worksheetDays.length; i++) {
         sheetRanges[worksheetDays[i]] = currentSheets.getItem(worksheetDays[i]).getRange().load("values");
    }
    return context.sync().then(function() {
        console.log(sheetRanges.Sunday.values); // null
    });
});

Next:

Excel.run(function(context) {
    const worksheetDays = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
    var sheetRanges = {};
    const currentSheets = context.workbook.worksheets;
    for (let i = 0; i < worksheetDays.length; i++) {
        sheetRanges[worksheetDays[i]] = currentSheets.load("name");
    }
})
.then(context.sync)
.then(function () {
     for (let i = 0; i < worksheetDays.length; i++) {
        sheetRanges[worksheetDays] = sheetRanges[worksheetDays].load("values");
    }
 })
.then(function () {
    console.log(sheetRanges.Sunday.values); // null
});

Basically, I'm lost. I just need to get the cell values of all sheets.
How to solve this?

Thanks!

Upvotes: 2

Views: 1430

Answers (1)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

You are very very close. In fact, your third attempt was almost correct. But for the sake of others who might come across this question, let me describe the issue with the first two code blocks, first.

For the first two attempts, the problem is that you can only load properties that actually exist on the object. And unfortunately for your code, values isn't a property on a worksheet object.

Personally, I find that the easiest way is to write the code as if the properties are already loaded (and using IntelliSense to guide you). Then, go up one or more lines, and do the load statement, just as you did, specifying the property name that you're using (i.e., range.load('value')), and make sure that you have a context.sync(), properly chained (which you correctly do) or await-ed (if you're using TypeScript).

Now, the issue with your almost-correct approach #3 is that if you are constructing a Range for the entire worksheet and then loaded its values property, values would be null, because such range is unbounded (a million+ rows by 16k columns; there is no way you want to shuttle that sort of data between the host Excel application and JavaScript). So the API you want to use is sheet.getUsedRange().

So, with all of this being said, you can take your 3rd code attempt and just replace

currentSheets.getItem(worksheetDays[i]).getRange().load("values");

with

currentSheets.getItem(worksheetDays[i]).getUsedRange().load("values");

So you were literally 4 characters away from having it right on your third try. By the way, if you had been loading the address property, you would in fact have gotten it right. At that point, the issue wasn't the way you were loading values, but rather the fact that unbounded ranges (and also inconsistent values, for things like bold/italic/color/etc if they are different across multiple cells) return a null. I'll pass feedback to our content team to make sure this is made clearer.

So, final code (and also TypeScript-ifying it for good measure :-), so you can have good IntelliSense):

await Excel.run(async context => {
    const worksheetDays = ["Sunday", "Monday", /*...*/];
    const sheetRanges = {} as { [key: string]: Excel.Range }

    const currentSheets = context.workbook.worksheets;
    for (let i = 0; i < worksheetDays.length; i++) {
        sheetRanges[worksheetDays[i]] = currentSheets.getItem(worksheetDays[i]).getUsedRange().load("values");
    }

    await context.sync();
    console.log(sheetRanges["Sunday"].values);
});

You can try this snippet live in literally five clicks in the new Script Lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in (free), then choose "Import" in the navigation menu, and use the following GIST URL: https://gist.github.com/Zlatkovsky/a045beb300c9b1a86276f6ff99fd64a4. See more info about importing snippets to Script Lab.


Finally, I would be remiss not to mention that for many of the above topics -- i.e., how to properly load properties, understanding how collection access works, using TypeScript inside of an Office Add-in, and more -- you might find the book "Building Office Add-ins using Office.js" a valuable resource. For full disclosure: I am the author of said book. But I can also guarantee you'd find it useful. And for that matter, the LeanPub store itself offers a 45-day unconditional 100% "happiness guarantee" for the book (which I've never had anyone request a refund for).

All the best, and good luck in your add-in development experience! Do keep posting questions if you run into issues...

Upvotes: 1

Related Questions