You would think that accessing the name or email of the creator of a Google Drive file would be super easy. In the Google Drive display, there is already a provision for the creation date and the name of the file’s owner, and in the Shared Drive, we can see the creator’s name. It is hardly a security issue.
We can even fairly easily retrieve the details of the user who last modified the file using the Drive API (Google Apps Script Advanced Sheets Service) with the lastModifyingUser property of the returned file object, which gives us a happy little list of User properties that include the modifying user’s display name and email.
With the file creator, all we want is similar information, and it would be generated once and remain immutable. Seems like a no-brainer.
So why is it so hard to get the file creator details in Google Drive programmatically?
Table of Contents
Why do you even need to Get The File Creator, Yagi?
Whelp, it boils down to traceability, support and security. For my previous clients and my current employer, we run complex Google Workspace tools that harness structured directories and files that are created and interacted with very intentionally.
The base template structure of these files, typically Google Sheets and Google Docs, are multifacited and requires some level of training to understand. On top of that, the file creator sets the parameters for the work that will be done in the file. That creator then may pass on ownership of the file to a Shared Drive (if it is not already in there), effectively losing trace of the creator (well, not without a convoluted discovery process).
If users need to query the file creator, they may not know who it is. If we need to track file creation by user, we can’t.
Product: List all Files and Folders in a Google Drive Directory in Google Sheets
Why not just use the Google Admin Console?
If you are a small department in an organisation that is many thousands of employees strong, then you may not have access to the admin console to audit file creation.
So, is it just impossible to get the file creator? You seem to be doing a whole lot of complaining, but taking no action!
Hold on there, just a minute! Fair enough, I am having a bit of a whinge, but in Cory Doctorow fashion, I will provide you with some solutions to my over-catastrophisation of this whole thing.
We will look at a rather defeatist but simple approach to this first, using Google Apps Script DriveApp, before diving into the complex and API request-intensive workaround I came up with that will get you as close as we can to the owner of the file.
Finally, if you think that it is worthwhile for you, then add a comment or, at the very least, a plus 1 to the Issue Tracker feature request for this over at Google:
https://issuetracker.google.com/issues/484057244
Youtube Video
A very short note on the Code
I have created a reference global variable called REF in a ‘Globals’ file where I have stored the test file references for convenience. You can do the same and add your own test files here:
|
1 2 3 4 5 |
const REF = { test_file_id: "###MY_Drive_File####", sharedDrive_test_file_id: "1###Shared_Drive_File####", error_file: "1111111111111111111111111111111111WEEEEE", } |
I give up! I’ll just use the File Owner!
I’m a little disappointed in you, particularly when I wrote all that code for the better solution, but I’m not your mother, nor am I your AI waifu.
My Drive
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
/** * Get's the owner of the file and the creation date and * assumes the owner is the creator. * Only useful for 'My Drive' files. * * @param {string} fileId - id of the target file. * @returns {{owner:string, creationDate: string}} */ function getMyDriveFileCreatorByOwner_DriveApp(fileId){ const file = DriveApp.getFileById(fileId); const owner = file.getOwner().getEmail(); const creationDate = file.getDateCreated(); return {owner,creationDate} } function test_getMyDriveFileCreatorByOwner_DriveApp(){ console.log("My Drive File") const result = getMyDriveFileCreatorByOwner_DriveApp(REF.test_file_id) console.log(result) console.log("Shared Drive File - ERROR") const resultSharedDrive = getMyDriveFileCreatorByOwner_DriveApp(REF.sharedDrive_test_file_id) console.log(resultSharedDrive) } |
It’s quite likely that the owner of a file stored in MyDrive is the creator, but not always. Sometimes users will create a file and hand ownership over to another user.
This little function uses the Google Apps Script built-in DriveApp Class. First, I grab the target file. The File subclass can retrieve the owner (.getOwer()) and creation date (.getDateCreated()).
You will see when you run the test function that an error occurs in the Shared Drive test because shared drives don’t have owners. This means that the getOwner() method will return null making getEmail() impossible to access. This could be handled with a try-catch or if statement, but you’re better off being slightly less lazy and using the Drive API Advanced Service.
Shared Drive Owner
Nothing is ever easy. If you thought you could get away with grabbing the owner credentials with a single Google Drive API call, then I have some bad news.
The Google Drive API can grab a file owner’s details, including their email, along with the creation time for the file, but it behaves just like DriveApp in that if the file is not in a user’s My Drive directory, then all you will get back is undefined.
So, on the condition that there is no ‘owner’, we can safely suspect that it is a shared drive file and use the Drive Permissions resource to try and vaguely guess the file creator from the roles assigned to the users.
There is a role hierarchy to roles that we can follow to assign creator/owner status, which you can find here (Roles and Permissions in Google Drive). So the order would be:
- owner
- organizer
- fileOrganizer
Note that the Drive API v3 requires activation via the Services button in the Google Apps Script IDE.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
/** * Get's the ownership of a file base on a heirachy or roles (owner, * organizer, fileOrganizer) and the creation date and assumes one of * the these people is the defacto creator of the file. * Only useful for 'My Drive' files. * * @param {string} fileId - id of the target file. * @returns {[{ownershipHeirachy:[{role:string, email:string}], creationDate: string}, error]} */ function getFileCreatorByOwnerForSharedDrive_DriveAPI(fileId){ let ownershipHeirachy = []; let error = null; // --- GET FILE CREATION TIME AND POSSIBLY OWNER FROM DRIVE API --- const queryParams = { supportsAllDrives: true, fields: "createdTime,owners(emailAddress)", } let file = null try{ file = Drive.Files.get(fileId, queryParams); }catch(error){ console.error(`Error (Drive API): ${e}`) return [null, e.message]; } const creationDate = file.createdTime console.log("FILE:\n", file) if(file.owners){ file.owners.forEach(owner => { ownershipHeirachy.push({role: "owner", email: owner.emailAddress}) }) }else{ // --- IF NOT OWNER GET OWNER HEIRACHY FROM PERMISSIONS --- queryParams.fields = "permissions(emailAddress,role)" let filePermissions try{ filePermissions = Drive.Permissions.list(fileId, queryParams) }catch(e){ console.error(`Error (Drive Activity API): ${e}`) return [null, e.message]; } console.log("FILE PERMISSIONS: \n",filePermissions) const emailsByRole = { organizer: [], fileOrganizer: [] } filePermissions.permissions.forEach(entry =>{ const role = entry.role if(emailsByRole.hasOwnProperty(role)){ emailsByRole[role].push(entry.emailAddress) } }) if(emailsByRole.organizer.length > 0){ emailsByRole.organizer.forEach(user => { ownershipHeirachy.push({role: "organizer", email: user}) }) }else{ emailsByRole.organizer.forEach(user => { ownershipHeirachy.push({role: "file organizer", email: user}) }) } } return [{ownershipHeirachy,creationDate},error] } function test_getFileCreatorByOwnerForSharedDrive_DriveAPI(){ console.log("### My Drive File") const result = getFileCreatorByOwnerForSharedDrive_DriveAPI(REF.test_file_id) console.log(JSON.stringify(result, null, 2)) console.log("### Shared Drive File - ERROR") const resultSharedDrive = getFileCreatorByOwnerForSharedDrive_DriveAPI(REF.sharedDrive_test_file_id) console.log(JSON.stringify(resultSharedDrive, null, 2)) } |
Reviewing the code above, you can see that we are not being greedy API goblins and are only retrieving the additional permissions from the Permissions resource if we need it. We are also limiting our returned data by setting the ‘field’ property (often neglected in Google documentation).
Also note that the Drive File resource also includes the supportsAllDrives flag. If you miss this one out your Shared Drive data will disappear.
Best Possible Chance Of Finding the Creator
Ooh, it suddenly got quite existential, didn’t it?
Our final and most successful attempt at finding the file’s creator requires us to use the Drive Activity service in conjunction with the People API. So head over to Services in your IDE and add those two.
Why both? My guess is that at the time Google was creating the Drive Activity API, they had discovered that a single user can have multiple email aliases. Those aliases are still always unique to the user, but it would probably be a better investigation metric to have one unifying ID for the user.
Enter personName.
And no, personNameis not the name of the user, as you would expect. It is the user’s id, for example people/123456789123456789. This stinks of a last-minute development decision, but we’ve all been there.
So with a now human-readable understanding of what personNameis we need some other tool to find the user’s email. This is where the People API comes in.
The people API is made for finding all the metadata assigned to the personName, that is, of course, if your Google Admins have set their Directory to visible for all or some special group of which your users are a part.
Now the People API in Google Apps Script is, ironically, fairly unfriendly and its error handling will rarely tell you why it is cross with you.
It won’t automatically add the scopes that you need to your ‘appsscript.json’ file either. Rather, it just won’t speak to you until you figure it out.
So you will need to head over to the Project Settings tab and check Show ‘appsscript.json’ manifest file in editor. Then head back and add all the scopes in your project:
- “https://www.googleapis.com/auth/directory.readonly”
- “https://www.googleapis.com/auth/userinfo.profile”
- “https://www.googleapis.com/auth/userinfo.email”
|
1 2 3 4 5 6 7 8 |
"oauthScopes": [ "https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/drive.activity.readonly", "https://www.googleapis.com/auth/contacts.other.readonly", "https://www.googleapis.com/auth/directory.readonly", "https://www.googleapis.com/auth/userinfo.profile", "https://www.googleapis.com/auth/userinfo.email" ], |
Now you should be good to run your code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
// ############# // Apps Scirpt Drive Activiy API and People API approach to get creator of the file // ############# /** * Gets the target file's Creator Email by looking up the file and searching for the `CREATE` action. * If it exists, then the actor id of the creator is retrieved and parse through the People API * to get the actor's email. * * Note, the user running the file may not have access to the full history of the drive activity if * they were added to the file or the file's directory before the file was created. * * Ths function used the following Google Advanced Services: * - DriveActivity * - Drive * - People - Works best if 'Contact Sharing' is set to on in the Google Admin Console. * * The function requires the addition of custom scopes which means that all project scopes need to be added to the * `appsscript.json` manifest file. * Scopes required: * 1. "https://www.googleapis.com/auth/directory.readonly" * 2. "https://www.googleapis.com/auth/userinfo.profile" * 3. "https://www.googleapis.com/auth/userinfo.email" * * @param {String} - fileId - the Google Drive File ID * @returns {[{fileId: String, creator: String}, error:string]} */ function getFileCreator_FilterByCreateMove(fileId) { let creatorEmail = "Unknown"; let creatorId = null; // --- GET CREATOR (VIA DRIVE ACTIVITY) --- const resourceName = 'items/' + fileId; try { const response = DriveActivity.Activity.query( { itemName: resourceName, filter: "detail.action_detail_case:(CREATE MOVE)", }, { fields: "activities(actors(user.knownUser.personName),primaryActionDetail),nextPageToken" } ); const activities = response.activities; console.log(JSON.stringify(response, null, 2)) if (activities && activities.length > 0) { const oldestActivity = activities[activities.length - 1]; // Extract the actor (the person who did the action) creatorId = oldestActivity.actors[0].user.knownUser.personName; } }catch(e){ console.error(`Error (Drive Activity API): ${e}`) return [null, e.message]; } // --- GET EMAIL (VIA THE PEOPLE API) --- if(creatorId !== null){ try{ const person = People.People.get(creatorId, { personFields: 'emailAddresses', }); creatorEmail = person.emailAddresses ? person.emailAddresses[0].value : "Unknown Email"; }catch(e){ console.error(`Error (People API): ${e}`) return [null, e.message];; } } const result = { fileId: fileId, creator: creatorEmail, }; return [result, null]; } function test_getFileCreator_FilterByCreateMove(){ let result = null; let error = null; console.log("### My Drive File"); [result, error] = getFileCreator_FilterByCreateMove(REF.test_file_id); console.log(result,":", error); console.log("### Shared Drive File"); [result, error] = getFileCreator_FilterByCreateMove(REF.sharedDrive_test_file_id); console.log(result,":", error); console.log("### Error File"); [result, error] = getFileCreator_FilterByCreateMove(REF.errorFile); console.log(result,":", error); } |
Getting the File Creator Id from Drive Activity
Here we use the DriveActivity.Activity.query() method. This method takes a resourceName, which is the file ID with items/ prepended to it.
You can really see that the designer of this new API approach loved their abstraction.
The query method takes a request body object and the ever-forgotten from the documentation (grumble grumble) field property field mask.
A key property of the Drive Activity Query field is the filter property. This property allows us to filter by time or by a particular action type. For us, we want to filter out any actions that either CREATE the file or MOVE it. Why also MOVE? Well, if the file is moved from, say, My Drive, into a Shared Drive, then we might not be able to get the CREATE instance due to our permissions, but we will be able to get the MOVE action.
The query returns an array object of activities. Each activity contains the primary action (referred to as CREATE or MOVE in our case) and an array of actors who did the action. Typically, this is one actor.
Each item in the ‘actors’ array contains the actor’s details as a user object that nests down into the knownUser.PersonName, which, we have discovered, is not the actor’s actual name, but rather it is their id.
knownUsersounds quite ominous and suggests that we may not always be able to retrieve the user of the action. So we need to keep that in mind as a check when we are writing our code.
Getting the Creator’s Email from their ID
Now that we have the creator’s ID, we call the People API to retrieve their email with the ‘get’ method. ‘Get’ takes a mysterious resourceName as a parameter, which is the PersonName (e.g. “people/123456789123456789”) that we retrieved from the Drive Activity call.
The People API has its own way of declaring returnable fields with ‘personField’. At least they are in the docs, so good job team 👍.
Its funny ,if you look at the People API documentation it really looks like it is in its own world. The API structure differs from many of the other Google Workspace APIs and even the Docs Page layout is different.
There is a whole lot of stuff we can grab from this request, like the user’s name, locale, and cover photo. That is, if the user has provided it, and you have added the appropriate scopes. You can find the scopes at the bottom of the documentation for the People.People.get method.
Some Final Notes
So what script did you end up choosing? Did you make any interesting modifications for your own project?
Did you try any other approaches? I know I did. I also took a look at Drive API’s Revisions method, but there is a very clear warning at the top of the docs that files with large revision histories will have older revisions omitted. I even gave this a test run with some work files with many thousands of revisions over 12 months, and found that the revisions stored are not that old. Dead end there.
The final script I shared with you was the best I could put together to find the creator of a file. The good news is that the data that I am grabbing is static, and in projects, I would ensure that if I already have the creator’s email, I would not go looking for it again.
Having said that, I do think that there is a real argument for having the file creator’s email in the metadata of the Drive API File object. If you agree, drop a comment or a plus one on the feature request here.
~ Yagi