google-apps-scriptgoogle-sheetstimestamp

How to automatic add timestamp to duplicated google sheet?


I am running the following script for Google Sheets duplication:

   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
       }
     }
   }

from this answer by Tanaike.

How do I add a Timestamp in a specific cell inside the duplicated new sheet that is locked from editors?


Solution

  • I understood that OP's expected situation is as follows.

    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
        }
      }
    }

    In this case, when the user "A" runs the script by clicking the button, the script is run as the user "A". By this, the user "A" can edit the cell even when the cell is protected. I understood that this was not the OP's expected result.

    In order to achieve this goal, I think that the same method as this thread can be used. But, from Second I have no idea how to write or edit your script. from OP's comment, I would like to propose a modified script for this situation.

    The script is run through the Web Apps because in this case, the script can be run as the owner of Spreadsheet even when the user "A" clicks the button. In order to use this, please do the following flow.

    Usage:

    Please do the following flow on the script editor.

    1. Deploy Web Apps.

    The detailed information can be seen in the official document.

    Please set this using the new IDE of the script editor.

    1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
    2. Please click "Select type" -> "Web App".
    3. Please input the information about the Web App in the fields under "Deployment configuration".
    4. Please select "Me" for "Execute as".
    5. Please select "Anyone with Google account" for "Who has access to the app:".
    6. Please click "Deploy" button.
    7. On the script editor, at the top right of the script editor, please click "click Deploy" -> "Test deployments".
    8. Copy Web Apps URL. It's like https://script.google.com/macros/s/###/exec. This URL is used with Python script.

    2. Sample script.

    Please copy and paste the following script to the script editor. And, please set the Web Apps URL to const webAppsUrl = "https://script.google.com/macros/s/###/dev";.

    const doGet = _ => duplicateSheetWithProtections(true);
    
    function duplicateSheetWithProtections(e) {
      if (!e) {
        const webAppsUrl = "https://script.google.com/macros/s/###/dev"; // Please set your Web Apps URL.
        UrlFetchApp.fetch(webAppsUrl, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
        return;
      }
      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 protect1 = sheet2.getRange("B2").setValue(new Date()).protect(); // Added
      protect1.removeEditors(protect1.getEditors()); // Added
    
      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
        }
      }
    }
    
    // ### Please don't remove the following comment line.
    // DriveApp.getFiles(); // This comment line is used for automatically detecting a scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for requesting Web Apps.
    // ###
    

    3. Testing.

    When you click the button assigned with duplicateSheetWithProtections, the script is run. And, the script is run by Web Apps. By this, when the date object is put into cell "B2" and the cell "B2" is protected. In this case, the user who is not the owner of the Spreadsheet cannot edit "B2".

    Note:

    As an appendix, my 1st understanding of OP's question is as follows.

    When the script is run, OP wanted to put a date object to the cell "B2" of the new sheet. For this, I proposed the following 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 protect1 = sheet2.getRange("B2").setValue(new Date()).protect(); // Added
      protect1.removeEditors(protect1.getEditors()); // Added
      protect1.setWarningOnly(true); // Added
    
      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
        }
      }
    }

    But, from the discussion in this chat room, I could understand the OP's expected result. So, I proposed the above-modified script using Web Apps.

    References: