Excel office-js API - Insert values to a blank sheet

Question

How can I insert values into a blank sheet?
The way that I currently know of for inserting values is by retrieving the range and then inserting the values using the values property, such as

range.getRange("A1:" + cellBottomRight);
range.load("values");
context.sync().then(function () {
    range.values = twoDimensionalArrayOfValues;
});

Is there a simpler way by using a single function to simply insert the values, rather than first retrieving the range?

Thanks!

EDIT:
I've been trying to create a new sheet and then insert a 2-dimensional array to it, which its values would be inserted starting from the cell A1.
So far, I managed to do the following:

let neeSheet = context.workbook.worksheets.add("New sheet");
newSheet.activate();
newSheet.getRange("A1").values = twoDimensionalArray;
context.sync();

But didn't work.

How can I get it to work?
Thanks!

To elaborate some on Philip's answer below: OBJECT.LOAD(PROPERTIES) is required in situations where you plan to subsequently read those property values. If you're only planning to set the object's properties (as you are, in the scenario that you've described above), or to call methods on the object, or to use the object to navigate to another object, you do not need to call OBJECT.LOAD(PROPERTIES). — Kim Brandl
– Kim Brandl, Commented Aug 14, 2017 at 23:29
I'm basically trying to create a new sheet and insert there a values that I have in a 2-dimensional array. The code below did work, but only for a string - it didn't allow me to insert a 1-dimensional or a 2-dimensional array to the range. Of course, I have at the end of the code the context.sync();, and I also tried return context.sync(); — Avi
– Avi, Commented Aug 16, 2017 at 14:17

Philip Rueker · Accepted Answer · 2017-08-14 22:04:45Z

4

You don't actually have to load the range if you just want to set the values, so you can do this:

var range = ctx.workbook.worksheets.getItem("Sheet1").getRange("A1");
range.values = [["Value"]];
return ctx.sync();

answered Aug 14, 2017 at 22:04

Philip Rueker

9485 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Avi Over a year ago

What about inserting values to a newly created sheet? When I tried to do var newRange = context.workbook.worksheets.add("New sheet");, then newRange.activate(); and finally return context.sync().then(function () { newRange.getRange("A1").values = twoDimensionalArrayOfValues; }); - but that doesn't work

Kim Brandl · Accepted Answer · 2017-08-16 19:40:01Z

(Adding this answer in response to the new information that @avi12 added to the question above, under "EDIT")

Philip's answer above correctly shows how to insert a single value into cell A1 of a worksheet. To address the specific scenario that you've described in your (updated) question, here are some code snippets (one in TypeScript and the other in JavaScript) that show how to create a new worksheet and then add data to the worksheet, using a 2-dimensional array of data. The key thing to point out here is that I'm retrieving the range by using getResizedRange (passing in dimensions of my array), so that the size of the range matches the size of the data set that I'm inserting into it.

Note: You can quickly and easily try these snippets yourself by using 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/kbrandl/01c4faf352c34286188311c1198f6307.

TypeScript:

async function run_TS() {
    try {
        await Excel.run(async (context) => {

            // define values that will be inserted into new sheet
            let values = [["A1", "B1", "C1"], ["A2", "B2", "C2"]]; 

            // create and activate new sheet
            let sheets = context.workbook.worksheets;
            let newSheet = sheets.add("New sheet TS");
            newSheet.activate();

            // add data to the new sheet
            let range = newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1);
            range.values = values;

            // sync
            await context.sync();

            console.log("Finished with run_TS function");
        });
    }
    catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
    }
}

JavaScript:

function run_JS() {
    Excel.run(function (context) {

        // define values that will be inserted into new sheet
        var values = [["A1", "B1", "C1"], ["A2", "B2", "C2"]];

        // create and activate new sheet
        var sheets = context.workbook.worksheets;
        var newSheet = sheets.add("New sheet JS");
        newSheet.activate();

        // add data to the new sheet
        var range = newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1);
        range.values = values;

        // sync        
        return context.sync()
            .then(function () {
                console.log("Finished with run_JS function");
            });
    })
        .catch(function (error) {
            OfficeHelpers.UI.notify(error);
            OfficeHelpers.Utilities.log(error);
        });
}

Collectives™ on Stack Overflow

Excel office-js API - Insert values to a blank sheet

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related