Parse links to files from Google Drive folder to Google Sheet (Apps Script)

gistfile1.txt

/* modified by @alexkolodko
from @hubgit and http://stackoverflow.com/questions/30328636/google-apps-script-count-files-in-folder
for this stackexchange question http://webapps.stackexchange.com/questions/86081/insert-image-from-google-drive-into-google-sheets by @twoodwar
*/

function onOpen() {
   var ui = SpreadsheetApp.getUi();
   // Or DocumentApp or FormApp.
   ui.createMenu('Links to files')
      // .addItem('Show sidebar', 'showSidebar')
      .addItem('All Files in Folder', 'listFilesInFolder')
      .addSeparator()  
      .addItem("Adobe Illustrator", "listFilesInFoldesAdobeIllustrator")
      .addItem("Audio", "listFilesInFoldesAudio")
      .addItem("CSS", "listFilesInFoldesCSS")
      .addItem("CSV", "listFilesInFoldesCSV")
      .addItem("Google Docs", "listFilesInFoldesGoogleDocs")
      .addItem("Google Forms", "listFilesInFoldesGoogleForms")
      .addItem("Google Sheets", "listFilesInFoldesGoogleSheets")
      .addItem("Google Slides", "listFilesInFoldesGoogleSlides")
      .addItem("HTML", "listFilesInFoldesHTML")
      .addItem("Images", "listFilesInFoldesImages")
      .addItem("JSON", "listFilesInFoldesJSON")
      .addItem("JavaScript", "listFilesInFoldesJavaScript")
      .addItem("Microsoft Excel", "listFilesInFoldesMicrosoftExcel")
      .addItem("Microsoft PowerPoint", "listFilesInFoldesMicrosoftPowerPoint")
      .addItem("Microsoft Word", "listFilesInFoldesMicrosoftWord")
      .addItem("Open Document Spreadsheet", "listFilesInFoldesOpenDocumentSpreadsheet")
      .addItem("Open Document Text", "listFilesInFoldesOpenDocumentText")
      .addItem("PDF", "listFilesInFoldesPDF")
      .addItem("RTF", "listFilesInFoldesRTF")
      .addItem("Text", "listFilesInFoldesText")
      .addItem("TSV", "listFilesInFoldesTSV")
      .addItem("Video", "listFilesInFoldesVideo")
      .addItem("XML", "listFilesInFoldesXML")
      .addItem("ZIP", "listFilesInFoldesZIP")
      .addSeparator()
      .addItem('All Files in Folder and Subfolders', 'listFilesInFolderAndSubfolders')
      .addToUi();
}

// function showSidebar() {
//   var html = HtmlService.createHtmlOutputFromFile('Page')
//       .setTitle('My custom sidebar');
//   SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
//       .showSidebar(html);
// }

function listFilesInFoldesAdobeIllustrator(){listFilesInFolder("application/illustrator")}
function listFilesInFoldesAudio(){listFilesInFolder("audio/")}
function listFilesInFoldesCSS(){listFilesInFolder("text/css")}
function listFilesInFoldesCSV(){listFilesInFolder("text/csv")}
function listFilesInFoldesGoogleDocs(){listFilesInFolder("application/vnd.google-apps.document")}
function listFilesInFoldesGoogleForms(){listFilesInFolder("application/vnd.google-apps.form")}
function listFilesInFoldesGoogleSheets(){listFilesInFolder("application/vnd.google-apps.spreadsheet")}
function listFilesInFoldesGoogleSlides(){listFilesInFolder("application/vnd.google-apps.presentation")}
function listFilesInFoldesHTML(){listFilesInFolder("text/html")}
function listFilesInFoldesImages(){listFilesInFolder("image/")}
function listFilesInFoldesJSON(){listFilesInFolder("application/json")}
function listFilesInFoldesJavaScript(){listFilesInFolder("application/javascript")}
function listFilesInFoldesMicrosoftExcel(){listFilesInFolder("application/vnd.ms-excel")}
function listFilesInFoldesMicrosoftPowerPoint(){listFilesInFolder("application/vnd.ms-powerpoint")}
function listFilesInFoldesMicrosoftWord(){listFilesInFolder("application/msword")}
function listFilesInFoldesOpenDocumentSpreadsheet(){listFilesInFolder("application/vnd.oasis.opendocument.spreadsheet")}
function listFilesInFoldesOpenDocumentText(){listFilesInFolder("application/vnd.oasis.opendocument.text")}
function listFilesInFoldesPDF(){listFilesInFolder("application/pdf")}
function listFilesInFoldesRTF(){listFilesInFolder("application/rtf")}
function listFilesInFoldesText(){listFilesInFolder("text/plain")}
function listFilesInFoldesTSV(){listFilesInFolder("text/tab-separated-values")}
function listFilesInFoldesVideo(){listFilesInFolder("video/")}
function listFilesInFoldesXML(){listFilesInFolder("application/xml")}
function listFilesInFoldesZIP(){listFilesInFolder("application/zip")}



