Export a list of your files in Google Drive to Google Sheets using Apps Script

I recently had to export a list of my files in Google Drive but I couldn't figure out how to do that from Drive's UI. Therefore, I wrote a script using Apps Script to achieve this. In this tutorial, I'll show you how to export a list of your Drive files to a Google Sheets spreadsheet.

This illustrates the power of Apps Script. Even if an app like Google Drive does not have a feature, you often can build that feature yourself with just a few lines of code!

Prerequisites

• Basics of coding using Apps Script.

• The while loop.

• The array method push()

• Writing values to a range in a Google Sheets spreadsheet.

Three steps to get a list of your files in Google Drive using Apps Script

• Step 1 — Create a Google Sheets spreadsheet to save the list

• Step 2 — Write an Apps Script to search Drive for files you own and export this list to the spreadsheet

• Step 3 — Run the script

Step 1 — Create a Google Sheets spreadsheet to save the list

Create a new Google Sheets spreadsheet or open an existing spreadsheet where you want the list saved. Here's a tip: You can quickly create a new Google Sheets spreadsheet using https://spreadsheet.new.

Create a sheet in the spreadsheet called "Files". The list of files will be written to the sheet.

Step 2 — Write an Apps Script to search Drive for files you own and export this list to the spreadsheet

The next step is to write an Apps Script to search Drive for the files you own and then export this list to your spreadsheet. First open the Apps Script editor by selecting Extensions —> Apps Script.

Then replace the default code in the editor with the code below. Don't worry, the next section explains how this code works.

function getMyFilesFromDrive() {
 var myFiles = DriveApp.searchFiles('"me" in owners');
 var sheet = SpreadsheetApp.getActive().getSheetByName("Files");
 sheet.clear();
 var rows = [];
 rows.push(["ID", "Name", "Url"]);
 while(myFiles.hasNext()) {
   var file = myFiles.next();
   if(file != null) {
     rows.push([file.getId(), file.getName(), file.getUrl()]);
   }
 }
 sheet.getRange(1,1,rows.length,3).setValues(rows);
}

var myFiles = DriveApp.searchFiles('"me" in owners');

var sheet = SpreadsheetApp.getActive().getSheetByName("Files");
sheet.clear();

var rows = [];
rows.push(["ID", "Name", "Url"]);

// While there is another file to process
while(myFiles.hasNext()) {
  // Assign the next file to the variable file
  var file = myFiles.next();
  // Check that the file is not null just to be sure
  if(file != null) {
    // Add the file's Id, name and url to an array and add this array to the array rows. 
    rows.push([file.getId(), file.getName(), file.getUrl()]);
  }
}

sheet.getRange(1,1,rows.length,3).setValues(rows);

Step 3 — Run the script

The final step is to run the script. When you run the script, a list of your files in Drive will be written to the spreadsheet.

Conclusion

In this tutorial I showed you how to export a list of your files in Google Drive to a Google Sheets spreadsheet using Apps Script. Hope this post was helpful. Thanks for reading!