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

Question

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

taller · Accepted Answer · 2024-06-15 23:55:20Z

0

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

}

edited Jun 15, 2024 at 23:55

answered Jun 15, 2024 at 15:13

taller

19.7k2 gold badges9 silver badges24 bronze badges

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

5 Comments

Jampong Over a year ago

This worked. Thank you. I learned 2 things from here,console and the indexing

Jampong Over a year ago

Hi Taller, is there anyway that .getRange("A1") would work for getRangeByIndex(0,0,1,1) ? I am trying to make it dynamic

taller Over a year ago

What's kind of dynamic? Pls share a sample scenario.

Jampong Over a year ago

basically, I want to be able to copy from Column F to J of sheet 1moving down a row for every increment of row to destination cell of sheet 2. like 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

taller Over a year ago

You don't need to use looping. Believe it or not, it's a fairly simple method. I've uploaded a snippet. Hope it's helpful for you.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related