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

Time:11-17

enter image description here

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

CodePudding user response:

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:

  • Related