I have a formula in worksheet 'Price History' cell E21 that changes to different worksheet names automatically depending on what other cells are equaling too. The script I have is returning TypeError: newSheetName.getRange is not a function. In the below script i tried to copy a blank cell above E21 and paste the cell into a blank cell on the specified worksheet but that didn't help. I am trying to only activate the worksheet of which the value is in E21. Thank you

function MyAccount() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Price History");
  var newSheetName = sheet.getRange("E21").getValue();
  sheet.getRange("E20").copyTo(newSheetName.getRange("C1"), { contentsOnly: true });
}

CodePudding user response：

• This line var newSheetName = sheet.getRange("E21").getValue(); doesn't set the variable newSheetName as a sheet but a string value.

SUGGESTION:

Since you have mentioned that the value of cell E21 basically contains worksheet name that is randomly changing, perhaps you can still try using

UPDATED

After running the script, the test cell value was copied to the destination sheet called NewTestSheet on the last row of column C, which is C4

CodePudding user response：

You are trying to use a String value as a Sheet. Instead, use the sheet name to get a new sheet, then copy to the new range.

function MyAccount() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Price History");
  var copyRange = sheet.getRange("E20");
  var nameRange = sheet.getRange("E21");
  copyRange.copyTo(ss.getSheetByName(nameRange.getValue()).getRange("C1"), { contentsOnly: true });
}

Or,

function MyAccount() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Price History");
  var copyValue = sheet.getRange("E20").getValue();
  var nameRange = sheet.getRange("E21");
  ss.getSheetByName(nameRange.getValue()).getRange("C1").setValue(copyValue);
}