office-jsoffice-scriptsms-office-script

getRange works when pasting from one sheet to next but not if getRangeByIndex


I am trying to understand why getRangeByIndex does not work when copy pasting from one sheet to another sheet

Here is the getRange script

function main(workbook: ExcelScript.Workbook) {
    let temp = workbook.getWorksheet("Temp");
    let selectedSheet = workbook.getActiveWorksheet();
    // Paste to range A1 on temp from range F9:J9 on selectedSheet
    temp.getRange("A1").copyFrom(selectedSheet.getRange("F9:J9"), ExcelScript.RangeCopyType.all, false, false);
}

Here is the getRangeByIndex

function main(workbook: ExcelScript.Workbook) {
    let temp = workbook.getWorksheet("Temp");
    let selectedSheet = workbook.getActiveWorksheet();
    // Paste to range A1 on temp from range F9:J9 on selectedSheet
    temp.getRange("A1").copyFrom(selectedSheet.getRangeByIndexes(0,5,1,5), ExcelScript.RangeCopyType.all, false, false);
}

Solution

  • Microsoft documentation:

    ExcelScript.Worksheet interface

    function main(workbook: ExcelScript.Workbook) {
    
        let temp = workbook.getWorksheet("Temp");
        let selectedSheet = workbook.getActiveWorksheet();
        // Paste to range A1 on temp from range F9:J9 on selectedSheet
        console.log(selectedSheet.getRangeByIndexes(8, 5, 1, 5).getAddress())
        temp.getRange("A1").copyFrom(selectedSheet.getRangeByIndexes(8,5,1,5), ExcelScript.RangeCopyType.all, false, false);
    
    }
    

    Question: F9 to J9 of sheet 1 to A1 of sheet 2, then F10 to J10 of sheet 1 to A2 of sheet2, then F11 to J11 of sheet 1 to A3 of sheet 2 and so on

    function main(workbook: ExcelScript.Workbook) {
    
        let temp = workbook.getWorksheet("Temp");
        let selectedSheet = workbook.getActiveWorksheet();
     
        const rowCnt = 11; // Copy F9:J19 to A1:E11
        temp.getRange("A1").copyFrom(selectedSheet.getRangeByIndexes(8,5,rowCnt,5), ExcelScript.RangeCopyType.all, false, false);
    
    }