How to duplicate sheet using google apps script and rename it based on last column value in Row 1?

Question

Hi everyone,

I want to duplicate the sheet and rename the duplicated sheet based on the name in row 1 (shown in the screenshot above).

So for example, when I run the script, the script able to find the last value in row 1 ("Phase 1" in this case) and set it as the name for the duplicated sheet. So if the last value in row 1 is in cell C1, then it will name the duplicated sheet based on the value in cell C1.

This is my script but I'm not sure where did I did wrongly:

 function AddReportSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var ss1 = SpreadsheetApp.getActiveSheet();
  ss.duplicateActiveSheet();
  var lastcol = ss1.getRange(1,ss1.getLastColumn() +1)
  var myValue = ss1.getRange(1,1,1,lastcol).getDisplayValue();
  ss.renameActiveSheet(myValue);
}

Any help or advise will be greatly appreciated! :D

Answer 1

In your situation, how about the following modification?

From:

var lastcol = ss1.getRange(1,ss1.getLastColumn() +1)
var myValue = ss1.getRange(1,1,1,lastcol).getDisplayValue();
ss.renameActiveSheet(myValue);

To:

var myValue = ss1.getRange(1, 1, 1, ss1.getLastColumn()).getDisplayValues()[0].filter(String).pop();
ss.renameActiveSheet(myValue);

References:

• filter()
• pop()