javascript - Student Gradebook. Unable to complete script because of "Access denied: DriveApp" - Stack Overflo

admin管理员组

文章数量:1023213

My script takes 35 student names from the Sheet 'Studenti'. Then finds the proper folder with their name (i.e. 'Smith Peter'), removes old pdf document with old grades holding their name (i.e 'Smith Peter.pdf') and saves to the folder ('Smith Peter') a newly made pdf document with new grades ('Smith Peter.pdf'). And goes to take another student from the list.

The problem: after 1,2 or 3 iterations the script stops saying: Access denied: DriveApp. (line 22, file "Code": files.next().setTrashed(true);). A week ago the script was working without problems, I tried to change names, folders, destinations, source sheets and checked advice here. But no success. I don't know what is wrong with the line 22. Please help!

The code is enclosed

Execution transcript (shows where the script has stopped)

function generatePdf() {

var report = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Gradebook ready to save as pdf
var zasobnik = SpreadsheetApp.openById('1eygLDH0iJoXfcVOMIin0hnKpMF59HszmXZosPKvwYWc');  
var zasobsheet = zasobnik.getSheetByName("Studenti"); // list of 35 students with names, Folder IDs
var data = zasobsheet.getDataRange().getValues(); 
  
for (var i = 1; i < data.length; i++){
    report.getRange('B2').setValue(data[i][0]); // setting student's name in a gradebook (first column)
    report.getRange('B3').setValue(' '); // just to jump from B2 to B3
  
var pdf = DriveApp.getFileById('1WHwm7xK28Orj22RxaKWEsCl3UWvWxYUhRg4ZGf87-GQ'); // ID of Gradebook (IF of ActiveSpreadsheet)
var theBlob = pdf.getBlob().getAs('application/pdf').setName(data[i][0] + ".pdf");
var folder = DriveApp.getFolderById(data[i][2]); // third column
  
var files = DriveApp.getFilesByName(data[i][0] + ".pdf"); // pdf with old grades
      while (files.hasNext()) {    
       files.next().setTrashed(true);    
   }
var newFile = folder.createFile(theBlob); // creating pdf with new grades in the student's folder
 
 }
}

My script takes 35 student names from the Sheet 'Studenti'. Then finds the proper folder with their name (i.e. 'Smith Peter'), removes old pdf document with old grades holding their name (i.e 'Smith Peter.pdf') and saves to the folder ('Smith Peter') a newly made pdf document with new grades ('Smith Peter.pdf'). And goes to take another student from the list.

The problem: after 1,2 or 3 iterations the script stops saying: Access denied: DriveApp. (line 22, file "Code": files.next().setTrashed(true);). A week ago the script was working without problems, I tried to change names, folders, destinations, source sheets and checked advice here. But no success. I don't know what is wrong with the line 22. Please help!

The code is enclosed

Execution transcript (shows where the script has stopped)

function generatePdf() {

var report = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Gradebook ready to save as pdf
var zasobnik = SpreadsheetApp.openById('1eygLDH0iJoXfcVOMIin0hnKpMF59HszmXZosPKvwYWc');  
var zasobsheet = zasobnik.getSheetByName("Studenti"); // list of 35 students with names, Folder IDs
var data = zasobsheet.getDataRange().getValues(); 
  
for (var i = 1; i < data.length; i++){
    report.getRange('B2').setValue(data[i][0]); // setting student's name in a gradebook (first column)
    report.getRange('B3').setValue(' '); // just to jump from B2 to B3
  
var pdf = DriveApp.getFileById('1WHwm7xK28Orj22RxaKWEsCl3UWvWxYUhRg4ZGf87-GQ'); // ID of Gradebook (IF of ActiveSpreadsheet)
var theBlob = pdf.getBlob().getAs('application/pdf').setName(data[i][0] + ".pdf");
var folder = DriveApp.getFolderById(data[i][2]); // third column
  
var files = DriveApp.getFilesByName(data[i][0] + ".pdf"); // pdf with old grades
      while (files.hasNext()) {    
       files.next().setTrashed(true);    
   }
var newFile = folder.createFile(theBlob); // creating pdf with new grades in the student's folder
 
 }
}

• javascript
• pdf
• google-sheets
• google-drive-api

Share Improve this question Follow asked Aug 28, 2017 at 13:35 Richard SmejkalRichard Smejkal 5155 bronze badges

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 4

After few experiments I found the roots of the problem but not the solution. The problem with "Access denied" was related to the fact, that I was not the owner of the file created by my script and only the owner of the file may trash or delete the file. Although I was author of the script and had edit rights to the file which was placed on my drive, it did not work. On the other hand I could delete/trash the file manually without problems (and it was misleading for me).

