Case Indexing Drive

// === CONFIGURATION ===
const ROOT_FOLDER_ID = 'insert';
const MASTER_INDEX_DOC_ID = 'insert';
const TARGET_SPREADSHEET_FOLDER_ID = 'insert';
const EMAIL = 'insert';
const FILE_BATCH_SIZE = 750;
const FOLDER_BATCH_SIZE = 200;

// === ENTRY POINT ===
function startCaseIndexing() {
  const folder = DriveApp.getFolderById(ROOT_FOLDER_ID);
  const folderName = folder.getName();
  const createdDate = folder.getDateCreated();

  const ss = SpreadsheetApp.create(folderName + ' - Case Index');
  const ssId = ss.getId();
  DriveApp.getFolderById(TARGET_SPREADSHEET_FOLDER_ID).addFile(DriveApp.getFileById(ssId));
  DriveApp.getRootFolder().removeFile(DriveApp.getFileById(ssId));

  const sheet = ss.getSheets()[0];
  sheet.setName('File Metadata');
  sheet.clear();
  sheet.appendRow(['Subfolder', 'File Name', 'Created Date', 'Modified Date', 'File Link', '', 'File ID']);

  const stateSheet = ss.insertSheet('__BatchState');
  stateSheet.hideSheet();
  stateSheet.getRange('A1').setValue('folderQueue');
  stateSheet.getRange('B1').setValue('colorMap');
  stateSheet.getRange('C1').setValue('meta');

  const folderQueue = [[folder.getId(), '']];
  stateSheet.getRange(2, 1, folderQueue.length, 2).setValues(folderQueue);
  stateSheet.getRange('B2').setValue(JSON.stringify({}));
  stateSheet.getRange('C2').setValue(JSON.stringify({
    fileCount: 0,
    folderCount: 0,
    colorIndex: 0,
    rootId: folder.getId()
  }));

  PropertiesService.getScriptProperties().setProperty('activeSheetId', ssId);
  continueIndexingBatch_();

  addToMasterIndexDoc_(folder.getId(), folderName, createdDate, folder.getUrl(), ss.getUrl());
  MailApp.sendEmail(EMAIL, 'Case Indexed (Batch Started)', `Initial batch started for "${folderName}". Spreadsheet: ${ss.getUrl()}`);
}

function continueIndexingBatch_() {
  const ssId = PropertiesService.getScriptProperties().getProperty('activeSheetId');
  if (!ssId) return;

  const ss = SpreadsheetApp.openById(ssId);
  const sheet = ss.getSheetByName('File Metadata');
  const stateSheet = ss.getSheetByName('__BatchState');
  const colorPalette = [
    '#f4cccc','#fce5cd','#fff2cc','#d9ead3','#d0e0e3','#cfe2f3','#d9d2e9','#ead1dc',
    '#ea9999','#f9cb9c','#ffe599','#b6d7a8','#a2c4c9','#9fc5e8','#b4a7d6','#d5a6bd'
  ];

  const folderQueue = stateSheet.getRange(2, 1, stateSheet.getLastRow() - 1, 2).getValues();
  const colorMap = JSON.parse(stateSheet.getRange('B2').getValue());
  const meta = JSON.parse(stateSheet.getRange('C2').getValue());

  let fileCount = 0;
  let folderCount = 0;
  const drive = Drive;
  const updatedQueue = [];

  for (const [folderId, pageToken] of folderQueue) {
    if (folderCount >= FOLDER_BATCH_SIZE || fileCount >= FILE_BATCH_SIZE) {
      updatedQueue.push([folderId, pageToken]);
      continue;
    }

    const folder = DriveApp.getFolderById(folderId);
    const parentFolderName = folder.getParents().hasNext() ? folder.getParents().next().getName() : 'Root';

    if (!colorMap[parentFolderName]) {
      colorMap[parentFolderName] = colorPalette[meta.colorIndex % colorPalette.length];
      meta.colorIndex++;
    }

    const query = `'${folderId}' in parents and trashed = false`;
    const filesResp = drive.Files.list({
      q: query,
      maxResults: 100,
      pageToken: pageToken || undefined,
      fields: 'files(id,name,createdTime,modifiedTime),nextPageToken'
    });

    const files = filesResp.files || [];
    for (const file of files) {
      if (fileCount >= FILE_BATCH_SIZE) {
        updatedQueue.unshift([folderId, filesResp.nextPageToken || '']);
        stateSheet.getRange(2, 1, updatedQueue.length, 2).setValues(updatedQueue);
        stateSheet.getRange('B2').setValue(JSON.stringify(colorMap));
        stateSheet.getRange('C2').setValue(JSON.stringify(meta));
        ScriptApp.newTrigger('continueIndexingBatch_').timeBased().after(2 * 60 * 1000).create();
        return;
      }

      const row = [
        parentFolderName,
        (file.name || 'Untitled').replace(/\.[^\.]+$/, ''),
        new Date(file.createdTime),
        new Date(file.modifiedTime),
        `=HYPERLINK("https://drive.google.com/file/d/${file.id}/view", "Link")`,
        '',
        file.id
      ];
      sheet.appendRow(row);
      const range = sheet.getRange(sheet.getLastRow(), 1, 1, 5);
      range.setBackground(colorMap[parentFolderName]);
      range.getCell(1, 2).setFontWeight('bold');

      fileCount++;
    }

    if (filesResp.nextPageToken) {
      updatedQueue.push([folderId, filesResp.nextPageToken]);
    } else {
      const subfolders = folder.getFolders();
      while (subfolders.hasNext()) {
        const sub = subfolders.next();
        updatedQueue.push([sub.getId(), '']);
      }
      folderCount++;
    }
  }

  if (updatedQueue.length > 0) {
    stateSheet.getRange(2, 1, updatedQueue.length, 2).clearContent();
    stateSheet.getRange(2, 1, updatedQueue.length, 2).setValues(updatedQueue);
    meta.fileCount = 0;
    meta.folderCount = 0;
    stateSheet.getRange('B2').setValue(JSON.stringify(colorMap));
    stateSheet.getRange('C2').setValue(JSON.stringify(meta));
    ScriptApp.newTrigger('continueIndexingBatch_').timeBased().after(2 * 60 * 1000).create();
  } else {
    PropertiesService.getScriptProperties().deleteProperty('activeSheetId');
  }
}

function addToMasterIndexDoc_(folderId, name, created, folderUrl, sheetUrl) {
  const doc = DocumentApp.openById(MASTER_INDEX_DOC_ID);
  const body = doc.getBody();
  const tables = body.getTables();
  let table;
  if (tables.length === 0) {
    table = body.appendTable([
      ['Folder ID', 'Case Name', 'Date Created', 'Folder Link', 'Spreadsheet Link']
    ]);
  } else {
    table = tables[0];
  }
  const row = table.appendTableRow();
  row.appendTableCell(folderId);
  row.appendTableCell(name);
  row.appendTableCell(created.toDateString());
  row.appendTableCell('Link').setLinkUrl(folderUrl);
  row.appendTableCell('Spreadsheet').setLinkUrl(sheetUrl);
  doc.saveAndClose();
}