0

I'm struggling to understand an issue I'm having with row indexes being out of bounds.

Using Apps Script I'm copying a range from one sheet to another. The sheet that is being copied to originally contains one empty row, and then I am copying two rows of data to it - this works fine.

I then am trying to copy the row heights from the source sheet to the destination sheet and this is where I run into the issue. The first row works fine but then when it gets to the 2nd row it gives me an out of bounds error. If I change the destination sheet so that it starts off with 2 empty rows then I do not get this error.

I also do not get the error if I first run the copy function and then separately run the change row height function after.

To me it seems like Apps Script is storing the destination sheet before the additional row is added and then when I try to change the height of the 2nd row it thinks it doesn't exist? Does anyone know a way around this.

What's also odd is if I run getLastRow() on the destination sheet right before changing the row heights it returns 2, so somewhere it knows there are 2 rows.

Here is my function that copies a named range and then the row heights:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const namedRanges = [['namedRange', 24, 2]] //Range name, number of columns, number of rows

function copyRange(sheetName, rangeName, destRow, destCol) {
  const sourceRange = ss.getRangeByName(rangeName);
  const sourceSheet = sourceRange.getSheet();
  const destSheet = ss.getSheetByName(sheetName);
  
  for (let i = 0; i < namedRanges.length; i++) {
    if (namedRanges[i][0] === rangeName) {
      var rangeIndex = i;
      break;
    }
  }

  const destRange = destSheet.getRange(destRow, destCol, namedRanges[rangeIndex][2] - 1, namedRanges[rangeIndex][1] - 1);

  sourceRange.copyTo(destRange)

  for (let i = 0; i < namedRanges[rangeIndex][2]; i++) {
    let row = sourceRange.getRow() + i;
    let height = sourceSheet.getRowHeight(row);
    destSheet.setRowHeight(destRow + i, height);
  }
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Jamie B
  • 11
  • 1
  • I think you are jumping through a lot of hoops to accomplish something that is easily done with a Range.copyTo() method. You will get a row out of bounds error whenever you try to access a row that is not within the current range. – Cooper Dec 21 '21 at 17:41
  • I am using Range.copyTo() - but this doesn't copy the row heights which is why I need to do the row heights separately. I'm trying to access a row in the sheet not the range - the row definitely exists at the point I am trying to access it – Jamie B Dec 21 '21 at 18:58
  • Well that's probably why you getting the errors that your getting. – Cooper Dec 21 '21 at 19:05
  • I'd recommend that you focus on doing the individual operations and not try to generailize into a function at first. I think you'll find that the operation is so simple that the function is not really useful – Cooper Dec 21 '21 at 19:07
  • Add `SpreadsheetApp.flush()` and see if it solves the issue? – TheMaster Dec 21 '21 at 20:43

2 Answers2

1

Fix was to add SpreadsheetApp.flush() between copying the range and changing the row heights as per TheMaster's comment above.

Jamie B
  • 11
  • 1
0

If you are just copying from a named range to a named range in which the latter only need be a single cell. Then this would suffice.

function myfunk() {
  const nrl = [{srg = 'srg1',drg: 'drg1'},{srg = 'srg2',drg: 'drg2'}];
  nrl.forEach(obj => {
    obj.srg.copyTo(obj.drg)
  });
}

This can only occur in the same spreadsheet and the sheet names are included in the definition of the named ranges.

Cooper
  • 59,616
  • 6
  • 23
  • 54