Office Scripts - Merge Cell

Question

Is there a code for repetitive merging. Like for example I want to merge A1:B1, then again A2:B2, then A3:B3 and so forth. It goes a long way up to 500th cell A500:B500?

I am familiar with this code:

    function main(workbook: ExcelScript.Workbook) {
        let selectedSheet = workbook.getActiveWorksheet();
        selectedSheet.getRange("A2:B2").merge(false);
        selectedSheet.getRange("A2:B2").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
    }

Are there a few lines code to apply it multiple times, to avoid actually repeating the merging of 2 cells while recording?

score 1 · Answer 1 · answered Mar 23 '23 at 16:32

You can try using the code below

    function main(workbook: ExcelScript.Workbook) {
        let selectedSheet = workbook.getActiveWorksheet();
        let address = "A2:B500"
        selectedSheet.getRange(address).merge(true);
        selectedSheet.getRange(address).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
    }

score 0 · Accepted Answer · answered Mar 23 '23 at 06:41

This obviously comes with caveats but the only thing I can think off is to merge the first set of cells and then copy that merge down the desired amount of rows below ...

function main(workbook: ExcelScript.Workbook) {
    let worksheet = workbook.getActiveWorksheet();

    worksheet.getRange("A2:B2").merge(false);
    worksheet.getRange("A3:B30").copyFrom(worksheet.getRange("A2:B2"), ExcelScript.RangeCopyType.formats);
}

If the cells are populated, it will work because it's going to copy formats only but not sure if that is acceptable for you or not.

Office Scripts - Merge Cell

2 Answers2