CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Opsoft
Opsoft

Reputation: 41

Google Sheets: getting a sheet by its index

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

}

Upvotes: 4

Views: 13334

Answers (1)

ScampMichael
ScampMichael

Reputation: 3728

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

Upvotes: 3

Related Questions