Hi all!
I have an AppScript linked to a Google Sheet. The Sheet looks as such:
In column A I have URLs all linking to Google Doc files (Sheet, Slides, Doc, etc). In some subsequent columns I have metadata for those files that I retrieve with the App Script, such as creator/owner of the document, recent edits, etc.
The AppScript permission is tied to my user account. My user account is part of a GSuite organization. All the documents in the list have the permission for "anyone in the organization can view/comment/edit". I can personally open and at least view (if not do more) each document in that list, because my user is part of the organization (I opened them to check).
However, for some URLs I get the following error: GoogleJsonResponseException: API call to drive.revisions.list failed with error: The authenticated user does not have the required access to the file
This is despite the fact that I can actually open the URLs with my user account.
I made sure the "https://www.googleapis.com/auth/drive" scope is included, and Drive service is enabled.
I have seen this thread with the same error, but the context is different. In my case, I checked the individual files for their permissions, and my user has access to the files.
I am at a loss at what else could cause this problem. Appreciate any pointers!
Here is the current AppScript code for reference (ignore the fact that it is not very good code, currently focusing on making it work):
function getData() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OVERVIEW");
const lastRow = sheet.getLastRow();
const range = sheet.getRange("A2:A" + lastRow);
for (var i = 0; i < lastRow - 1; i++) {
const cell = range.getCell(i + 1, 1);
const value = cell.getValue();
const row = i + 2;
if (value.toString() != "") {
const id = value.split("/")[5];
if (id) {
// Initialize variables to store values
let title, owner, lastEditDate, lastEditorEmail, lastCommentDate, lastCommentAuthor;
try {
let file;
try {
file = DriveApp.getFileById(id);
} catch (error) {
// Handle the error, for example:
Logger.log("Error fetching file for row " + row + ": " + error);
continue; // Skip to the next iteration to process the next row
}
// Title
title = file.getName();
// Owner
try {
owner = file.getOwner().getEmail();
} catch (error) {
owner = "";
}
// Last edit
const revisionsList = Drive.Revisions.list(id);
if (revisionsList && revisionsList.items.length > 0) {
revisionsList.items.sort(function(a, b) {
return new Date(b.modifiedDate) - new Date(a.modifiedDate);
});
lastEditDate = revisionsList.items[0].modifiedDate;
lastEditorEmail = revisionsList.items[0].lastModifyingUser.emailAddress;
}
// Last commented on
const commentsList = Drive.Comments.list(id).items;
if (commentsList && commentsList.length > 0) {
commentsList.sort(function(a, b) {
return new Date(b.createdDate) - new Date(a.createdDate);
});
lastCommentDate = commentsList[0].createdDate;
const formattedLastCommentDate = Utilities.formatDate(new Date(lastCommentDate), "GMT+1", 'dd/MM/yyyy');
lastCommentDate = formattedLastCommentDate;
lastCommentAuthor = commentsList[0].author.displayName;
}
// Format Last Edit Date in DD/MM/YYYY format
if (lastEditDate) {
const formattedLastEditDate = Utilities.formatDate(new Date(lastEditDate), "GMT+1", 'dd/MM/yyyy');
lastEditDate = formattedLastEditDate;
}
} catch (error) {
// Handle error for inaccessible file or other exceptions
Logger.log("Error fetching data for row " + row + ": " + error);
}
// Set values in the row, only if they were retrieved successfully
sheet.getRange(row, 5).setValue(title || null);
sheet.getRange(row, 9).setValue(owner || null);
sheet.getRange(row, 10).setValue(lastEditDate || null);
sheet.getRange(row, 11).setValue(lastEditorEmail || null);
sheet.getRange(row, 12).setValue(lastCommentDate || null);
sheet.getRange(row, 13).setValue(lastCommentAuthor || null);
}
}
}
}
LinkedIn
Twitter
