CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsduplicatesrename
weizer
weizer

Reputation: 1127

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

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

Upvotes: 1

Views: 864

Answers (1)

Tanaike
Tanaike

Reputation: 201503

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:

Upvotes: 2

Related Questions