Reviewing content in a Google Drive

Learn how to run a Google Apps Script in a Google Sheet to assess Google Documents in a Google Drive.

Overview

Need to see what duplicate and outdated content is in your Google Drive? Assess your Google Drive content via a Google Apps Script. Google Apps Script is JavaScript that lets you make applications that integrate with Gmail, Calendar, Drive, and other Google Workspace apps via built-in libraries.

For instance, Google Apps Script can run in a Google Sheet to add data about each Google Doc in your Google Drive. Data for each document can include the last modified date, the created date, data from Google Analytics, or even the number of broken links in each doc. This guide provides a sample Google Apps Script you can run to retrieve details about the Google Docs in your Google Drive.

✎ Google Apps Script can't show the number of open comments on a document.

After the script runs, determine which content to archive, consolidate, or review. This guide shows both steps of this process:

Step 1 - Create a Google Apps Script

✎ When you first use a Google Apps Script, a pop-up window asks for access to your Google Drive.

Create and run a Google Apps Script using the following steps:

  1. Open a new Google Sheet.
  2. Click Extensions, and select Apps Script.

  3. In the Apps Script window that appears, remove the default function code.

    Location of the default function code to delete.
    Figure 1 - Default function code.

  4. Copy and paste the following code into the Apps Script:

     function listGoogleDocsInFolder(folder) {
   var files = folder.getFilesByType(MimeType.GOOGLE_DOCS); // Only looking at Documents, not spreadsheets, presentations, etc
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
   while (files.hasNext()) {
     var file = files.next();
     try {
       var doc = DocumentApp.openById(file.getId());
       var creationDate = file.getDateCreated();
       var lastModified = file.getLastUpdated();
       var docUrl = doc.getUrl(); // Get the document URL

       sheet.appendRow([file.getName(), folder.getName(), creationDate, lastModified, docUrl]); // Append URL to the row
     } catch (e) {
       Logger.log("Error processing file: " + file.getName() + ", Error: " + e.toString());
     }
   }

   var subfolders = folder.getFolders();
   while (subfolders.hasNext()) {
     var subfolder = subfolders.next();
     listGoogleDocsInFolder(subfolder); // Recursively call the function for subfolders
   }
 }

 function listGoogleDocs() {
   var folderId = "0AKSQM_w_oOoAUk9PVB"; // Replace with the ID of the root folder
   var folder = DriveApp.getFolderById(folderId);
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
   sheet.clear(); // Clear existing data
   sheet.appendRow(["Document Name", "Folder Name", "Created Date", "Last Modified Date", "Document URL"]); // Add URL column
  
   listGoogleDocsInFolder(folder);
 }

    ✎ This example Google Apps Script provides the document name, created date, last modified date, URL, and folder name only for Google Doc files.

  5. To find the starting folder ID where you want to search for documents in each subfolder, click the Google Drive and note the ID after folders/ in your browser’s URL.

    Location of the folder ID.
    Figure 2 - Folder ID.

  6. Replace the default folder ID 0AKSQM_w_oOoAUk9PVB in the Apps Script with your starting folder ID.

  7. Click the Save project icon.

    Location of the Save project icon.
    Figure 3 - Save project icon.

  8. Ensure listGoogleDocs is selected for Select function to run.

    Location of the Select function to run icon.
    Figure 4 - Select function to run icon.

  9. Click Run.

    Location of the Run icon.
    Figure 5 - Run icon.

  10. The Execution log appears and shows the Google Apps Script is running. Wait until the Execution log shows Execution completed.

    Location of the Execution complete message.
    Figure 6 - Execution complete.

    ✎ It can take several minutes for the Google Apps Script to complete depending on the amount of content in the Google Drive.

  11. Analyze your data.

Step 2 - Analyze Data

When the Google sheet has data, seek answers to these questions:

  • What documents need archival?
  • Which content needs review and update?
  • Which documents look duplicated?
  • What content is in the wrong folder?
  • What is the average time these documents were last modified?

Content to archive

Sort the Google Sheet by the oldest created date to identify outdated content. Also, review documents with Copy of at the beginning of the name.

For archival permission, identify the content owners by looking at the document details or version history. When you contact someone responsible for the content, ensure you are clear on whether you plan to delete the content or move it to an archive location. If you can’t find an owner or the owner no longer works for your company, you can look at the document analytics to see when it was last viewed.

Outdated content

Create a formula in the cells of a new column of the Google Sheet that shows the number of days since a document was last modified. For instance, if the date you want to analyze is in cell D2 of the Google Sheet and the dates are in a format like 4/12/2024 16:11:38, use this formula to find the number of days old the content is from the current date:

=DATEDIF(DATEVALUE(MID(D2, 1, FIND(" ", D2) - 1)), TODAY(), "D")

Drag the formula down to calculate the number of days old for the rest of your last modified dates. Then, sort by this new “days since last modified” data to find content over a year old(or whatever date you prefer) and make a plan to review the content further. Share your data with stakeholders to find the priority content to update.

Another useful way to use the “days since last modified” data is to average the days. The “average days since last modified” is a metric to measure and report on the health of the content in your Google Drive.

Duplicate content

Check document names to ensure any naming standards are followed and to identify any duplicate content. Review any documents that cover similar topics or have similar names to see if content can be combined or archived.

Other uses

Use the folder name column to check if content is in the correct folders or to find duplicate folder structures.

Troubleshooting the Google Apps Script

The example Google Apps Script in this guide has error detection for common errors such as Unexpected error while getting the method or property openById on object DocumentApp. This means script execution continues if certain common errors occur. However, you may encounter errors in other areas of code that need error handling. All errors appear in the Execution log.

When running the Google Apps Script, a common error is TypeError: Cannot read properties of undefined. This error means the wrong function to run is selected. Use the Select function to run dropdown to select listGoogleDocs.

If Google Apps script doesn’t appear as an option in the Extensions of your Google Sheet, or you see errors not mentioned in this guide, consult Google’s Apps Script troubleshooting docs.