GAS

【GAS】非共有のGoogleドライブの画像をスプレッドシートのセル内に貼り付ける方法

今回は、Googleドライブに格納された画像を共有することなくスプレッドシートのセル内に貼り付ける方法をご紹介します。

通常の画像挿入の不便なところ

通常、画像をセル内に貼り付ける場合は、スプレッドシートのツールバー「挿入->画像をセル内に挿入」で行います。

このようにセル内に画像を挿入することで、値としてコピペやVLOOKUP・IMPORTRANGEなどの関数で他のシートやブックから参照することができるため、非常に重宝します。

そんな画像挿入の不便なところ

実際に活用していくと、複数の画像を一度に各セル内に挿入したいケースが出てくると思います。ですが、一括貼り付けのような機能がいまのスプレッドシートには残念ながら存在しません。

IMAGE関数を使って、画像を参照する方法もありますが、そのためにはインターネットから直接アクセスできるURLを指定する必要があります。社内の機密情報や個人的に公開したくない画像の場合は一つ一つのセルに対して上で紹介したような操作を手作業でしていく必要があります。

対象の画像が10個程度なら良いのですが、それ以上となるとかなりの手間です。こんな時こそGASを使って作業を効率化ていきましょう。

画像を一括で挿入するスクリプト

以下の画像のようなスプレッドシートがあったとします。B列にA列と同じファイル名の画像を貼り付けたいという状況です。

また、貼り付けたい画像はGoogleドライブに非共有(インターネット上に公開されていない)で格納されています。

コード

function setImageValue() {
  const ss = SpreadsheetApp.openById("[貼り付けたいスプレッドシートID]")
  const sh = ss.getSheetByName("[貼り付けたいシート名]")

  const folder = DriveApp.getFolderById("[GoogleドライブID]")
  const folderFiles = folder.getFiles()
  const fileMap = getFileMap(folderFiles)

  const fileNameArray = sh.getRange(3, 1, sh.getLastRow() - 2, 1).getValues().flat()
  for (let i = 0; i < fileNameArray.length; i++) {
    const fileId = fileMap.get(fileNameArray[i])
    if (!fileId) continue
    const obj = DriveApp.getFileById(fileId)
    const blob = obj.getAs(MimeType.PNG)

    insertCellImage(sh.getRange(i + 3, 2), blobToDataUrl(blob))
  }
}

function getFileMap(folderFiles) {
  let contacts = new Map()
  while (folderFiles.hasNext()) {
    const file = folderFiles.next()
    contacts.set(file.getName(), file.getId())
  }

  return contacts
}

function insertCellImage(range, sourceUrl) {
  range.setFormula('=IMAGE("http")')
  const builder = range.getValue().toBuilder()
  builder.setSourceUrl(sourceUrl)
  range.setValue(builder.build())
}

function blobToDataUrl(blob) {
  return `data:${blob.getContentType()};base64,${Utilities.base64Encode(blob.getBytes())}`
}

コードを実行してみましょう。

セル内に画像が挿入されいるのがわかると思います。

処理がタイムアウトしてしまう場合

御覧の通り、処理が重く1件当たり1秒ぐらいかかりますので、貼り付けたい画像の数によっては1回のスクリプト実行の上限6分を超えてしまう可能性があります。その場合は多少工夫をして処理を6分以内に収めるか、タイムアウト後も継続して実行されるようにする必要があります。

具体的な方法については、こちらの記事を参照ください。

関連記事【GAS】「起動時間の最大値を超えました」エラー時の対策 処理の継続編

プログラムの解説

実は現在のGASでセル内に画像を挿入する方法は公式のドキュメントには載っていません。そのため、GASの非公開関数を使って今回の機能を実現しています。

そのため、今後のGASのアップデート次第ではスクリプトが動作しなくなる可能性もあります。あらかじめご承知おきください。

今回重要が部分は以下の2つのメソッドです。

function insertCellImage(range, sourceUrl) {
  range.setFormula('=IMAGE("http")')
  const builder = range.getValue().toBuilder()
  builder.setSourceUrl(sourceUrl)
  range.setValue(builder.build())
}

function blobToDataUrl(blob) {
  return `data:${blob.getContentType()};base64,${Utilities.base64Encode(blob.getBytes())}`
}

現在のGASではセル内に画像を挿入するには、IMAGE関数に画像のURLを渡す必要があります。しかし今回は公開用のURLを発行できないようなものを扱うので、ただsetFormulaでIMAGE関数を挿入する方法は使えません。

そこで、今回は画像ファイルのデータをURLとして渡せる形式に変換して、URLの挿入を実現しています。データURLという先頭に「data:」から始まるURLをblobToDataUrl関数で生成しています。

https://developer.mozilla.org/ja/docs/Web/HTTP/Basics_of_HTTP/Data_URIs

このデータURLをsetSourceUrlに渡すことでセル上に画像が挿入されます。

まとめ

今回は「非共有のGoogleドライブの画像をスプレッドシートのセル内に貼り付ける方法」をご紹介しました。

セル内に画像を張り付ける機能自体が、まだ新しめ機能であり今後アップデートでまだまだ便利になる余地があります。きっと今回作った一括挿入の機能もいつか追加されるでしょう。

それまでは、このような多少むりやりな感のあるスクリプトを使っていただければと思います。