Script to duplicate inactive sheet in Google Sheets

user_15205029 0
Viewed 172

I have this script below, which works very well to duplicate a sheet and rename it from the contents of cell A3.

Now I would like to run this exact function, but on an inactive (or even hidden) sheet. It can be a duplicate with only values copied, as there is no need to copy all of the deep data.

function AddReportSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();
    var myValue = SpreadsheetApp.getActiveSheet( ).getRange("A3").getDisplayValue();
    SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(myValue);
}
google-apps-script spreadsheet google-sheets
1 Answers

Explanation:

Your goal is to duplicate a particular sheet and set the name of the duplicate based on the value of cell A3 of the original sheet.

Solutions:

Duplicate the sheet:

Select the name of the sheet you want to duplicate. In the following example, Sheet1:

function AddReportSheet() {   
    const sheetName = "Sheet1"; // put the name of your sheet to duplicate
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getSheetByName(sheetName);
    const myValue = sheet.getRange('A3').getDisplayValue();
    sheet.copyTo(ss).setName(myValue);   
}

Duplicate the sheet and keep only the values and format:

If you want to keep only the values of the duplicate sheet then you can use the copyTo(destination, options) and keep only the contents e.g. {contentsOnly:true}.

function AddReportSheet() {   
    const sheetName = "Sheet1"; // put the name of your sheet to duplicate
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getSheetByName(sheetName);
    const myValue = sheet.getRange('A3').getDisplayValue();
    const new_sheet = sheet.copyTo(ss).setName(myValue);   
    const rng = new_sheet.getDataRange();
    rng.copyTo(rng, {contentsOnly:true})
}
user_112252910
Related