Lesson learnt: If you allow someone else to use your script that creates a new file and also trashes the old one than you probably cannot plete your script next time sice you are not allowed (Access denied) to trash the file created by other user (although the file was created on your own drive !).

Adjusted question: Does anybody know how to write the script to trash/delete the file of which I am not the owner ( but I have edit rights) and which was created by other user on my drive?

In my case, the temporary file is recreated on a regular basis, revoking my user permission would leave the original in the owner's Drive, which may have unintended consequences when collaborating.

Instead,

• I create a temporary folder.
• Move the file to the folder after the code is run.
• Delete the folder.

var fileToDelete = DriveApp.getFileById(fileId);
var parentFolder = fileToDelete.getParents().next();
// Create the temp-folder inside the current parent folder
var deleteFolder = parentFolder.createFolder("TempFolder");
fileToDelete.moveTo(deleteFolder);
DriveApp.getFolderById(deleteFolder.getId()).setTrashed(true)

Confirmed; the file and folder have been removed from my Drive as well as the Owner's Drive. Which contradicts the understanding that I had before implementing this code.

I have the same problem with trashing files, that I don't own. I found that

1. If you are on the list getEditors() or getViewers() I have the solution: Just revoke permission from yourself. file.revokePermissions(Session.getActiveUser()); and file will disappear.
2. Example 1 doesn't work if file has set isShareableByEditors() - if it's "false", then you can't do anything.
3. If you've added file to your drive which is publicly accessible for everyone with link, you can't revoke permissions, because you don't have one. app script return access denied. So, I thought that I can setTrashed(true). I was wrong. Google also return Access denied: DriveAp.

I didn't find a way to remove unwanted files from my drive in case 2 and 3 thru apps script. But it's still possible by clicking on them and choosing option "Remove".

In the SO post, the user is trying to delete the converted file and got the same error as you are.

I suspect you're hitting a rate limit. If you are doing more than 20 write operations (create, update, delete) in rapid succession, you need to throttle the requests to approximately one every 1.5 seconds.

Suspected reason is the user hits rate limit. Since you are also updating a data, there is also a possibility that this is the reason why it is working on the first few execution and reached the limit after.

And that is the only allotted maximum throughput you can achieve in Drive.

My script takes 35 student names from the Sheet 'Studenti'. Then finds the proper folder with their name (i.e. 'Smith Peter'), removes old pdf document with old grades holding their name (i.e 'Smith Peter.pdf') and saves to the folder ('Smith Peter') a newly made pdf document with new grades ('Smith Peter.pdf'). And goes to take another student from the list.

The problem: after 1,2 or 3 iterations the script stops saying: Access denied: DriveApp. (line 22, file "Code": files.next().setTrashed(true);). A week ago the script was working without problems, I tried to change names, folders, destinations, source sheets and checked advice here. But no success. I don't know what is wrong with the line 22. Please help!

The code is enclosed

Execution transcript (shows where the script has stopped)

function generatePdf() {

var report = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Gradebook ready to save as pdf
var zasobnik = SpreadsheetApp.openById('1eygLDH0iJoXfcVOMIin0hnKpMF59HszmXZosPKvwYWc');  
var zasobsheet = zasobnik.getSheetByName("Studenti"); // list of 35 students with names, Folder IDs
var data = zasobsheet.getDataRange().getValues(); 
  
for (var i = 1; i < data.length; i++){
    report.getRange('B2').setValue(data[i][0]); // setting student's name in a gradebook (first column)
    report.getRange('B3').setValue(' '); // just to jump from B2 to B3
  
var pdf = DriveApp.getFileById('1WHwm7xK28Orj22RxaKWEsCl3UWvWxYUhRg4ZGf87-GQ'); // ID of Gradebook (IF of ActiveSpreadsheet)
var theBlob = pdf.getBlob().getAs('application/pdf').setName(data[i][0] + ".pdf");
var folder = DriveApp.getFolderById(data[i][2]); // third column
  
var files = DriveApp.getFilesByName(data[i][0] + ".pdf"); // pdf with old grades
      while (files.hasNext()) {    
       files.next().setTrashed(true);    
   }
var newFile = folder.createFile(theBlob); // creating pdf with new grades in the student's folder
 
 }
}

My script takes 35 student names from the Sheet 'Studenti'. Then finds the proper folder with their name (i.e. 'Smith Peter'), removes old pdf document with old grades holding their name (i.e 'Smith Peter.pdf') and saves to the folder ('Smith Peter') a newly made pdf document with new grades ('Smith Peter.pdf'). And goes to take another student from the list.

