Google Sheets: getting a sheet by its index

Question

I am trying to get a sheet using a calculated index. I am having a problem but am unsure whether it's a problem converting a float to an integer or if there is a syntax error. What am I getting wrong?

function copySheetValues()
{

  var spread = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = SpreadsheetApp.getActiveSheet();

  //get the source sheet data
  var sourceDataRange = sourceSheet.getDataRange();
  var sourceSheetValues = sourceDataRange.getValues();
  var sourceRows = sourceDataRange.getNumRows();
  var sourceColumns = sourceDataRange.getNumColumns();


  // get the source sheet index and set the next sheet index
  var sourcesheetIndex = sourceSheet.getIndex();
  var destinationsheetIndex = Math.round(sourcesheetIndex + 1);

  // get the next sheet
  var destinationSheet = spread.getSheets()[destinationsheetIndex]

  //destination.insertSheet(sourcename, 0);
  destinationSheet.getDataRange().offset(0, 0, sourceRows, sourceColumns).setValues(sourceSheetValues);

}

Answer 1

As getIndex() is 1 based and getSheets() is 0 based you might try:

var destinationSheet = spread.getSheets()[sourceSheet.getIndex()];

go figure

Caveat: getIndex() returns the sheets position within the spreadsheet where getSheets() has to do with the order in which the sheets were added and the two may not correspond if the spreadsheet has been rearranged after the sheets were added.

To insure that the destination sheet is the sheet located just after the source sheet:

// get the source sheet index and set the next sheet index
  var destinationsheetIndex = sourceSheet.getIndex() + 1;

// get the next sheet
  var sheets = spread.getSheets();
  for (var i=0; i<sheets.length; i++) {
    if(sheets[i].getIndex() == destinationsheetIndex ) {
      var destinationSheet = sheets[i];
      break;
      };
    };