Search code examples
google-apps-scriptgoogle-sheetsspreadsheet-protection

Google Sheet - Sheet Duplication

I have a Google Sheets file that has inside 2 Sheets. One is named "START" & the other "MASTER". 2sheets Start&Master The "START" sheet has a button that has an assigned script: Script Button The script duplicates the "MASTER" tab and renames it "DD/MM" which stands for Day/Month.

The script that is running is:

function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  sheet = ss.getSheetByName('MASTER');
  sheet2 = sheet.copyTo(ss).setName('DD/MM');
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var p = protections[i];
    var rangeNotation = p.getRange().getA1Notation();
    var p2 = sheet2.getRange(rangeNotation).protect();
    p2.setDescription(p.getDescription());
    p2.setWarningOnly(p.isWarningOnly());
    if (!p.isWarningOnly()) {
      p2.removeEditors(p2.getEditors());
      p2.addEditors(p.getEditors()); // p2.setDomainEdit(p.canDomainEdit()); //  only if using an Apps domain
    }
  }
}

I have 2 questions, how:

  1. The duplicated sheet instead of renamed "DD/MM" to take the current date (with a leading zero for the 1-digit dates (eg. 01,02,03 etc.) and the month that is created (with a leading zero also for the 1-digit months eg. 01, 02, 03, etc)
  2. Instead of the duplicated sheet that always goes far right, to go right side of the "MASTER" the most recent days are always visible. Recent Date Last
Solution

  • I believe your goal is as follows.

    1. You want to set the new inserted sheet name in the format of dd/MM of today's date.
    2. When the new sheet is inserted, you want to put it just on the right side of "MASTER" sheet.

    If my understanding is correct, how about the following modification?

    In your showing script, it seems that the text of DD/MM is directly used as the sheet name. And, the created sheet is not moved.

    Modified script:

    function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('MASTER');
  var sheetName = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM');
  if (ss.getSheetByName(sheetName)) {
    console.log(`Same sheet name of "${sheetName}" is existing. Please confirm the sheet name again.`);
    return;
  }
  var sheet2 = sheet.copyTo(ss).setName(sheetName).activate();
  var idx = ss.getSheets().findIndex(s => s.getSheetName() == "MASTER") + 2;
  ss.moveActiveSheet(idx);

  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var p = protections[i];
    var rangeNotation = p.getRange().getA1Notation();
    var p2 = sheet2.getRange(rangeNotation).protect();
    p2.setDescription(p.getDescription());
    p2.setWarningOnly(p.isWarningOnly());
    if (!p.isWarningOnly()) {
      p2.removeEditors(p2.getEditors());
      p2.addEditors(p.getEditors());
      // p2.setDomainEdit(p.canDomainEdit()); //  only if using an Apps domain
    }
  }
}

    • When this script is run, a new sheet is inserted as the sheet name of the format dd/MM of today's date. And, the new sheet is moved to just the right side of "MASTER" sheet.

    • In this modification, when the same sheet name exists in your Google Spreadsheet, a log with console.log(Same sheet name of "${sheetName}" is existing. Please confirm the sheet name again.); is shown, and the script is stopped. At that time, please remove or rename the existing sheet name.

    References: