【GAS】スプレッドシートで任意のフォームを表示する＆スプレッドシートへ反映する

2021.06.15
GAS（Google Apps Script）

GAS（Google Apps Script）の案件を手掛ける上でよく使い回す処理をファンクション化してみました。
コピーしてそのまま使うこともできますし、一部カスタマイズして使ってください。
（今回はファンクションというよりプログラムの塊ですね）

「複数のサイトを横断的に見ないと答えにたどり着かない。。。」
そんな思いにこたえることができれば幸いです！

さて、今回は「スプレッドシートで任意のフォームを表示する＆スプレッドシートへ反映する」処理を紹介します。

1. はじめに
2. プログラム
3. 処理概要
4. まとめ

はじめに

スプレッドシートでもExcel VBAのようなカスタマイズした入力フォームを表示したい！という要望があるかと思います。

基本的にはalertやpromptなどの決まったポップアップしか表示できませんが、ひと工夫することで任意のフォームを表示することが可能になります。
また、そのフォームで入力した値をスプレッドシートへ反映する方法についても説明していきます。
任意のフォームを表示するというだけであれば簡単なのですが、動的なフォームを表示する場合は参考になるかと思います。

今回はどのような動きをするものなのか動画も用意していますので、参考にしてみてください。

今回はトリガーを使用しているので、トリガーの作成から動画を準備しています。

プログラム

function editTrigger(e) {
  let sheet = e.source.getActiveSheet();
  let cell = e.source.getActiveRange();
  let value = sheet.getRange(cell.getRow(), cell.getColumn()).getValue();

  checkComment(cell, value);
}

function checkComment(cell, value) {
  if (value) {
    let html = '';
    html += '<!DOCTYPE html>';
    html += '<html>';
    html += '<head>';
    html += '<base target="_top">';
    html += '<style>';
    html += '  #comment {';
    html += '    width:450px;';
    html += '    height:240px;';
    html += '  }';
    html += '</style>';
    html += '</head>';
    html += '<body>';
    html += '  <div>';
    html += '    <center>';
    html += '      <textarea id="comment">' + SpreadsheetApp.getActiveSheet().getRange(cell.getRow(), cell.getColumn() + 1).getValue() + '</textarea>';
    html += '      <br>';
    html += '      <button onclick="okClick()">OK</button>';
    html += '      <button onclick="cancelClick()">Cancel</button>';
    html += '    </cener>';
    html += '  </div>';
    html += '  <script>';
    html += '    function okClick() {';
    html += '      google.script.run.setComment(' + cell.getRow() + ', ' + cell.getColumn() + ', document.getElementById("comment").value);';
    html += '      google.script.host.close();';
    html += '    }';
    html += '    function cancelClick() {';
    html += '      google.script.host.close();';
    html += '    }';
    html += '  </script>';
    html += '</body>';
    html += '</html>';
    let commentInputBox = HtmlService.createHtmlOutput(html);
    SpreadsheetApp.getUi().showModalDialog(commentInputBox, 'フォームタイトル');
  }
}

function setComment(row, col, value) {
  SpreadsheetApp.getActiveSheet().getRange(row, col + 1).setValue(value);
  SpreadsheetApp.getActiveSheet().getRange(row, col).setValue(false);
}

処理概要

「editTrigger」がトリガー実行されるファンクションとしています。
引数のeには様々な情報が入っていますが、ここでは説明を割愛して利用しているもののみに言及します。
「e.source.getActiveSheet()」でトリガーが実行された時点でアクティブなシートを返しています。
「e.source.getActiveRange()」でトリガーが発火された時のセルを返しています。
このセルから対象行と対象列を取得しているのですが、このロジックはよく使うので覚えておくとよいです！
（今回はどこを編集してもフォームが表示されますが、通常は条件式で対象範囲を限定するようにします）

「checkComment」が任意のフォームを表示するためのファンクションになります。
ポイントとなっているのは、

同一スクリプト内にHTMLコードを生成する処理を記載している
google.script.runでスプレッドシートのファンクションを呼び出すようにしている

動的なフォームと値を返すようにするのはこの２点が必須です！
動的なフォームを作成するには同一スクリプト内でHTMLコードを生成するしかなく、HTMLフィルを別に用意するとうまくいきませんでした。
（うまくいく方法があったら教えてほしいです！！）
ここでいう動的というのはスプレッドシートからの値をフォームに反映させることをいっていますが、他にも色々と応用可能です！
値を返すために「google.script.run」でスプレッドシートのファンクションを呼び出すようにしています。
これはGASでWebサイトを作る際によく使われる方法なのですが、同じ要領で使用します。

「setComment」がフォームで入力した値を受け取るためのファンクションになります。
ここでは、チェックボックスもOFFにする処理を実装しているので、「editTrigger」で範囲を限定するようにすれば、そのままでも十分に使えるはずです。

まとめ

今回は「スプレッドシートで任意のフォームを表示する＆スプレッドシートへ反映する」処理について紹介しました。
色々と組み合わせるとより高度なことができるので、色々と試してみてください！
今後も様々な内容を記事にする予定なので、よかったらお気に入り登録してください♪
このサイトの内容は引用元を紹介して頂ければ自由に引用して頂いて構いません。

Google Apps Script は無料枠でも十分に使えとても便利です。
OSに依存せずに使えることから様々な環境で自動化・省力化してスローライフを楽しみましょう！

GASでの簡易システム制作に関するお問い合わせはこちらからお待ちしております。
LancersやMENTAでも活動していますので、そちらからお仕事を依頼して頂けます。