0

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 });
}
Hearno
  • 27
  • 6

2 Answers2

1
  • 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 getRangeByName(name) method as this will return the sheet with the given name & then you can get the range of that sheet. See this sample tweaked script below:

UPDATED

function MyAccount() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Price History");
  var newSheetName = ss.getSheetByName(sheet.getRange("E21").getValue());
  var Direction=SpreadsheetApp.Direction;
  var lastRowOfC = ss.getRange("C"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getRow() + 1; //Get the last row of column C on any worksheet
  sheet.getRange("E20").copyTo(newSheetName.getRange(lastRowOfC,3), { contentsOnly: true });
}

Getting the last row script was derived from one of the answers from Determining the last row in a single column (Getting last row on a column that contains empty gaps)

Sample test on my end:

NOTE: It would also be better if you could share a sample sheet that you're using in case I have misunderstood your setup.

enter image description here

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

enter image description here

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
  • 1
    This does exactly what I want, the line ```var newSheetName = sheet.getRange("E21").getValue();``` was throwing me off. Thank you for fixing that for me – Hearno Nov 11 '21 at 16:22
  • How would i go about copying this value to the lastrow of column C, somehow make this find the last row with data in it then offset one down and paste the values? – Hearno Nov 11 '21 at 21:05
  • 1
    @Hearno I have updated my answer where I have added a code to get the last row specifically on column `C` on any worksheet. Hopefully it helps. – SputnikDrunk2 Nov 11 '21 at 21:17
  • You are the best, you respond perfectly with just what I need. Thank you – Hearno Nov 11 '21 at 21:36
1

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);
}
Janine White
  • 439
  • 5
  • 14
  • This is perfect for me too! How would I go about getting the last row of column C and pasting the values there instead of just pasting them to only C1? Thank you – Hearno Nov 11 '21 at 17:12
  • @Hearno It depends on whether or not you're expecting all of the rows to be filled. If so, then ss.getSheetByName(nameRange.getValue()).getLastRow() will give you the last row with values in it. Otherwise, iterate down column C to find the first empty cell, then subtract 1, perhaps. – Janine White Nov 11 '21 at 17:56
  • What would be the line of code to just find the last line of column C and paste the one cell at the last cell in the row of column C? – Hearno Nov 11 '21 at 18:27
  • @Hearno https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column – Janine White Nov 11 '21 at 20:49