Google Apps Script untuk list folder, tampilkan pada sheet awal (hapus yang lama) pada workbook (Google SpreadsheetApp)

listFilesInFolder.appscripts.js

//const folderId = 'YOUR_FOLDER_ID_HERE'; // <-- IMPORTANT: Replace with your actual Folder ID
// 1. Set the ID of the Google Drive folder you want to scan.
const folderId = '1FjkrvL1lPz0fYPOYNB9';

function myFunction() {
  listFilesInFolder(folderId);
}

/**
 * Lists files from a Google Drive folder with their ID, download URL, and size.
 * It also calculates the total size and writes everything to the active sheet.
 *
 * @OnlyCurrentDoc
 */
function listFilesInFolder(folderId) {

  // 2. Get the active spreadsheet and the first sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

  // 3. Clear the sheet and add the new headers.
  sheet.clear();
  const headers = ['File Name', 'File ID', 'Download URL', 'Size (MB)'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');

  try {
    // 4. Access the folder and prepare to collect data.
    const folder = DriveApp.getFolderById(folderId);
    const files = folder.getFiles();
    const fileData = [];
    let totalSizeInBytes = 0; // Initialize total size counter

    // 5. Loop through each file in the folder.
    while (files.hasNext()) {
      const file = files.next();
      
      const fileId = file.getId();
      const fileName = file.getName();
      const fileSizeInBytes = file.getSize();
      
      // Add current file's size to the total
      totalSizeInBytes += fileSizeInBytes;

      // Create a direct download link
      const downloadUrl = `https://drive.google.com/uc?export=download&id=${fileId}`;
      
      // Convert size from bytes to megabytes
      const fileSizeInMB = (fileSizeInBytes / (1024 * 1024)).toFixed(2);
      
      fileData.push([fileName, fileId, downloadUrl, fileSizeInMB]);
    }

    // 6. Write all collected file data to the spreadsheet.
    if (fileData.length > 0) {
      sheet.getRange(2, 1, fileData.length, fileData[0].length).setValues(fileData);
    }
    
    // 7. Calculate and write the total size at the bottom of the list.
    if (totalSizeInBytes > 0) {
      const totalSizeInMB = (totalSizeInBytes / (1024 * 1024)).toFixed(2);
      const lastRow = sheet.getLastRow();
      
      // Add the total size two rows below the last entry for spacing
      sheet.getRange(lastRow + 2, headers.length - 1).setValue('Total Size:').setFontWeight('bold');
      sheet.getRange(lastRow + 2, headers.length).setValue(`${totalSizeInMB} MB`);
    }

    // 8. Auto-resize columns and show a success message.
    sheet.autoResizeColumns(1, headers.length);
    //SpreadsheetApp.getUi().alert('Success!', `Found and listed ${fileData.length} files.`, SpreadsheetApp.getUi().ButtonSet.OK);

  } catch (e) {
    SpreadsheetApp.getUi().alert('Error', 'Could not process the folder. Please check if the Folder ID is correct in the script.', SpreadsheetApp.getUi().ButtonSet.OK);
    Logger.log(e); // Logs the error for debugging
  }
}

https://kampar.wordpress.com/2025/07/20/43-millions-ebooks-for-only-idr-35-245/