Search Google Drive for owned files

The following Google Apps Script is designed to search Google Drive for all of the files owned by an individual (as defined by their email address). It will collate this information onto a Google Sheet, including:

• The filename,
• The file ID,
• A direct/clickable link to the file,
• The type of file eg PDF, Google Sheet/Doc, etc,
• The file creation date, 
• The file last updated date.
  

Provide the owner and maximum script runtime for search Drive for files.

This is the first time I have explored 'DriveApp.searchFiles()' so I was quite excited to see its potential and what I could put together. Usually all of the files/folders have to be together in a logical structure in order to points Apps Script at them, but here they can be spread around the whole of Google Drive and we can seemlessly pull them altogether into a Google Sheet.

 

Performance

During the time I was writing this post I shared the tool with another colleague who promptly gave me some great feedback that led to it becoming 23x faster! It went from collating 3,000 files in 16 minutes to doing so in 41 seconds! When they ran the new version it collated 73,000 files in 20 minutes.

So how was this achieved? Basically by not appending each row of data to the sheet after every file iteration. Instead the file information was pushed into an empty array which at the end was then collectively pasted into the Google Sheet. The process of going back and forth to the sheet to append each file was having a significant negative performance impact.

The only drawback for the end user is that it is less clear of the progress of the tool as they no longer see each file being appended to the Sheet. Using a Toast notification is somewhat ineffective too as the files are processed so quickly. They do however have the final popup message that informs them of completion.

 

Instructions

Please follow the guidance on the 'Welcome' sheet to provide the 2 pieces of required information. Use the 'Start' button or the 'Admin' menu to run the tool. 'Sheet1' is where the file information will be collated by the tool. The 'Log' sheet is to help debug any problems and you will receive a popup message to also inform you of any problems the tool encounters.

The Code

There are a couple of elements of code I wanted to highlight that are very new and have been part of the learning with this project. As usual I include comments as much as possible to help you understand/manipulate the code.

As mentioned above the 'DriveApp.searchFiles()' method is the way we are going to go out and search Google Drive for what we want. There are a number of file-specific query terms that we can use depending on what we require, in this instance we want owners:

var files = DriveApp.searchFiles(
    "'" + ownerEmailAddress + "'" + ' in owners'
);

Next we need to navigate through the 'fileIterator' and collate the relevant information we want such as File name, ID, etc. What happens if we need to pause the tool during this however, or more likely it reaches the quota limit and needs manually starting again. We certainly do not want to start from the beginning as we will likely just reach the same point again. So instead we make use of a 'Continuation token' which allows us to resume the file iteration at a later time:

var continuationToken = files.getContinuationToken();

The script stores this in the Script Properties so that is can be accessed next time around when the tool is re-run:

// set 'Continuation Token'  Script Property
var continuationToken = scriptProperties.setProperty('CONTINUATION_TOKEN', continuationToken);

// continue iterating through Drive files with Token
var files = DriveApp.continueFileIterator(continuationToken);

Download

Search Google Drive for owned files download here (please use 'File' > 'Make a copy' for your own version).

As of November 2021 this is now available as a Google Workspace Marketplace Add-on.