Googleスプレッドシートのカスタムファンクションを使ってチャートをセルに埋め込む

概要

Googleスプレッドシートのカスタムファンクションを使ってチャートをセルに埋め込む方法についてご紹介します。

デモ

本内容を使用すると、下記デモのようにセル"B7"にカスタムファンクション=embedChart("A2:A6")を入力することでセル"A2:A6"にあるデータをチャート化し、画像としてセルに埋め込むことができます。

フロー

チャートをセルに埋め込むための全体のフローは、データ範囲を入力してチャートを作成し、画像として保存した後にセルに埋め込みます。

insertChart()はチャートをスプレッドシート上に貼り付けることはできますが、セルに埋め込むことはできません。調べてみましたが、私の方ではGASを使ってチャートをセルへ直接埋め込む方法は見当たりませんでした。そのため、=IMAGE()関数を使用することにしました。

問題点

取り組み始めて直ぐにスプレッドシートのカスタムファンクションは結構な制限があることに気付きました。

1. チャートを作成するためのnewChart()は使用できますが、貼り付けるためのinsertChart()（これは今回は使用しませんが。）は使用できません。
2. DriveAppが使用できません。そのため、画像として保存することができません。
3. setFormula()が使用できません。そのため、=IMAGE()関数を挿入することができません。

解決策

これらの問題を解消するためにWeb Appsを使用することにしました。これまでの経験からWeb Appsを使うといろいろな制限を回避することができることが分かっています。例えば、Execution APIではできないトリガー設定がWeb Appsではできるなど。

実際にWeb Appsを適応させてみたところ、上記の全ての問題は解消されました。

Web Appsのデプロイ

本家のドキュメントはこちらです。

1. スクリプトエディタの上部にある公開をクリック
2. ウェブアプリケーションとして導入をクリック
3. プロジェクトバージョンを新規作成で適当な名前で作成
4. 次のユーザとしてアプリケーションを実行では「自分」にセット
5. アプリケーションにアクセスできるユーザを「全員（匿名ユーザを含む）」にセット
6. OKをクリック

以上でWeb Appsのデプロイは完了です。

ここで注意するところは、スクリプトを修正した際には上の3にある「プロジェクトバージョンを新規作成で適当な名前で作成」を行って必ず更新する必要があることです。これを失念するとスクリプトを変更しても反映されないといった問題につながります。

スクリプト

下記スクリプトをスプレッドシートから起動したスクリプトエディタ内へ貼り付けてください。貼り付けた後に、Web Appsをデプロイしてください。

var folderId = "### Folder ID ###";
var webappsurl = "https://script.google.com/macros/s/######/exec";

function embedChart(range) {
  var ac = SpreadsheetApp.getActiveSheet().getActiveCell();
  var q1 = "?datarange=" + range;
  var q2 = "&row=" + ac.getRow();
  var q3 = "&col=" + ac.getColumn();
  var url = webappsurl + q1 + q2 + q3;
  UrlFetchApp.fetch(url);
}

function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.PIE)
    .addRange(sheet.getRange(e.parameters.datarange))
    .setOption('height', 280)
    .setOption('width', 480)
    .setOption('title', 'Sample chart')
    .build();
  var file = DriveApp.getFolderById(folderId).createFile(
    chart.getAs('image/png').setName("chart_image.png")
  );
  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  sheet.getRange(e.parameters.row, e.parameters.col).setFormula(
    '=IMAGE("' + "http://drive.google.com/uc?id=" + file.getId() + '")'
  );
}

スクリプトのフロー

• embedChart()
  • =embedChart("a2:a6")をセル"B7"へ入力
  • fetch()を使ってWeb AppsのdoGet()へデータ範囲"a2:a6"を送信
• doGet()
  • doGet()でデータを取得（doGet()の中で制限にひっかかる作業をさせます）
  • 受信したデータ範囲"a2:a6"を使ってチャートを作成（サンプルとしてpieチャートに設定します）
  • チャートを画像として保存（今の場合PNGとして保存します）
  • =IMAGE()で使用できるように権限を変更（IMAGE関数はGoogle Drive内のファイルを直接見ることはできないようです）
  • setFormula()を使って=IMAGE(URL)を=embedChart("a2:a6")のセルへ上書き（これによりシートを再度読み込んだ際に画像を直接読み込むため表示速度が向上します）

気付いたこと

=embedChart("a2:a6")を入力してから画像が表示されるまで約40秒の時間がかかってしまいます。スクリプトエディタ上で行うと数秒程度で終了します。この差はカスタムファンクションによると考えられるのでしょうか。これを何とかしようといろいろ試しましたが解決には至っていません。