Insert all photos from one google drive folder into google Sheet?

Question

I am not much of a coder, and only smart enough to modify code slightly. But I am hoping someone can help me. I am looking to upload all images in a google folder into a spread sheet with a script. I need the folder name to be changeable based on a cell, so I can change the name of the cell to a different folder run the script and the script will bring in all photos from the folder. Some folders might have 5 photos, others might have 25 photos. I have been able to figure it out but only if I reference the actual photo, and not just reference the name of the folder and have it import all. Any and all help is thanked in advance.

Answer 1

Insert all images in a folder into a spreadsheet. I've only tested this with jpeg images

function getImages() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const sh1 = ss.getSheetByName("Sheet1");//I need another sheet to force the images to the top layer
  var lr = sh.getLastRow();
  const hdr = ["FileName","FileId","FileType","Image"]
  if(lr == 0) sh.getRange(1,1,1,hdr.length).setValues([hdr]);
  const folder = DriveApp.getFolderById(gobj.globals.testfolderid);
  const files = folder.getFiles();
  var types = ["image/jpeg","image/png","image/bmp","image/gif"];//you may wish to edit this
  var fA = [];
  while (files.hasNext()) {
    let file = files.next();
    let type = file.getMimeType();
    if (~types.indexOf(type)) {
      lr = sh.getLastRow();
      sh.getRange(lr + 1, 1, 1, 3).setValues([[file.getName(),file.getId(),file.getMimeType()]]);
      let bs = Utilities.newBlob(file.getBlob().getBytes(),file.getMimeType(),file.getName());
      sh.insertImage(bs,4,lr + 1).setWidth(100).setHeight(100);
    }
    sh1.activate();
    SpreadsheetApp.flush()
    sh.activate();//for some reason images do not appear to be on the correct layer without doing this.
  }
}

Output:

Here's a freebee:

function clearContentAndImages() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.getImages().forEach(i => i.remove());
  sh.clearContents();
}

Using folder name:

function getImages() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const sh1 = ss.getSheetByName("Sheet1")
  var lr = sh.getLastRow();
  const hdr = ["FileName", "FileId", "FileType", "Image"]
  if (lr == 0) sh.getRange(1, 1, 1, hdr.length).setValues([hdr]);
  var types = ["image/jpeg", "image/png", "image/bmp", "image/gif"];
  var fA = [];
  const folders = DriveApp.getFoldersByName("Dogs");
  while (folders.hasNext()) {
    let folder = folders.next();
    const files = folder.getFiles();
    while (files.hasNext()) {
      let file = files.next();
      let type = file.getMimeType();
      if (~types.indexOf(type)) {
        lr = sh.getLastRow();
        sh.getRange(lr + 1, 1, 1, 3).setValues([[file.getName(), file.getId(), file.getMimeType()]]);
        let bs = Utilities.newBlob(file.getBlob().getBytes(), file.getMimeType(), file.getName());
        sh.insertImage(bs, 4, lr + 1).setWidth(100).setHeight(100);
      }
      sh1.activate();
      SpreadsheetApp.flush()
      sh.activate();//for some reason images do not appear to be on the correct layer without doing this.
    }
  }
}

If you would like to find all of the files in your drive that are less than 2 MB or any folder and all subfolders

function getAllImagesInDrive() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const sh1 = ss.getSheetByName("Sheet1");
  sh.clearContents();
  sh.getImages().forEach(img => img.remove());
  const hdr = ["FileName", "FileId", "FileType", "Image"];
  sh.getRange(1, 1, 1, hdr.length).setValues([hdr]);
  PropertiesService.getScriptProperties().setProperty("imgA", '[]');//initialize array
  const bsmax = 2000000;
  getImagesInFolder(DriveApp.getFolderById(DriveApp.getRootFolder().getId()));
  let fA = JSON.parse(PropertiesService.getScriptProperties().getProperty("imgA"));
  var fObj = { header: hdr, pA: [] }
  var d = 0;
  fA.forEach(([n, id, t,], i) => {
    try {
      let file = DriveApp.getFileById(id);
      let bs = Utilities.newBlob(file.getBlob().getBytes(), file.getMimeType(), file.getName());
      if (bs.getBytes().length < bsmax) {
        //sh.insertImage(bs, 4, i + 2).setWidth(100).setHeight(100);
        if (!fObj.hasOwnProperty(id)) {
          fObj.pA.push(id);
          fObj[id] = { "name": n, "id": id, "type": t, "oidx": i, "bs": bs, "keep": true }
        }
      }
    }
    catch (e) {
      console.log('Error: %s FileName: %s type: %s index: %s', e, n, t, i);
      if (fObj.hasOwnProperty(id)) {
        fObj[id]["keep"] = false;
      }
    }
  });
  fA.filter(([n, id, t,]) => fObj[id]["keep"] == true).filter(e => e);
  fA.forEach((r, i) => {
    try {
      let lr = sh.getLastRow();
      sh.getRange(lr + 1, 1, 1, r.length).setValues([r]);
      sh.insertImage(fObj[r[1]].bs, 4, i + 2).setWidth(100).setHeight(100);
    }
    catch(e) {
      console.log(`Error:${e} id: ${JSON.stringify(r[1])} fObj: ${JSON.stringify(fObj[r[1]])}`);
      sh.getRange(i + 2, 4).setValue(e);
    }
  })
  sh1.activate();
  SpreadsheetApp.flush()
  sh.activate();//for some reason images do not appear to be on the correct layer without doing this.
}

