How To Audit File Sharing of Personal Google Drive.

We all use Google Drive’s file-sharing feature so frequently that it's easy to lose track of who has access to what. Over time, we may forget which files or folders we’ve shared and what level of access others have, especially with old clients, colleagues, or friends we’re no longer in touch with. 

Imagine if one of those shared files contains sensitive or confidential information, and it’s still accessible to someone who no longer needs it. That was exactly the situation I found myself in recently. So, I decided to create an audit log to track everything. It was a tedious and time-consuming process, but I can't do this every time I share some files with someone, so I decided to automate this task. Let's learn how?

Audit Google Drive File Sharing in Google Sheets.

There is no direct method for storing Google Drive file-sharing logs, so we are going to use Google App Script to write our own custom code to store all the required details directly inside a Google Sheet.

In this process, we will automatically scan your drive for all the files to get the required details like file name, file type, file path, file owner, link, type of access, user IDs, shared date, and last modified date. 

After collecting all these details, we will store them inside a clean new Google Sheet. At the end, we will set a trigger so our code will perform this activity every week to update your Google Sheet with new records.

Note: If you are using a custom domain with the premium version of Google Workspace that you can use this feature for free by using the Add-on available in Google Marketplace.

Here is a step-by-step process for automating auditing Google Drive Files.

Step 1: Create a Google Sheet for Audit Logs.

Open a new Google Sheet in your Google Drive and name the sheet as well as the first tab of the sheet. In my case, the sheet name is "Audit_Sheet" and the sheet's first tab name is "Audit". The sheet name can be anything based on your choice.

In the top-right corner of the sheet, click on the share button drop-down and copy the link. Keep this link handy, as we are going to use this link in our App Script Code.

Step 2: Open Google App Script Code.

In the sheet, click on Extensions > App Script. A new window will open with the Google App Script code editor. Make sure that your app script page is logged in with the same ID for which you want to audit file sharing details.

⚠️ Warning

If you're encountering a "400 Bad Request" error when running your Google Apps Script, it may be due to a mismatch between the Google account currently active in the browser and the one used to authorize the script.

Google Apps Script tends to "remember" the account that was used the last time you accessed the Script Editor. If you're now opening or running the script using a different account in the same browser session, the script can fail to authenticate properly, resulting in this error.

Step 3: Write Google App Script Code.

Copy and paste the full code into the Google Apps Script Code Editor, then click on “Untitled project” at the top left and give your project a meaningful name (e.g., “Drive Sharing Audit Tool”). In the second line of the code, replace the placeholder with the link to your Google Sheet where you want to store the audit logs. In the fourth line, enter the name of the first sheet (tab) in that Google Sheet, which you can find in the lower-left corner of the spreadsheet (e.g., “Sheet1” or “AuditLog”).

Script Code:

function scanAllDriveFilesToSheet() {
  const sheetUrl = '<Paste_Google_Sheet_Link_Here>';
  const ss = SpreadsheetApp.openByUrl(sheetUrl);
  const sheet = ss.getSheetByName("<Paste_Sheet_1_Name_Here>");
  sheet.clearContents(); // Optional: clear old logs

  // Set headers
  sheet.appendRow(["File Name", "File Type", "File Path", "Owner", "Link", "Access Type", "User IDs", "Shared Date", "Last Modified"]);

  const files = DriveApp.getFiles();
  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();
    const fileType = file.getMimeType();
    const filePath = file.getParents().hasNext() ? file.getParents().next().getName() : "Root";
    const owner = file.getOwner() ? file.getOwner().getEmail() : "Unknown";
    const url = file.getUrl();
    const lastModified = file.getLastUpdated();
    const sharedDate = file.getDateCreated();
    const viewers = file.getViewers().map(u => u.getEmail()).join(", ");
    const editors = file.getEditors().map(u => u.getEmail()).join(", ");
    const access = (editors ? "Edit: " + editors : "") + " | View: " + viewers;

    sheet.appendRow([fileName, fileType, filePath, owner, url, access, viewers + " " + editors, sharedDate, lastModified]);
  }
}

Step 3: Give Permission to Run the Script.

After making all the required changes to the script, click the Save icon and then click the Run ▶️ button to execute the script. If this is your first time running an Apps Script, Google will prompt you to authorize the script to access your Google account. This access is necessary for the script to read and write data in your Google Drive in order to perform the audit.

When you click "Review Permissions", a pop-up window may appear saying "Google hasn’t verified this app". This is normal for custom scripts. Click on Advanced, then select "Go to <Project_Name>" (your project name will appear there). 

Google will then ask you to review and approve access by checking the required boxes. Select both the checkbox and click on Continue. This is a one-time, safe process and ensures the script can function as intended.

Code will start executing and you can open your Google sheet to see the updated audit logs for the the files present in your Google Drive.

Step 4: Schedule the Automation.

If you don't want to run the Script every time to see the updated details, then you can schedule this script to run automatically. To schedule your Apps Script to run weekly, open the Script Editor and click on the clock icon 🕒 in the left toolbar (called Triggers). Click "+ Add Trigger", choose the function you want to run, set the event source to Time-driven, and then choose time based trigger as "Week timer" and select the desired day and time.

Once saved, the script will automatically run every week based on your selected schedule and no manual execution needed.

⚠️ Important: This script reads sensitive Drive data, so do not share your script with unknown users or publish it without a proper security review.

I hope you find this method helpful for Auditing Google Drive Files. It is specifically useful for someone using their Google Personal Account to manage everything.

Tip: If you ever make changes to your script (like updating logic or the linked Sheet), don't forget to re-save and re-authorize the script if needed. Also, it's a good practice to occasionally check the Executions log under Apps Script → Executions to ensure the script is running correctly on schedule and handling all files as expected.