Apps Script to Fix Google Drive Storage Issues

function driveCleaner() {
    const SIZE_LIMIT_MB = 50;
    const SIZE_LIMIT_BYTES = SIZE_LIMIT_MB * 1024 * 1024;
  
    const files = [];
    const duplicatesMap = {};
    let largeCount = 0;
    let duplicateCount = 0;
    let totalCount = 0;
  
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
    const sheet = spreadsheet.getActiveSheet();
  
    setupReportSheet(sheet); 
  
    const driveFiles = DriveApp.getFiles();
  
   
    while (driveFiles.hasNext()) {
      const file = driveFiles.next();
      totalCount++;
  
      const name = file.getName();
      const size = file.getSize();
      const type = file.getMimeType();
      const created = file.getDateCreated();
      const url = file.getUrl();
      const sizeMB = (size / (1024 * 1024)).toFixed(2);
      let flag = "Normal";
  
      const key = `${name}_${size}`;
      const isDuplicate = duplicatesMap[key] === true;
      const isLarge = size > SIZE_LIMIT_BYTES;
  
      if (isDuplicate && isLarge) {
        flag = "Large, Duplicate";
        duplicateCount++;
        largeCount++;
      } else if (isDuplicate) {
        flag = "Duplicate";
        duplicateCount++;
      } else if (isLarge) {
        flag = "Large";
        largeCount++;
      }
  
      files.push([name, sizeMB, type, created, url, flag]);
      duplicatesMap[key] = true;
    }
  
    
    if (files.length > 0) {
      sheet.getRange(2, 1, files.length, 6).setValues(files);
      applyConditionalFormatting(sheet);
      sheet.getRange(1, 1, sheet.getLastRow(), 6)
        .setBorder(true, true, true, true, true, true);
      sheet.getRange(1, 1, sheet.getLastRow(), 6)
        .applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY);
    }
  
    SpreadsheetApp.flush();
  
    const reportUrl = spreadsheet.getUrl();
    Logger.log("✅ Report Updated: " + reportUrl);
  
    
    showSummaryAlert(duplicateCount, largeCount);
    showSidebar();
  
    
    sendSummaryEmail(totalCount, largeCount, duplicateCount, reportUrl);
  }
  
  function setupReportSheet(sheet) {
    const headers = ["📄 File Name", "📦 Size (MB)", "📁 Type", "📅 Created Date", "🔗 URL", "🚩 Flag"];
    
    sheet.clear();
    sheet.setName("Drive Cleanup Report"); 
    sheet.appendRow(headers);    
    const headerRange = sheet.getRange(1, 1, 1, headers.length);
    headerRange.setFontWeight("bold").setBackground("#f4f4f4").setFontSize(12);
  
    sheet.setFrozenRows(1);  
    sheet.autoResizeColumns(1, headers.length);  
  }
  
  function applyConditionalFormatting(sheet) {
    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return; // No data const flagRange = sheet.getRange(2, 6, lastRow - 1); const rules = [ SpreadsheetApp.newConditionalFormatRule() .whenTextEqualTo("Large") .setBackground("#ffdddd") .setRanges([flagRange]) .build(), SpreadsheetApp.newConditionalFormatRule() .whenTextEqualTo("Duplicate") .setBackground("#fff5cc") .setRanges([flagRange]) .build(), SpreadsheetApp.newConditionalFormatRule() .whenTextEqualTo("Large, Duplicate") .setBackground("#ffe0b2") .setRanges([flagRange]) .build() ]; const existingRules = sheet.getConditionalFormatRules(); sheet.setConditionalFormatRules([...existingRules, ...rules]); } function showSummaryAlert(duplicateCount, largeCount) { const ui = SpreadsheetApp.getUi(); ui.alert(`📊 Drive Cleanup Summary:\n\n📎 Duplicate Files: ${duplicateCount}\n📦 Large Files: ${largeCount}`); } function showSidebar() { const html = HtmlService.createHtmlOutputFromFile("Sidebar") .setTitle("🧹 Drive Cleaner"); SpreadsheetApp.getUi().showSidebar(html); } function sendSummaryEmail(total, largeCount, dupCount, reportLink) { const email = Session.getActiveUser().getEmail(); const subject = "🧹 Google Drive Cleanup Summary"; const body = ` Hey ${email}, Here’s your Drive Cleanup Summary: 📁 Total files scanned: ${total} 📦 Large files (>50MB): ${largeCount}
  📎 Duplicate files: ${dupCount}
  
  📊 Report: ${reportLink}
  
  Open the link above to view flagged files.
  
  – Your Cleaner Bot 🤖`;
  
    GmailApp.sendEmail(email, subject, body);  
  }
  
  function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const flags = sheet.getRange(2, 6, sheet.getLastRow() - 1).getValues();
  
    let duplicateCount = 0;
    let largeCount = 0;
  
    flags.forEach(row => {
      const flag = row[0];
      if (flag.includes("Duplicate")) duplicateCount++;
      if (flag.includes("Large")) largeCount++;
    });
  
    ui.alert(`📊 Drive Cleanup Summary:\n\n📎 Duplicate Files: ${duplicateCount}\n📦 Large Files: ${largeCount}`);
    showSidebar();
  }
  
  function getDuplicateFiles() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const data = sheet.getDataRange().getValues();
    const duplicates = [];
  
    for (let i = 1; i < data.length; i++) { const flag = data[i][5]; if (flag.includes("Duplicate")) { duplicates.push({ name: data[i][0], url: data[i][4], rowIndex: i + 1, id: extractFileId(data[i][4]) }); } } return duplicates; } function extractFileId(url) { const match = url.match(/[-\w]{25,}/); return match ? match[0] : null; } function deleteSelectedFiles(selectedRows) { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); let deletedCount = 0; selectedRows.sort((a, b) => b - a);  
  
    for (let i = 0; i < selectedRows.length; i++) {
      const row = selectedRows[i];
      const fileUrl = sheet.getRange(row, 5).getValue();
      const fileId = extractFileId(fileUrl);
  
      try {
        if (fileId) {
          DriveApp.getFileById(fileId).setTrashed(true);
          sheet.deleteRow(row);  
          deletedCount++;
        }
      } catch (e) {
        Logger.log(`❌ Could not delete file at row ${row}: ${e}`);
      }
    }
  
    return deletedCount;
  }