function getImagesInFolder(fldr) {
  const types = ["image/jpeg", "image/png", "image/bmp", "image/gif"];
  const smax = 2000000;//max files size
  var fA = JSON.parse(PropertiesService.getScriptProperties().getProperty("imgA"));
  var files = fldr.getFiles();
  while (files.hasNext()) {
    let file = files.next();
    let type = file.getMimeType();
    if (~types.indexOf(type) && file.getSize() < smax) {
      fA.push([file.getName(), file.getId(), file.getMimeType(), ""]);
      PropertiesService.getScriptProperties().setProperty("imgA", JSON.stringify(fA));
    }
  }
  var subfldrs = fldr.getFolders();
  while (subfldrs.hasNext()) {
    getImagesInFolder(subfldrs.next());
  }
}

The output is similar to previous version except that it also provides failure errors within the spreadsheet.

Answer 2

worked, just was annoying to start through app script so chatgpt went ahead and added some prompts to enter the info

// This function is executed when the spreadsheet is opened, adding a custom menu.
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Creates a new menu in the Google Sheets UI
  ui.createMenu('Image Importer')
    .addItem('Start Import', 'importImgs1') // Adds an item to the menu with a function to call
    .addToUi(); // Finally adds the menu to the UI
}

function importImgs1() {
  var ui = SpreadsheetApp.getUi(); // Get the UI environment to prompt the user

  // Prompt for the Sheet name
  var sheetNamePrompt = ui.prompt(
    'Sheet Name Required',
    'Please enter the name of the Sheet where you want to import images:',
    ui.ButtonSet.OK_CANCEL);

  // Check if the user clicked "OK"
  if (sheetNamePrompt.getSelectedButton() !== ui.Button.OK) {
    ui.alert('Import Cancelled', 'You did not enter a sheet name. The import process has been cancelled.', ui.ButtonSet.OK);
    return;
  }
  
  var sheetName = sheetNamePrompt.getResponseText();
  
  // Prompt for the folder ID
  var folderIDPrompt = ui.prompt(
    'Folder ID Required',
    'Please enter the Google Drive Folder ID where your images are stored:',
    ui.ButtonSet.OK_CANCEL);

  // Check if the user clicked "OK"
  if (folderIDPrompt.getSelectedButton() !== ui.Button.OK) {
    ui.alert('Import Cancelled', 'You did not enter a folder ID. The import process has been cancelled.', ui.ButtonSet.OK);
    return;
  }
  
  var folderID = folderIDPrompt.getResponseText();

  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sh) {
    ui.alert('Error', 'The sheet name you entered does not exist. Please check and try again.', ui.ButtonSet.OK);
    return; // Exit the function if the sheet does not exist
  }
  
  sh.clear(); // Clear existing content on the sheet
  sh.appendRow(["name", "image"]); // Append header row
  
  try {
    var folder = DriveApp.getFolderById(folderID); // Attempt to get the folder with the provided ID
    var data = [];
    var files = folder.getFiles(); // Get all files within the folder
    while (files.hasNext()) {
      var file = files.next();
      data = [ 
        file.getName(),
        "=image(\"https://docs.google.com/uc?export=download&id=" + file.getId() +"\")",
      ];
      sh.appendRow(data); // Append each file's data as a new row
    }
    ui.alert('Import Complete', 'Images have been successfully imported.', ui.ButtonSet.OK);
  } catch(e) {
    ui.alert('Error', 'Invalid Folder ID or an error occurred. Please check the Folder ID and try again.', ui.ButtonSet.OK);
  }
}

Answer 3

by ID

function importImgs1() {
  var folderID = '1ZfWEnxtKQiuz8V9j2ckgIZRHRMmYc7rH';
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('import images');
  sh.clear();
  sh.appendRow(["name", "image"]);
  var folder = DriveApp.getFolderById(folderID);
  var data = [];
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    data = [ 
      file.getName(),
      "=image(\"https://docs.google.com/uc?export=download&id=" + file.getId() +"\")",
    ];
    sh.appendRow(data);
  }
}

by Name

function importImgs2() {
  var folderName = 'img';
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('import images by name');
  sh.clear();
  sh.appendRow(["name", "image"]);
  var folders = DriveApp.getFoldersByName(folderName);
  var foldersnext = folders.next();
  var data = [];
  var files = foldersnext.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    data = [ 
      file.getName(),
      "=image(\"https://docs.google.com/uc?export=download&id=" + file.getId() +"\")",
    ];
    sh.appendRow(data);
  }
}

Answer 4

function insertImages() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const nameF = 'Folder'; // Sheet Name of the sheet containing the Folder Name
  const rangeF = 'A1'; // Cell containing the Folder Name
  const nameT = 'Target'; // Sheet Name of the sheet to insert images
  let row = 2; // Starting row to insert
  const col = 1; // Column to insert
  
  const sheet = ss.getSheetByName(nameT);
  const folderName = ss.getSheetByName(nameF).getRange(rangeF).getValue();
  const folders = DriveApp.getFoldersByName(folderName);
  while (folders.hasNext()) {
    const folder = folders.next();
    const files = folder.getFiles();
    while (files.hasNext()) {
      const file = files.next();
      sheet.insertImage(file.getBlob(), col, row++);
    }
  }
}