Bulk Delete Files in Google Drive with Google Apps Script: Organize with Spreadsheets

listFilesForDeletion Function

This function prompts the user to specify a folder name, folder ID, or select “All Files in Drive”. It then lists all files within the specified folder or across the entire Drive.

function listFilesForDeletion() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Specify a Folder',
    'Enter "All Files in Drive" or provide a folder name or folder ID:',
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() === ui.Button.CANCEL) {
    ui.alert('Operation cancelled.');
    return;
  }

  const input = response.getResponseText().trim();

  // If "All Files in Drive" is entered, list all files; otherwise, list files in the specified folder
  if (input === 'All Files in Drive') {
    listAllFilesForDeletion();
  } else {
    listFilesInFolderForDeletion(input);
  }
}

• The function uses ui.prompt() to request user input, allowing the user to specify a folder name or folder ID to determine the scope of the file listing.
• If the input is “All Files in Drive”, the listAllFilesForDeletion() function is called to list all files in the Drive.
• For any other input, the listFilesInFolderForDeletion() function is called to list files within the specified folder.

listAllFilesForDeletion Function

This function lists all files in Google Drive.

function listAllFilesForDeletion() {
  const sheet = setupDeletionSheet();
  const files = DriveApp.getFiles();
  let row = 2;

  while (files.hasNext()) {
    const file = files.next();
    appendFileDataForDeletionToSheet(sheet, file, row);
    row++;
  }
}

• It uses DriveApp.getFiles() to retrieve all files in Google Drive and adds them to the spreadsheet.

listFilesInFolderForDeletion Function

This function lists the files within the specified folder.

function listFilesInFolderForDeletion(folderNameOrId) {
  const sheet = setupDeletionSheet();
  let folder;

  try {
    folder = DriveApp.getFolderById(folderNameOrId);
  } catch (e) {
    const folders = DriveApp.getFoldersByName(folderNameOrId);
    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      SpreadsheetApp.getUi().alert('Folder not found.');
      return;
    }
  }

• It retrieves the target folder based on the folder ID or folder name provided by the user.
• The files within the specified folder are then fetched using the getFiles() method and listed in the spreadsheet.

setupDeletionSheet Function

This function sets up the spreadsheet to be used for listing files.

function setupDeletionSheet() {
  const sheetName = 'Bulk File Deletion';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);

  // Clear and reset the sheet
  sheet.clear();

  // Set headers
  const headers = [
    'Folder Name', 
    'File Name', 
    'Owner', 
    'Last Updated', 
    'Delete Target'
  ];

  sheet.appendRow(headers);
  const headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setHorizontalAlignment('center');
  headerRange.setVerticalAlignment('middle');

  // Set background color for headers (light green)
  headerRange.setBackground('#B7E1CD');

  return sheet;
}

• It retrieves or creates a sheet named “Bulk File Deletion” and initializes it.
• The header row (Folder Name, File Name, Owner, Last Updated, Delete Target) is set up, and a background color is applied to enhance visual clarity.

appendFileDataForDeletionToSheet Function

This function adds information for each file to the spreadsheet.

function appendFileDataForDeletionToSheet(sheet, file, row) {
  const folderName = getFolderName(file);
  const fileName = file.getName();
  const fileLink = file.getUrl();
  const owner = file.getOwner() ? file.getOwner().getEmail() : 'No Owner';
  const lastUpdated = file.getLastUpdated();

  // Add data to the sheet (file name as a hyperlink)
  sheet.getRange(row, 1).setValue(folderName);
  sheet.getRange(row, 2).setFormula(`=HYPERLINK("${fileLink}", "${fileName}")`);
  sheet.getRange(row, 3).setValue(owner);
  sheet.getRange(row, 4).setValue(lastUpdated);
  sheet.getRange(row, 5).insertCheckboxes(); // Add checkboxes for delete targets
}

• It records the file’s folder name, file name (with a hyperlink), owner, and last updated date in the spreadsheet, and adds a checkbox to mark the file as a delete target.

getFolderName Function

This function retrieves the name of the folder the file belongs to.

function getFolderName(file) {
  const parents = file.getParents();
  return parents.hasNext() ? parents.next().getName() : 'Root';
}

• If the file has a parent folder, it returns the folder’s name; otherwise, it displays “Root”.

Open a Google Spreadsheet and click on “Extensions” in the menu, then “Apps Script” to open the Apps Script editor.

Since the default function myFunction() {} is present, delete it and replace it with the script below.

deleteSelectedFiles Function

This function deletes the files marked with checkboxes in the spreadsheet.

function deleteSelectedFiles() {
  const sheetName = 'Bulk File Deletion';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    SpreadsheetApp.getUi().alert('The sheet "Bulk File Deletion" was not found.');
    return;
  }
  
  // Set up headers
  const headers = [
    'Folder Name', 
    'File Name', 
    'Owner', 
    'Last Updated', 
    'Delete Target', 
    'Deleted At'
  ];
  
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

• It retrieves the “Bulk File Deletion” sheet and reviews the listed file information.
• Files marked for deletion are identified using the getRange() method, and the process continues only if the checkbox is selected.

Deletion Process

Files with selected checkboxes are deleted.

  const lastRow = sheet.getLastRow();
  for (let i = 2; i <= lastRow; i++) {
    const checkbox = sheet.getRange(i, 5).getValue();
    
    if (checkbox) { // If checkbox in column E is checked
      const fileLink = sheet.getRange(i, 2).getFormula();
      const fileIdMatch = fileLink.match(/\/d\/([a-zA-Z0-9-_]+)/);
      
      if (fileIdMatch) {
        try {
          const fileId = fileIdMatch[1];
          const file = DriveApp.getFileById(fileId);
          
          // Move the file to Trash
          file.setTrashed(true);

• The file ID is extracted from each file’s link, and the corresponding file is retrieved using DriveApp.getFileById().
• The setTrashed(true) method is then used to move the file to the Trash.

Record of Deleted Files

A timestamp is added to indicate when the file was successfully deleted.

          // Add timestamp in column F
          sheet.getRange(i, 6).setValue(new Date());
          
          // Update folder name to "Trash"
          sheet.getRange(i, 1).setValue('Trash');
          
          // Reset the checkbox in column E
          sheet.getRange(i, 5).setValue(false);
        } catch (e) {
          console.error('Error occurred while deleting the file:', e.message);
        }

• The deletion timestamp is recorded in the “Deleted At” column of the spreadsheet, serving as a deletion history.
• The delete target checkbox is reset, and the folder name is updated to “Trash”.

After writing the script, save it with a name.

(Example: “Bulk File Deletion”)

Run the script.

If you’re running the script for the first time, you need to authorize it.

Therefore, press “Review Permissions.“

Detailed Authorization Steps

Press “Advanced.”

Press “Go to Untitled project (Unsafe).”

After that, press “Allow.”