CODEWITHSUNDEEP
exceloffice-js
RichaS
RichaS

Reputation: 219

Find available range in Excel in JS API

I have a requirement in my add in where I need to insert a table in Excel. But I need to know if the available cell range is empty for my table to insert there. So I have to check that if my cursor is at A1, do we have available space from A1 to A15 and there is no existing data there.

I tried getRangeByIndexes() function but this just gives a range. I can't find a function that returns a boolean for my requirement.

Upvotes: 2

Views: 1416

Answers (2)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

There are a number of ways of doing this. I think the best one is to use a combination of getSelectedRange, getAbsoluteResizedRange, and getUsedRangeOrNullObject

await Excel.run(async (context) => {
    let numRows = 10;
    let numColumns = 3;
    let range = context.workbook.getSelectedRange().getCell(0, 0).getAbsoluteResizedRange(numRows, numColumns);
    let usedRange = range.getUsedRangeOrNullObject(true /*valuesOnly*/);
    await context.sync();
    if (usedRange.isNullObject) {
        console.log("The range is empty");
    } else {
        console.log("Sorry, it's already taken")
    }
});

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/4835778375bb5f5b9d49bff4f5d522f0. See more info about importing snippets to Script Lab.

The objects returned by *OrNullObject-sufficed methods are a bit unusual. If you've not run into them before, I recommend you read about it in our docs. I also go into great detail about it in my book, "Building Office Add-ins using Office.js" (with all profits to charity), in a chapter entitled "9. Checking if an object exists".

Upvotes: 1

Sudhi Ramamurthy
Sudhi Ramamurthy

Reputation: 2478

Excel will always have "space" to insert data/table. I guess you want to make sure the cells are in fact empty before inserting table? If so, you can get getSelectedRange() API and use getOffsetRange() to select both the selected range (load just the address and values) and the range associated table dimension (load just values). You can then check to make sure values are empty before inserting. Another way could be to check worksheet.getUsedRange() to ensure the used range is just A1 (empty worksheet). This only works if you are ensuring that the whole worksheet is empty (not just the table's address).

Upvotes: 1

Related Questions