google apps script - How to duplicate a sheet with protected ranges?

Google apps script - How to duplicate a sheet with protected ranges?

Duplicating a sheet in Google Apps Script while preserving protected ranges can be a bit tricky because the copyTo method does not automatically copy protected ranges. Instead, you need to explicitly copy the protected ranges from the original sheet to the duplicated sheet. Here's an example script to duplicate a sheet along with its protected ranges:

function duplicateSheetWithProtectedRanges() { // Get the active spreadsheet var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Specify the name of the sheet to duplicate var sheetToDuplicateName = 'OriginalSheet'; // Get the original sheet var originalSheet = spreadsheet.getSheetByName(sheetToDuplicateName); // Duplicate the sheet var duplicatedSheet = originalSheet.copyTo(spreadsheet); // Set a new name for the duplicated sheet var newSheetName = 'DuplicatedSheet'; duplicatedSheet.setName(newSheetName); // Get all protected ranges from the original sheet var protectedRanges = originalSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); // Iterate through protected ranges and duplicate them to the new sheet protectedRanges.forEach(function (range) { var protectedRange = range.getRange(); var protectedSheet = range.getSheet(); var duplicatedRange = duplicatedSheet.getRange( protectedRange.getRow(), protectedRange.getColumn(), protectedRange.getNumRows(), protectedRange.getNumColumns() ); var protection = duplicatedRange.protect(); protection.setDescription(range.getDescription()); if (range.canEdit()) { protection.removeEditors(protection.getEditors()); protection.addEditor(Session.getActiveUser()); } else if (range.canView()) { protection.removeEditors(protection.getEditors()); protection.removeEditors(protection.getEditors()); } }); Logger.log('Sheet duplicated with protected ranges.'); }

This script performs the following steps:

  1. Duplicates the original sheet using the copyTo method.
  2. Sets a new name for the duplicated sheet.
  3. Retrieves all protected ranges from the original sheet using getProtections.
  4. Iterates through the protected ranges, duplicates them to the new sheet, and adjusts the protection settings.

Make sure to replace 'OriginalSheet' and 'DuplicatedSheet' with the actual names you are using in your spreadsheet.

To run the script, open the Google Sheets document, click on "Extensions" > "Apps Script," paste the script, and run the duplicateSheetWithProtectedRanges function.

Examples

  1. "Google Apps Script duplicate sheet with protected ranges"

    • Description: General query for duplicating a sheet while preserving protected ranges.
    // Example Google Apps Script code to duplicate a sheet with protected ranges function duplicateSheetWithProtectedRanges() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } }

  2. "Google Apps Script duplicate sheet with format"

    • Description: Duplicate a sheet with both protected ranges and cell formatting.
    // Example Google Apps Script code to duplicate a sheet with protected ranges and format function duplicateSheetWithFormat() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Duplicate formatting newSheet.getDataRange().setValues(sourceSheet.getDataRange().getValues()); }

  3. "Google Apps Script duplicate sheet with formulas"

    • Description: Duplicate a sheet with protected ranges and formulas.
    // Example Google Apps Script code to duplicate a sheet with protected ranges and formulas function duplicateSheetWithFormulas() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Duplicate formulas newSheet.getDataRange().setFormulas(sourceSheet.getDataRange().getFormulas()); }

  4. "Google Apps Script duplicate sheet with values"

    • Description: Duplicate a sheet with protected ranges while copying only values.
    // Example Google Apps Script code to duplicate a sheet with protected ranges and values function duplicateSheetWithValues() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Copy only values newSheet.getDataRange().setValues(sourceSheet.getDataRange().getValues()); }

  5. "Google Apps Script duplicate sheet with charts"

    • Description: Duplicate a sheet with protected ranges and embedded charts.
    // Example Google Apps Script code to duplicate a sheet with protected ranges and charts function duplicateSheetWithCharts() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Duplicate embedded charts var charts = sourceSheet.getCharts(); for (var i = 0; i < charts.length; i++) { newSheet.insertChart(charts[i]); } }

  6. "Google Apps Script duplicate sheet with conditional formatting"

    • Description: Duplicate a sheet with protected ranges and conditional formatting.
    // Example Google Apps Script code to duplicate a sheet with protected ranges and conditional formatting function duplicateSheetWithConditionalFormatting() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Duplicate conditional formatting newSheet.getDataRange().copyFormatToRange(newSheet, 1, newSheet.getMaxColumns(), 1, newSheet.getMaxRows()); }

  7. "Google Apps Script duplicate sheet without formulas"

    • Description: Duplicate a sheet with protected ranges excluding formulas.
    // Example Google Apps Script code to duplicate a sheet with protected ranges excluding formulas function duplicateSheetWithoutFormulas() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Exclude formulas newSheet.getDataRange().setValues(sourceSheet.getDataRange().getValues()); }

  8. "Google Apps Script duplicate sheet with custom formats"

    • Description: Duplicate a sheet with protected ranges and custom cell formats.
    // Example Google Apps Script code to duplicate a sheet with protected ranges and custom formats function duplicateSheetWithCustomFormats() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Duplicate custom cell formats var formats = sourceSheet.getRange(1, 1, sourceSheet.getMaxRows(), sourceSheet.getMaxColumns()).getNumberFormats(); newSheet.getRange(1, 1, newSheet.getMaxRows(), newSheet.getMaxColumns()).setNumberFormats(formats); }

  9. "Google Apps Script duplicate sheet with named ranges"

    • Description: Duplicate a sheet with protected ranges and named ranges.
    // Example Google Apps Script code to duplicate a sheet with protected ranges and named ranges function duplicateSheetWithNamedRanges() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Duplicate named ranges var namedRanges = spreadsheet.getNamedRanges(); for (var i = 0; i < namedRanges.length; i++) { var namedRange = namedRanges[i]; if (namedRange.getRange().getSheet().getName() === sourceSheet.getName()) { var newRange = newSheet.getRange(namedRange.getRange().getA1Notation()); spreadsheet.setNamedRange(namedRange.getName(), newRange); } } }

  10. "Google Apps Script duplicate sheet with comments"

    • Description: Duplicate a sheet with protected ranges and cell comments.
    // Example Google Apps Script code to duplicate a sheet with protected ranges and comments function duplicateSheetWithComments() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = spreadsheet.getSheetByName('SourceSheet'); var newSheetName = 'DuplicatedSheet'; // Duplicate the sheet var newSheet = sourceSheet.copyTo(spreadsheet).setName(newSheetName); // Duplicate protected ranges var protectedRanges = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protectedRanges.length; i++) { var range = protectedRanges[i].getRange(); var newRange = newSheet.getRange(range.getA1Notation()); var protection = newRange.protect(); protection.setDescription(protectedRanges[i].getDescription()); } // Duplicate cell comments var comments = sourceSheet.getComments(); for (var i = 0; i < comments.length; i++) { var comment = comments[i]; var range = comment.getRange(); var newRange = newSheet.getRange(range.getA1Notation()); newRange.setComment(comment.getComment()); } }

More Tags

sql-drop keylogger xvfb postgresql-json exit template-engine roi windows-8 javabeans boundary

More Programming Questions

More Other animals Calculators

More Various Measurements Units Calculators

More Tax and Salary Calculators

More Biology Calculators