Need to set permissions on duplicate of google sheet created through script - Stack Overflow

admin管理员组

文章数量:1389530

I have a script that copies an existing Google Sheet and sends the link on predetermined days.

How do I enable anyone with the link to edit the new sheet?

function duplicateAndEmailSheet() {
  var sourceSpreadsheetId = '*original file ID*';
  var recipientEmail = '*My email*';
  var today = new Date();
  var dayOfWeek = today.getDay(); // 0 = Sunday, 1 = Monday, ..., 5 = Friday, 6 = Saturday
  
  // Proceed only if the day is not Saturday (6)
  if (dayOfWeek !== 6) {
    // Adjust the date for email subject to tomorrow's date
    var emailSubjectDate = new Date(today);
    emailSubjectDate.setDate(today.getDate() + 1);

    // If it's Friday, set the date to the upcoming Sunday
    if (dayOfWeek === 5) { 
      emailSubjectDate.setDate(today.getDate() + (7 - today.getDay()));
    }
    
    var subject = 'subjectline ' + emailSubjectDate.toDateString();
    var body = 'subjectline ' + emailSubjectDate.toDateString() + ': ';

    // Copy the spreadsheet
    var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
    var newSpreadsheet = sourceSpreadsheet.copy('Duplicate as Template ' + emailSubjectDate.toDateString());

    // Get the URL of the new spreadsheet
    var newSpreadsheetUrl = newSpreadsheet.getUrl();
    body += newSpreadsheetUrl;

    // Send the email
    MailApp.sendEmail(recipientEmail, subject, body);
  }
}

function createTriggers() {
  ScriptApp.newTrigger('duplicateAndEmailSheet')
    .timeBased()
    .everyDays(1)
    .atHour(6) // Hour set to 06:00
    .nearMinute(30)
    .create();
}

I have a script that copies an existing Google Sheet and sends the link on predetermined days.

How do I enable anyone with the link to edit the new sheet?

function duplicateAndEmailSheet() {
  var sourceSpreadsheetId = '*original file ID*';
  var recipientEmail = '*My email*';
  var today = new Date();
  var dayOfWeek = today.getDay(); // 0 = Sunday, 1 = Monday, ..., 5 = Friday, 6 = Saturday
  
  // Proceed only if the day is not Saturday (6)
  if (dayOfWeek !== 6) {
    // Adjust the date for email subject to tomorrow's date
    var emailSubjectDate = new Date(today);
    emailSubjectDate.setDate(today.getDate() + 1);

    // If it's Friday, set the date to the upcoming Sunday
    if (dayOfWeek === 5) { 
      emailSubjectDate.setDate(today.getDate() + (7 - today.getDay()));
    }
    
    var subject = 'subjectline ' + emailSubjectDate.toDateString();
    var body = 'subjectline ' + emailSubjectDate.toDateString() + ': ';

    // Copy the spreadsheet
    var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
    var newSpreadsheet = sourceSpreadsheet.copy('Duplicate as Template ' + emailSubjectDate.toDateString());

    // Get the URL of the new spreadsheet
    var newSpreadsheetUrl = newSpreadsheet.getUrl();
    body += newSpreadsheetUrl;

    // Send the email
    MailApp.sendEmail(recipientEmail, subject, body);
  }
}

function createTriggers() {
  ScriptApp.newTrigger('duplicateAndEmailSheet')
    .timeBased()
    .everyDays(1)
    .atHour(6) // Hour set to 06:00
    .nearMinute(30)
    .create();
}

• google-sheets
• google-apps-script

Share Improve this question Follow edited Mar 21 at 16:57 philipxy 15.2k66 gold badges4343 silver badges9797 bronze badges asked Mar 17 at 11:23 Zigi EkronZigi Ekron 111 silver badge 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You want to copy a spreadsheet and set the sharing so that anyone with the link can edit the spreadsheet.

Though the SpreadsheetApp allows a user to copy a spreadsheet, sharing (Access and Permission) are driven by the DriveApp.

Consider this script:

---

function copyThenSetSharing() {

  // get the ID of the "master Spreadsheet template"
  var sheetId = "<<insert spreadsheet id>>"

  // open the file in Drive to display existing access and sharing
  var file = DriveApp.getFileById(sheetId)
  Logger.log("DEBUG: The name of the Master Spreadsheet = "+file.getName()+", sharing access = "+file.getSharingAccess()+" and sharing permission is "+file.getSharingPermission())

  // copy the master spreadsheet and get the id of the copied file
  var ss = SpreadsheetApp.openById(sheetId)
  var newSheet = ss.copy(`so_79514431_copy`)
  var newSheetId = newSheet.getId()

  // open the new file in Drive and display the inital access and permission settings
  var newFile = DriveApp.getFileById(newSheetId)
  Logger.log("DEBUG: BEFORE: The new name of the 'copied' spreadsheet = "+newFile.getName()+", sharing access = "+newFile.getSharingAccess()+" and sharing permission is "+newFile.getSharingPermission())

  // change sharing access and permission for the copied spreadsheet
  newFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK,DriveApp.Permission.EDIT)
  Logger.log("DEBUG: AFTER: The new name of the 'copied' spreadsheet = "+newFile.getName()+" and sharing access = "+newFile.getSharingAccess()+" and sharing permission is "+newFile.getSharingPermission())

}

---

• View sharing details:

  • getSharingAccess
  • getSharingPermission

• Set sharing details:

  • setSharing(accessType, permissionType)
    • accessType
    • permissionType

---

Sample output

The name of the Master Spreadsheet = so_79514431_master, sharing access = PRIVATE and sharing permission is NONE

DEBUG: BEFORE: The new name of the 'copied' spreadsheet = so_79514431_copy, sharing access = PRIVATE and sharing permission is NONE

DEBUG: AFTER: The new name of the 'copied' spreadsheet = so_79514431_copy, sharing access = ANYONE_WITH_LINK and sharing permission is EDIT

本文标签： Need to set permissions on duplicate of google sheet created through scriptStack Overflow