Googleスプレッドシートで複数枚の画像をまとめて挿入する方法【GAS活用】

function showFolderInputDialog() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt("GoogleドライブのフォルダIDまたはフォルダ名を入力してください:");
  if (response.getSelectedButton() == ui.Button.OK) {
    var folderInput = response.getResponseText();
    insertImagesFromDrive(folderInput);
  }
}

function insertImagesFromDrive(folderInput) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var folder = getFolderByIdOrName(folderInput);
  
  if (!folder) {
    SpreadsheetApp.getUi().alert("指定されたフォルダが見つかりません。");
    return;
  }
  
  var files = folder.getFiles();
  var range = sheet.getActiveRange();
  var startRow = range.getRow();
  var col = range.getColumn();
  var supportedFormats = ["image/png", "image/jpeg", "image/gif"];

  var row = startRow;

  while (files.hasNext()) {
    var file = files.next();
    var mimeType = file.getMimeType();
    
    if (supportedFormats.includes(mimeType)) {
      try {
        var publicUrl = getPublicImageUrl(file);
        
        if (publicUrl) {
          sheet.getRange(row, col).setFormula(`=IMAGE("${publicUrl}")`);
          row++;
        }
      } catch (e) {
        Logger.log("Error processing file: " + file.getName() + " - " + e.toString());
      }
    }
  }
}

function getFolderByIdOrName(folderInput) {
  try {
    return DriveApp.getFolderById(folderInput);
  } catch (e) {
    var folders = DriveApp.getFoldersByName(folderInput);
    return folders.hasNext() ? folders.next() : null;
  }
}

function getPublicImageUrl(file) {
  try {
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    return "https://drive.google.com/uc?id=" + file.getId();
  } catch (e) {
    Logger.log("Failed to set sharing for file: " + file.getName() + " - " + e.toString());
    return null;
  }
}