はじめに

みなさんご存知の通り、Google スプレッドシートはとても便利な表計算ソフトです。
コラボレーション機能が豊富な事もあり、業務においても表計算の枠を超えて情報集約ツール（？）としても活用される事があるかと思います。そんな使い方をしていると、往々にして 画像を表示したくなる こともあります。

そんな場合、通常は以下のように IMAGE 関数を利用することになります。

=IMAGE("https://example.com/images/sample.png")

ただし、IMAGE 関数のドキュメントには以下のような記述があります。

重要: 使用できる URL は、drive.google.com でホストされていないもののみです。SVG ファイル形式はサポートされていません。

これは困ったことになりました。

なぜなら業務でスプレットシートを扱う場合、たいてい Google Workspace で組織のアカウントとして運用が行われます。その場合 組織内限定で扱っている Google Drive 内の画像を、組織内限定のスプレッドシートで表示したい という要望が発生するためです。

一応 Drive でホストされている画像でも、全体公開すれば IMAGE 関数で表示することはできますが、それはセキュリティ上好ましくありません。

また別の方法として [挿入] > [画像] から手動でセルに画像を埋め込むことができます。
こうすればスプシ自体に画像が含まれるので、実質的に制限なく画像を表示することができます。

ただし、埋め込む方式は大量の画像を扱いたい場合に厄介な点があります。
関数ではないので一括処理が行えず非常に面倒であることや、スプシ自体のサイズが画像のぶん肥大化していくことなどです。

↑1MBの画像を埋め込んだ時の図（画像がスプレッドシートのサイズに含まれている）

GAS で Drive 画像をスプシに埋め込む

そこで GAS を利用して問題を解決します。
GAS であれば、セルから URL を読み取って画像をダウンロードし、スプシに画像を埋め込むまでを自動で一括処理することができます。
組織内限定で扱っている Google Drive 内の画像は、組織のアカウントであれば GAS でアクセスが可能です。これをセルに埋め込んでしまえば IMAGE 関数を使わないので制約を回避できるというわけです。（当然、スプシが見えれば埋め込まれた画像も見えるので、それぞれの公開範囲には十分注意する必要があります。）

またスプシ自体のサイズを無駄に肥大化させないよう、生画像の代わりに Google Drive のサムネを使うことで、いい塩梅に圧縮された画像を用意することもできます。

GAS で DriveAPI を使う

GAS では様々な Google サービスの API を使う事ができ、もちろん Drive API も利用することができます。

GAS の編集画面左パネルの サービス から追加する事で利用することができます。
サービスの ID として Drive を設定した場合、次のようにたった２行で Drive の特定ファイルのサムネ画像のバイナリを取得できます。※fileId については後述

const file = Drive.Files.get(fileId, {'fields': 'thumbnailLink'});
const blob = UrlFetchApp.fetch(file.thumbnailLink).getBlob();

取得した blob は Base64 エンコードすることでセルに埋め込むことが可能です。
関数にすると、こんな感じ。

function buildCellImage(blob) {
  var c_type = blob.getContentType();
  var base64 = Utilities.base64Encode(blob.getBytes());
  var data = "data:" + c_type + ";base64, " + base64;
  return SpreadsheetApp.newCellImage().setSourceUrl(data).build();
}

FileID について

Google Drive において FileID は、ドライブ上の各ファイルを一意に識別するための文字列です。
Google Drive の UI 上でアイテムを選択しコピーして取得できる URL にも含まれています。

// id の部分が FileID
https://drive.google.com/open?id=1GxhYqIwd_NN6kacSnPaIWVvryKZiLVep&usp=drive_copy

こんな感じで URL からクエリパラメータを取得する補助関数を作っておくと便利です。

function getQueryParams(url) {
  var params = {};
  if (!url) return params;
  var tokens = url.split('?');
  if (tokens.length < 2) return params;
  var keyValuePairs = tokens[1].split('&');
  for (var i = 0; i < keyValuePairs.length; i++) {
    var keyValue = keyValuePairs[i].split('=');
    params[decodeURIComponent(keyValue[0])] = decodeURIComponent(keyValue[1]);
  }
  return params;
}

完成

というわけで、以下が完成形のソースコード全体です。
スプシ全体から Google Drive のリンクを探して、UI から実行できるようにしてあります。

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('GAS実行')
  .addItem('ドライブURLを画像に差し替え', 'replaceDriveImage')
  .addToUi();
}

function replaceDriveImage() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const values = range.getRichTextValues();
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      const url = values[i][j].getLinkUrl();
      // URL がドライブ画像なら、ダウンロードしてセルに設定
      if (url && url.indexOf('https://drive.google.com/open') !== -1) {
        const fileId = getQueryParams(url)['id'];
        const file = Drive.Files.get(fileId, {'fields': 'thumbnailLink'});
        const blob = UrlFetchApp.fetch(file.thumbnailLink).getBlob();
        const image = buildCellImage(blob);
        sheet.getRange(i + 1, j + 1).setValue(image);
      }
    }
  }
}

function getQueryParams(url) {
  var params = {};
  if (!url) return params;
  var tokens = url.split('?');
  if (tokens.length < 2) return params;
  var keyValuePairs = tokens[1].split('&');
  for (var i = 0; i < keyValuePairs.length; i++) {
    var keyValue = keyValuePairs[i].split('=');
    params[decodeURIComponent(keyValue[0])] = decodeURIComponent(keyValue[1]);
  }
  return params;
}

function buildCellImage(blob) {
  var c_type = blob.getContentType();
  var base64 = Utilities.base64Encode(blob.getBytes());
  var data = "data:" + c_type + ";base64, " + base64;
  return SpreadsheetApp.newCellImage().setSourceUrl(data).build();
}

これを 拡張機能 > Apps Script に保存し、ヘッダメニューから GAS を実行します。
すると…

このようにリンクが次々 Drive のサムネ画像に置き換わっていきます！

以上で 組織内限定で扱っている Google Drive 内の画像を、組織内限定のスプレッドシートで表示したい という要望に応えることができました🥳

おわりに

スプレッドシートでやりたいことは、大抵 GAS を利用することで実現できます。
GAS を活用するとスプレッドシート運用は大幅に捗ります。皆様もぜひご活用ください。

以上、明日から業務で使える GAS の小ネタでした。

明日は @yakisabananoka さんです。