Reputation: 41
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
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