function listFilesInFolder(fileType) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var folderLink = sheet.getRange("A1").getValue(); // Read folder link from cell A1
    sheet.appendRow(["Name", "Type", "Date", "Size, KB", "URL", "URL Download"]);

    // Validate if the folder link is provided
    if (!folderLink) {
        Logger.log("Folder link not provided in cell A1.");
        return;
    }
    
    // Extract folder ID from the folder link
    var folderId = getFolderIdFromLink(folderLink);

    // Change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
    var folder = DriveApp.getFolderById(folderId);
    var contents = folder.getFiles();
    var cnt = 0;
    var file;

    while (contents.hasNext()) {
        var file = contents.next();

        // Check if the file type matches the specified fileType
        if (fileType && file.getMimeType().indexOf(fileType) === -1) {
            continue; // Skip files that do not match the specified type
        }

        cnt++;
        var data = [
            file.getName(),
            getFileExtension(file.getName()),
            file.getDateCreated(),
            Utilities.formatString('%.02f', file.getSize()/(1024)), //Size in MB
            file.getUrl(),
            "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
        ];
        sheet.appendRow(data);
    }
}


// Recursively with subfolders

function listFilesInFolderAndSubfolders(folderName, fileType) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var folderLink = sheet.getRange("A1").getValue(); // Read folder link from cell A1
    sheet.appendRow(["Name", "Type", "Folder", "Date", "Size", "URL", "URL Download"]);

    // Validate if the folder link is provided
    if (!folderLink) {
        Logger.log("Folder link not provided in cell A1.");
        return;
    }

    // Extract folder ID from the folder link
    var folderId = getFolderIdFromLink(folderLink);

    // Change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
    var rootFolder = DriveApp.getFolderById(folderId);

    // Call the recursive function to process all subfolders
    processFolder(rootFolder, fileType, sheet);
}

function processFolder(folder, fileType, sheet) {
    var contents = folder.getFiles();
    while (contents.hasNext()) {
        var file = contents.next();

        // Check if the file type matches the specified fileType
        if (fileType && file.getMimeType().indexOf(fileType) === -1) {
            continue; // Skip files that do not match the specified type
        }

        var data = [
            file.getName(),
            getFileExtension(file.getName()),
            folder,
            file.getDateCreated(),
            file.getSize(),
            file.getUrl(),
            "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
        ];
        sheet.appendRow(data);
    }

    var subfolders = folder.getFolders();
    while (subfolders.hasNext()) {
        processFolder(subfolders.next(), fileType, sheet); // Recursive call for subfolder
    }
}

function getFolderIdFromLink(folderLink) {
   // Extract folder ID from the folder link
   var regex = /\/folders\/([^\/?]+)/;
   var match = regex.exec(folderLink);

   if (match && match[1]) {
      return match[1];
   } else {
      return null;
   }
}

function getFileExtension(fileName) {
  var regex = /\.([a-zA-Z0-9]+)$/;
  var match = fileName.match(regex);

  if (match) {
    var fileExtension = match[1]; // Use index 1 to get the first capturing group
    Logger.log("The file extension is: " + fileExtension);
    return fileExtension;
  } else {
    Logger.log("No file extension found.");
    return null;
  }
}