CODEWITHSUNDEEP
office-jsoffice-scriptsms-office-script
Jampong
Jampong

Reputation: 25

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

Upvotes: 1

Views: 92

Answers (1)

taller
taller

Reputation: 18778

  • getRangeByIndexes(8, 5, 1, 5) refers to the range F9:J9

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

}

Upvotes: 0

Related Questions