GASを用いてGoogleドライブに格納された画像ファイルをスプレッドシートに展開したい

Question

実現したいこと

ここに実現したいことを箇条書きで書いてください。
・Googleドライブ内に保存された画像ファイルをスプレッドシートに展開したい
・画像は10枚ずつ列方向に並べ、1行下に画像のリンクを展開したい
・リンクの下に次の10枚を展開し、フォルダ内の画像をすべて展開するまで処理を繰り返したい
・処理はB2セルから開始したい

前提

Googleドライブ内にはサブフォルダがあるため、全てのサブフォルダを探索し処理するようにしています。
また、画像ファイルの拡張子がHEICのものが混ざっているためそれを除去するようスクリプトを組んでいます。

発生している問題・エラーメッセージ

エラーメッセージは出ていないものの、画像ファイルが表示されていません。
IMAGE関数や画像ファイルのリンクは出力されているので、スクリプトそのものは問題なく動作しているようです。

該当のソースコード

function insertImagesFromDrive() {
  var folderId = 'YOUR_FOLDER_ID'; // GoogleドライブのフォルダIDを指定してください。
  var folder = DriveApp.getFolderById(folderId);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startRow = 2; // B2セルから開始します
  var startColumn = 2; // B列から開始します
  var imagesPerRow = 11; // B2から始まり一行に10枚の画像を配置します
  processFolder(folder, sheet, startRow, startColumn, imagesPerRow);
}

function processFolder(folder, sheet, startRow, startColumn, imagesPerRow) {
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    var fileName = file.getName();
    if (fileName.match(/\.(jpg|jpeg|png|gif)$/i)) { // 画像ファイルのみを処理します
      var fileUrl = file.getUrl(); // ファイルのURLを取得します
      var imageFormula = '=IMAGE("' + fileUrl + '")'; // IMAGE関数を作成します
      sheet.getRange(startRow, startColumn).setFormula(imageFormula); // IMAGE関数をセルに挿入します
      sheet.getRange(startRow + 1, startColumn).setValue(fileUrl); // 画像のURLを次の行に挿入します
      startColumn++;
      if (startColumn > imagesPerRow) {
        startRow += 3; // 画像とURLを展開する行を1行下に移動します
        startColumn = 2; // 列をB列にリセットします
      }
    }
  }
  var subfolders = folder.getFolders();
  while (subfolders.hasNext()) {
    var subfolder = subfolders.next();
    processFolder(subfolder, sheet, startRow, startColumn, imagesPerRow); // 再帰的にサブフォルダを処理します
  }
}

試したこと

セルの大きさが干渉しているのかと思い調整したりしましたが、それでも画像が表示されず、
検索している中でドライブリンクからIMAGE関数で直接画像を表示できない旨発見したのですが、
解決方法がわからずでして、お力添えいただけますと幸いです。

Comments

[YellowGreen]

var fileUrl = file.getUrl(); // ファイルのURLを取得します

を

var fileUrl = "https://drive.google.com/uc?export=download&id=" + file.getId();

としてみるとどうなりますか？

[YAM]

ご提案ありがとうございます。
変更してみましたが、相変わらず画像が表示されずでした…

[YAM]

公開設定がセキュリティの関係で同一ドメインでなければ共有できないのですが、関係ありそうでしょうか？
（同一ドメインであれば誰でもアクセス可能ではあります）

[YellowGreen]

画像ファイルの共有設定はどうなってますか？
image関数で表示可能（リンクを知っている全員...）にしてありますか？

[YAM]

上記の通り、リンクを知っている全員…の設定にはなっておりますが、同一ドメインのみアクセス可能となっております。

[YellowGreen]

リンクを知っている全員がアクセスできる共有なら、同一ドメインのみでなくても、文字どおりGoogleアカウントがなくてもリンクを知っている全員がアクセスできると思いますが。

[YellowGreen]

Googleからログアウトした状態で画像のリンクから画像を表示できるなら共有設定は問題ないです。

[YAM]

同一ドメインの社員しか入れないグループで個々人のアカウントが管理されておりまして、Googleからログアウトすると画像の表示はできませんでした。

[YellowGreen]