The problem: after 1,2 or 3 iterations the script stops saying: Access denied: DriveApp. (line 22, file "Code": files.next().setTrashed(true);). A week ago the script was working without problems, I tried to change names, folders, destinations, source sheets and checked advice here. But no success. I don't know what is wrong with the line 22. Please help!

The code is enclosed

Execution transcript (shows where the script has stopped)

function generatePdf() {

var report = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Gradebook ready to save as pdf
var zasobnik = SpreadsheetApp.openById('1eygLDH0iJoXfcVOMIin0hnKpMF59HszmXZosPKvwYWc');  
var zasobsheet = zasobnik.getSheetByName("Studenti"); // list of 35 students with names, Folder IDs
var data = zasobsheet.getDataRange().getValues(); 
  
for (var i = 1; i < data.length; i++){
    report.getRange('B2').setValue(data[i][0]); // setting student's name in a gradebook (first column)
    report.getRange('B3').setValue(' '); // just to jump from B2 to B3
  
var pdf = DriveApp.getFileById('1WHwm7xK28Orj22RxaKWEsCl3UWvWxYUhRg4ZGf87-GQ'); // ID of Gradebook (IF of ActiveSpreadsheet)
var theBlob = pdf.getBlob().getAs('application/pdf').setName(data[i][0] + ".pdf");
var folder = DriveApp.getFolderById(data[i][2]); // third column
  
var files = DriveApp.getFilesByName(data[i][0] + ".pdf"); // pdf with old grades
      while (files.hasNext()) {    
       files.next().setTrashed(true);    
   }
var newFile = folder.createFile(theBlob); // creating pdf with new grades in the student's folder
 
 }
}

• javascript
• pdf
• google-sheets
• google-drive-api

Share Improve this question Follow asked Aug 28, 2017 at 13:35 Richard SmejkalRichard Smejkal 5155 bronze badges

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 4

After few experiments I found the roots of the problem but not the solution. The problem with "Access denied" was related to the fact, that I was not the owner of the file created by my script and only the owner of the file may trash or delete the file. Although I was author of the script and had edit rights to the file which was placed on my drive, it did not work. On the other hand I could delete/trash the file manually without problems (and it was misleading for me).

Lesson learnt: If you allow someone else to use your script that creates a new file and also trashes the old one than you probably cannot plete your script next time sice you are not allowed (Access denied) to trash the file created by other user (although the file was created on your own drive !).

Adjusted question: Does anybody know how to write the script to trash/delete the file of which I am not the owner ( but I have edit rights) and which was created by other user on my drive?

In my case, the temporary file is recreated on a regular basis, revoking my user permission would leave the original in the owner's Drive, which may have unintended consequences when collaborating.

Instead,

• I create a temporary folder.
• Move the file to the folder after the code is run.
• Delete the folder.

var fileToDelete = DriveApp.getFileById(fileId);
var parentFolder = fileToDelete.getParents().next();
// Create the temp-folder inside the current parent folder
var deleteFolder = parentFolder.createFolder("TempFolder");
fileToDelete.moveTo(deleteFolder);
DriveApp.getFolderById(deleteFolder.getId()).setTrashed(true)

Confirmed; the file and folder have been removed from my Drive as well as the Owner's Drive. Which contradicts the understanding that I had before implementing this code.

I have the same problem with trashing files, that I don't own. I found that

1. If you are on the list getEditors() or getViewers() I have the solution: Just revoke permission from yourself. file.revokePermissions(Session.getActiveUser()); and file will disappear.
2. Example 1 doesn't work if file has set isShareableByEditors() - if it's "false", then you can't do anything.
3. If you've added file to your drive which is publicly accessible for everyone with link, you can't revoke permissions, because you don't have one. app script return access denied. So, I thought that I can setTrashed(true). I was wrong. Google also return Access denied: DriveAp.

I didn't find a way to remove unwanted files from my drive in case 2 and 3 thru apps script. But it's still possible by clicking on them and choosing option "Remove".

In the SO post, the user is trying to delete the converted file and got the same error as you are.

I suspect you're hitting a rate limit. If you are doing more than 20 write operations (create, update, delete) in rapid succession, you need to throttle the requests to approximately one every 1.5 seconds.

Suspected reason is the user hits rate limit. Since you are also updating a data, there is also a possibility that this is the reason why it is working on the first few execution and reached the limit after.

And that is the only allotted maximum throughput you can achieve in Drive.

本文标签：