CODEWITHSUNDEEP
google-apps-script
Fazila
Fazila

Reputation: 71

Google Apps Script error: “You do not have permission to access the requested document”

I'm trying to create a script where on submitting another spreadsheet gets updated with a date and other information and a pdf version of the sheet goes to another member of staff who can then approve the same. Whilst the code runs for me, other staff members cannot use the script as they keep getting a “You do not have permission to access the requested document” despite having editor access to all documents.

Any thoughts on why this might be happening. The code I want staff to be able to run is:

function sendToResponse() {
  
  var subject = sheet.getRange(3, 2).getValue();
  var rowOfTargetSubjectInMasterSheet = rowOfTarget(subject);
  var myLastYearGroupCell = countCellForTargetSheet(sheet, 12, 3);
  var myLastPeriodLengthCell = countCellForTargetSheet(sheet, 12 + myLastYearGroupCell + 5, 3);

  Logger.log(myLastYearGroupCell);
  Logger.log(myLastPeriodLengthCell);
    
  var stuGroup = sheet.getRange(12, 3, myLastYearGroupCell).getValues();
  targetSheet.getRange(rowOfTargetSubjectInMasterSheet + myLastYearGroupCell + 2, 5, myLastYearGroupCell).setValues(stuGroup);
     
  var lengthTeaching = sheet.getRange(17 + myLastYearGroupCell, 3, myLastPeriodLengthCell).getValues();
  targetSheet.getRange(rowOfTargetSubjectInMasterSheet + myLastPeriodLengthCell + 2, 7, myLastPeriodLengthCell).setValues(lengthTeaching);
  
  var lengthStaffing = countCellFor(teacherRow);
    
  var staffing = sheet.getRange(teacherRow + 1, 1, lengthStaffing, 3).getValues();
  targetSheet.getRange(rowOfTargetSubjectInMasterSheet, 8, lengthStaffing, 3).setValues(staffing);
  
  var ptDays = sheet.getRange(teacherRow + 1, 5, lengthStaffing, 1).getValues();
  targetSheet.getRange(rowOfTargetSubjectInMasterSheet, 12, lengthStaffing, 1).setValues(ptDays);
  
  var lengthClass = countCellFor(classSheetRow);
  
  var classes = sheet.getRange(classSheetRow + 1, 1, lengthClass, 3).getValues();
  targetSheet.getRange(rowOfTargetSubjectInMasterSheet, 13, lengthClass, 3).setValues(classes);
  
  var ctlName = sheet.getRange(4, 2).getValue();
  var ctlEmail = sheet.getRange(5, 2).getValue();
  var sltName = sheet.getRange(6, 2).getValue();
  var sltEmail = sheet.getRange(7, 2).getValue();
  var emailDate = targetSheet.getRange(rowOfTargetSubjectInMasterSheet, 16);
  var sltResponse = targetSheet.getRange(rowOfTargetSubjectInMasterSheet, 17).getValue();
  var cell = targetSheet.getRange(rowOfTargetSubjectInMasterSheet, 1);
  var workingRow = cell.getRow();
  
  if (sltResponse == '') {
    
    var approve = url + "?approval=Approved" + "&reply=" + ctlEmail + "&r=" + workingRow + "&sltName=" + sltName;
        
    var email = HtmlService.createTemplateFromFile('CTLRequestResponseSLTEmail');
    email.sltName = sltName;
    email.subject = subject;
    email.CTL = ctlName;
    email.approve = approve;
        
    var response = email.evaluate().getContent();
        
    MailApp.sendEmail({
         to: sltEmail,
         subject: 'Timetable 2020-2021: CTL Request for ' + subject,
         htmlBody: response,
         attachments: [SpreadsheetApp.getActiveSpreadsheet()]
       });
  
    var d = new Date();
    emailDate.setValue(d);
  };
}

function doGet(e) {
  
  // Update Spreadsheet
  var answer = e.parameter.approval;
  var workingRow = e.parameter.r;
  var d = new Date()
   
  targetSheet.getRange(workingRow, 17).setValue(answer);
  targetSheet.getRange(workingRow, 16).setValue(d);
  
  //Email
  
  var ctlEmail = e.parameter.reply;
  var sltName = e.parameter.sltName;
  
  GmailApp.sendEmail(ctlEmail, "CTL Request 2020-2021", "Thank you for submitting your CTL Request for 2020-2021.  The form has been sent to your SLT Line Manager for approval.");
  
  var app = HtmlService.createHtmlOutput(sltName + " thank you for your response.");
  
  return app
  
}

I have deployed as a web app and tried the Execute the app as myself and user accessing the web app but neither work.

Thanks

Fazila

Upvotes: 4

Views: 2734

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

If you deploy the WebApp as yourself:

  • Make sure to set Who has access to the app: to Anyone or Anyone, even anonymous to make sure that any user will have access to the WebApp
  • Mind that all requests (like e.g. GmailApp.sendEmail()) will be executed on your behalf - that is under your name

If you deploy the WebApp as User accessing the web app:

  • Make sure that you set the sharing permission for the spreadsheet the code runs in a such a way that all users / the users whom you want to run the WebApp have edit permissions

Upvotes: 1

Related Questions