Assign an array of values to a range

Question

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?

CompuChip · Accepted Answer · 2017-05-15 09:28:43Z

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);

Thank you... originalRange.colCount should be originalRange.columnCount... — SoftTimur, May 15 at 7:45

Michael Zlatkovsky - Microsoft · Answer 2 · 2017-05-15 18:04:06Z

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 view the snippet, and then run it interactively with literally two clicks, using this Script Lab snippet. Give it a try!

Thank you for the answer and the tool... – SoftTimur May 16 at 14:20 — SoftTimur, May 16 at 14:20

asked	3 months ago
viewed	43 times
active	3 months ago

Assign an array of values to a range

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged ms-office office-js or ask your own question.

Hot Network Questions

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged ms-office office-js or ask your own question.

Related