CODEWITHSUNDEEP
ms-officeoffice-js
SoftTimur
SoftTimur

Reputation: 5490

Assign an array of values to a range

I want to initiate a range of worksheet with an array of values. I have the following code.

function initiate(address, values) {
    return Excel.run(function (ctx) {
        var sheet = ctx.workbook.worksheets.getActiveWorksheet();
        var range = sheet.getRange(address);
        var range.values = values;
        return ctx.sync()
    }
}

My tests show that this works only when values has exactly the same dimension (ie, height, width) as address, or when values is a single value. Otherwise, there will be an error.

Could anyone confirm that?

If so, I need to adjust address to suit the dimension of values. It is easy to get the dimension of values, but I cannot find a function to get a range from eg, 1 top-left cell + 1 number of rows + 1 number of columns, or 1 top-left cell and 1 bottom-right cell.

Am I missing something?

Upvotes: 1

Views: 253

Answers (2)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

As CompuChip said, there is not (yet) an API for resizing a range to a particular absolute size, though it is forthcoming.

That said: if you have an array, just start with a single cell and then resize it by array's row-count-minus-1 (i.e., array.length - 1), followed by columns-minus-1 (array[0].length - 1)

const values = [
    [1, 2, 3],
    ["Hello", "Bonjour", "Привет"]
]

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRange("D3").getResizedRange(
        values.length - 1, values[0].length - 1);
    range.values = values;
    await context.sync();
});

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

Upvotes: 1

CompuChip
CompuChip

Reputation: 9232

In VBA you would use Range.Resize. In OfficeJS there seems to be a function called getResizedRange which

[g]ets a Range object similar to the current Range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

Unfortunately it accepts the delta values, so you need to calculate the difference between the current range size and the target size. For example, if you wanted to obtain a range of rows by cols cells, you could try something along the lines of 

var originalRange = sheet.getRange(address);
var range = originalRange.getResizedRange(
     rows - originalRange.rowCount, cols - originalRange.columnCount);

Upvotes: 0

Related Questions