であれば、image関数ではなく、
スクリプトから直接貼り付ける
sheet.insertImage()か
SpreadsheetApp.newCellImage()か
でしょうか。

[YAM]

画像ファイルが2MB以上のものもあるため制限の少ないimage関数で処理したかったのですが、難しそうですね…
ご提案ありがとうございました、もう少しできることがないか試してみます。

[YellowGreen]

以下のコードは、エンコードの処理時間がかかるので、
ファイル数が多いとタイムアウトもあり得ますが、
2MBを超える画像も処理対象になると思います。
どうでしょうか。

var fileUrl = file.getUrl(); // ファイルのURLを取得します
var blob = file.getBlob();
var c_type = blob.getContentType();
var base64 = Utilities.base64Encode(blob.getBytes());
var data = 'data:' + c_type + ';base64,' + base64;
var image = SpreadsheetApp.newCellImage()
.setSourceUrl(data)
.build();
sheet.getRange(startRow, startColumn).setValue(image);//画像をセルに挿入します。
sheet.getRange(startRow + 1, startColumn).setValue(fileUrl); // 画像のURLを次の行に挿入します

[YAM]

ありがとうございます。
いただいた内容でうまくいきそうです。
タイムアウト対策も盛り込んで調整してみたいと思います。

Answer 1

いただいた内容をもとにタイムアウト対策も盛り込んでみました。
今のところテストデータでしか試してないのですが、とりあえず成功したのでこちらで回答とさせていただきます。

function insertImagesToSpreadsheet3() {
  var folderId = 'YOUR_FOLDER_ID'; // フォルダIDを貼り付けます
  var mainFolder = DriveApp.getFolderById(folderId);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startRow = 2; // 画像を挿入する開始行
  var startColumn = 2; // 画像を挿入する開始列
  var imageCount = 0; // 画像の数をカウントします
  var startTime = new Date();
  var triggerCreated = false;

  // フォルダ内のファイルを処理する関数
  function processFiles(folder) {
    var files = folder.getFiles();
    while (files.hasNext()) {
      var file = files.next();
      var fileType = file.getMimeType();
      // ファイルがjpeg, jpg, png画像の場合のみURLをスプレッドシートに挿入します
      if (fileType === "image/jpeg" || fileType === "image/jpg" || fileType === "image/png") {
        var fileUrl = file.getUrl(); // ファイルのURLを取得します
        var blob = file.getBlob();
        var c_type = blob.getContentType();
        var base64 = Utilities.base64Encode(blob.getBytes());
        var data = 'data:' + c_type + ';base64,' + base64;
        var image = SpreadsheetApp.newCellImage()
        .setSourceUrl(data)
        .build();
        sheet.getRange(startRow, startColumn).setValue(image); //画像をセルに挿入します。
        sheet.getRange(startRow + 1, startColumn).setValue(fileUrl); // 画像のURLを次の行に挿入します
        startColumn += 1; // 次の列に移動します
        imageCount += 1; // 画像の数を増やします
        // 1行に4枚の画像を挿入したら次の行に移動します
        if (imageCount % 4 === 0) {
          startRow += 2; // URLを挿入する行をスキップして次の行に移動します
          startColumn = 2; // 列を初期化します
        }
      }
      if((new Date().getTime() - startTime.getTime()) / 1000 > 300) {
        if(!triggerCreated) {
          ScriptApp.newTrigger('insertImagesToSpreadsheet3')
            .timeBased()
            .after(1000 * 60 * 1)  // 1分後にトリガー設定
            .create();
          triggerCreated = true;
        }
        return; // break out of the loop and end the function
      }
    }
  }

  // フォルダとサブフォルダを処理する関数
  function processFolder(folder) {
    processFiles(folder);
    var subFolders = folder.getFolders();
    while (subFolders.hasNext()) {
      processFolder(subFolders.next());
    }
  }
  processFolder(mainFolder);

  // If all images processed and trigger was created, then delete it
  if(triggerCreated) {
    var triggers = ScriptApp.getProjectTriggers();
    for(var i=0; i<triggers.length; i++) {
      if(triggers[i].getHandlerFunction() === 'insertImagesToSpreadsheet3') {
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
  }
}