Excel office-js: Merge 2 or more cells

Question

I need to merge 2 cells and have a value in the merged cell.
My (failed) attempts:

#1

Excel.run(function (context) {
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").values = values;
    newSheet.getRange("A1:B1").merge = true;
});

#2

Excel.run(function (context) {
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").values = values;
    newSheet.getRange("A1").getResizedRange(0, 1).merge = true;
});

Where did I do wrong?

EDIT: The marked answer is correct not because its main content - but because Kim Brandl's later reply - in which, my solution is using API 1.1 - but the merge() can only work in API 1.2.

Thanks a lot!

Answer 1

Try this from official doc

        Excel.run(function (ctx) {
        var sheetName = "New Sheet 1";
        var rangeAddress = "A1:B1"; 
        var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
        range.select();
        return ctx.sync(); 
    }).catch(function(error) {
            console.log("Error: " + error);
            if (error instanceof OfficeExtension.Error) {
                console.log("Debug info: " + JSON.stringify(error.debugInfo));
            }
    });

Answer 2

Your first issue is that merge() is a method, not a property. For a simple merge of cells in a range like this, the syntax would be:

var range = newSheet.getRange("A1:B1");
range.merge();

The second issue is that you need to sync after this in order to commit your changes to the worksheet:

Excel.run(function (context) {
  const newSheet = context.workbook.worksheets.add("New sheet 1");
  newSheet.load('name');
  return context.sync().then(function () {                       
      var range = newSheet.getRange("A1:B1");
      range.merge();       
      newSheet.activate(); 
      return context.sync();
  });        
});

Answer 3

Here's a code snippet that creates a new sheet, adds 2 rows of data to the sheet, and then merges cells A1 and B1.

Excel.run(function (context) {

    // create new sheet, add 2 rows of data
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "Row 2", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1).values = values;

    // merge cells A1 and B1 
    var myRange = newSheet.getRange("A1:B1");
    myRange.merge();

    return context.sync();
})
    .catch(function (error) {
        console.log('error: ' + error);
        if (error instanceof OfficeExtension.Error) {
            console.log('Debug info: ' + JSON.stringify(error.debugInfo));
        }
    });

Here's the result of running this code in Excel:

Note the following:

• If multiple cells within the range that you're merging contain values, then only the value in the first cell of the range (i.e., the upper-left-most cell in the range) is persisted -- all subsequent cell values are wiped-out. For example, the sheet in the example above initially contained a value in cell B1 ("Row 2") -- but as the screenshot above shows, this value is wiped-out whenever cell B1 is merged with cell A1.

• You only need a single context.sync() -- since you're just writing data to the sheet and merging cells, those commands can be queued up and executed with a single sync() at the end.

• It's important to always include error handling (as I've done with the catch statement), to ensure that things don't silently fail without you knowing it.

Update (adding info about Requirement Sets):

One additional bit of info -- looks like range.merge() was added to the Excel JavaScript API as part of Requirement Set 1.2. I'd suggest you verify that you're running a version/platform of Office that supports Requirement Set 1.2 (or higher) -- https://dev.office.com/reference/add-ins/requirement-sets/excel-api-requirement-sets. For information about how to get the latest version of Office, see this topic in the Office Add-ins docs.