I need to copy a range in a spreadsheet from one row to another - but the name of the sheet changes and I am unable to work out how to use the sheet reference rather than sheet name when referring to the range.

Example code below which works - however I need to tell GAS to go get the first sheet, not sheet "S1"

function copyRange() {
var sheet = SpreadsheetApp.openById("1Ofua0Tzut5fnZRLkXwldy4pq4AVupdvNYybrFGrHjBU").getSheetByName("S1")
var source_range = sheet.getRange("A2:G2");
var target_range = sheet.getRange("A3:G3");
source_range.copyTo(target_range);
}

CodePudding user response：

About Example code below which works - however I need to tell GAS to go get the first sheet, not sheet "S1", in this case, how about the following modification?

From:

var sheet = SpreadsheetApp.openById("1Ofua0Tzut5fnZRLkXwldy4pq4AVupdvNYybrFGrHjBU").getSheetByName("S1")

To:

var sheet = SpreadsheetApp.openById("1Ofua0Tzut5fnZRLkXwldy4pq4AVupdvNYybrFGrHjBU").getSheets()[0];

• By this modification, the 1st tab is used as sheet. When [0] of getSheets()[0] is changed to [1], the 2nd sheets is used. When you changed the arangement of the sheet, the sheet is changed. For example, when you move the 1st tab from 3rd tab, getSheets()[0] retrieves the current 1st tab. Please be careful about this.

Reference:

• getSheets()