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
Three steps to get a list of your files in Google Drive using Apps ScriptStep 1 — Create a Google Sheets spreadsheet to save the list
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);
}How does the getMyFilesFromDrive() function work?
First, we use the
searchFiles()method of theDriveAppclass to get a list of files to export. The search query'"me" in owners'restricts the search results to the files owned by you. Note: Please note the single quotes enclosing the query and the double quotes around "me".
var myFiles = DriveApp.searchFiles('"me" in owners');Next, we access the sheet called "Files" where we will save the list. We clear the sheet to ensure there is no other data in it. If you want the list written to a sheet with a different name, please replace "Files" in the code below with the name of your sheet (ensure that the name is surrounded by quotes).
var sheet = SpreadsheetApp.getActive().getSheetByName("Files");
sheet.clear();Then we create a two-dimensional array called
rowsto save data to be written to the spreadsheet. We write the header row to this array. The header row has 3 columns since we want to save each file's ID, Name and Url.
var rows = [];
rows.push(["ID", "Name", "Url"]);Next, we loop through the search results and add a row for each file to the array
rows. The code below uses what is called an iterator. An iterator lets you iterate (i.e., step through) through a set of values one at a time.
// 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()]);
}
}Finally, we write the values to the sheet.
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!