Googleフォームのカスタマイズ

Google Appsのサービスにありながら、正直な所あまり活用されていないというか、便利なのかよくわからないのがGoogleフォーム。ウェブ検索をしてみると、活用と銘打ってはいるものの殆どは基本機能のみを使ってるだけで、マニアックな改造などに関しては、ほとんどない。ということで、いかほどのものなのか？というメモを残していきたい。ちなみに、自分は仕事上での話ではあるものの、かなり使っている状態。無茶難題も飛んでくるので、最終的にはフォームでは対応しきれないし、其の場合のテクニックも検討中である。

各種ソースコード

自動応答返信スクリプト

function sendForms(e){

  try{

    // メール送信先の設定

    var tomail = ScriptProperties.getProperty('mail'); //送信先メールアドレス★1

    var titlename = ScriptProperties.getProperty('titlename'); //送信元名前

    var answerdb = ScriptProperties.getProperty('answerdb');

    var cc = ""// Cc:★2

    var tantou = "";

    var tantouname = "";

    var hospname = "";

    var z = 1;

    // ▼▼▼メール送信処理▼▼▼

    var FORM_DATA = e.response.getItemResponses();

    // 件名、本文の設定

    //改行するには <br>や<p> を入れてください

    var footer = '<br>'

        + "本件に関わるお問い合わせは、○○○部（電話番号：03-1259-96414）までご連絡ください。<br>";

    var bodytop = "「○○○」を受け付けました。<br>下記の発行番号は、問い合わせ時に必要になりますため、必ず保存してください。<br>"

       + "上長の確認・承認後、改めてご連絡致します。<br><br>"

                + "<b><div style='color:red; font-size:14pt;'>発行番号:" + uid + "</div></b><br><br>"

                + "【連絡事項】<br>"

                + "・本メールにて返信する場合は、‟全員に返信”にて送信ください。<br><br><br>";

    var body =""; //本文

    // 入力項目を本文に埋め込む

    for (var j = 0; j < FORM_DATA.length; j++){

      body += "【"+FORM_DATA[j].getItem().getTitle()+"】<br>";

      body += FORM_DATA[j].getResponse()+"<br><br>";;

      //入力されたメールアドレスをtoに入れる

      if(FORM_DATA[j].getItem().getTitle() == "担当者のメールアドレス" && FORM_DATA[j].getResponse() != ""){

        tantou = FORM_DATA[j].getResponse();

      if(FORM_DATA[j].getItem().getTitle() == "担当者の氏名" && FORM_DATA[j].getResponse() != ""){

        tantouname = FORM_DATA[j].getResponse();

      if(FORM_DATA[j].getItem().getTitle() == "担当者の所属施設" && FORM_DATA[j].getResponse() != ""){

        hospname = FORM_DATA[j].getResponse();

    //件名作成と文面のレイアウト調整

    var subject = "【発行番号：" + uid + "】送信ドキュメント名" + hospname + "_" + getDate(); //件名★4

    body += footer;

    body = bodytop + body;

    //送信者自身のメールアドレスをCCに追加する

    cc = cc + tantou;

    MailApp.sendEmail({

       to: tomail,

       subject: subject,

       htmlBody: body,

       cc: cc,

       name:titlename,

});

  } catch (e) {

    //エラーが発生した場合に管理者にメールを送信する

    //MailApp.sendEmail(tantou, "Error report", e.message);

自動応答スクリプトといっても、特別なことをしているわけではなく、フォーム送信時のスクリプトトリガーでもって起動するメール送信スクリプトである。

入力内容を拾って、本文に記載し、相手先だけじゃなく、自分に対してもCCという形で送信内容を即時に送れるようにMailAppでもって送っているというものである。わかりやすくするために、MailAppオプションとして、今回は、htmlBodyを使っている。HTMLメールとする為であるが、これを使わない時は、「\n」を入れると改行コードとみなしてくれる。htmlBodyの場合には、タグとして<br>や<P>タグを使わないと、改行されないので注意。

スクリプトを書いたら、必ずスクリプトトリガーにて、現在のプロジェクトとしてこのルーチンを追加することをお忘れなく。

自動ファイル生成スクリプト

//フォーム入力内容を拾って、ドキュメントを生成し、指定フォルダに格納するルーチン

function docsgenerator() {

  //各種変数の宣言と格納（プロジェクトプロパティの値を取得する）

  var templatedocs =  ScriptProperties.getProperty('templatedocs');      //ドキュメントのテンプレートファイルのIDを取り出す

  var targetfolder =  ScriptProperties.getProperty('targetfolder');           //ドキュメントを生成するフォルダの指定（最も上位の親フォルダ）

  var oyafolder = DriveApp.getFolderById(targetfolder);                           //親フォルダのIDを取得

  var rootfolder = DriveApp.getRootFolder();                                           //ルートフォルダを取得

  var movetarget = "";

  var movefiles = "";

  //テンプレートファイルをコピーしてIDを取得する

  var files = DriveApp.getFileById(templatedocs).makeCopy("初期ドキュメント名" + new Date());

  var filesId = files.getId();

  var parentsfolder = DocsList.getFileById(filesId).getParents()[0].getId();

  var tempparents = DriveApp.getFolderById(parentsfolder);

//作成したファイルにフォームデータを書き込む

  var sheet = SpreadsheetApp.openById(filesId);

  var hospname = kinniku[1].getResponse();

  var makeday = sheet.getRangeByName("makeday").setValue(getDate());

  var yournames = sheet.getRangeByName("yournames").setValue(kinniku[0].getResponse());

  var tantou = sheet.getRangeByName("tantounames").setValue(kinniku[0].getResponse());

  var location = sheet.getRangeByName("locationname").setValue(kinniku[1].getResponse());

var section = sheet.getRangeByName("sectionnames").setValue(kinniku[2].getResponse());

  var targetdays = sheet.getRangeByName("targetdays").setValue(kinniku[3].getResponse());

  var descript = sheet.getRangeByName("description").setValue(kinniku[4].getResponse());

  var betweenday = sheet.getRangeByName("betweenday").setValue(kinniku[5].getResponse());

  var comments = sheet.getRangeByName("comments").setValue(kinniku[6].getResponse());

  //作成書類固有のIDを発行し、フォームデータに書き込む

  uid = Number(ScriptProperties.getProperty('UniqueID')) + Number(1);

  ScriptProperties.setProperty('UniqueID',uid);

  var uniqueid = sheet.getRangeByName("UniqueID").setValue(uid);

  //シートの保護を設定

  var targetsheet = sheet.getSheets()[0];

  var permissions = sheet.getSheetProtection();

  permissions.setProtected(true);

  targetsheet.setSheetProtection(permissions);

  //作成したファイルをリネームする

  DocsList.getFileById(filesId).rename("定形ドキュメント名" + hospname + "_" + kinniku[3].getResponse() + "_" + uid);

//作成したファイルを指定のフォルダ内に格納する

  //対象のフォルダ内にhospnameと合致するフォルダが存在するかチェック

  var tempFolder = DriveApp.searchFolders("title = '"+hospname+"' and '"+targetfolder+"' in parents");

  tempFolder.hasNext();

  try{

    var tempList = tempFolder.next();

    var testflag = 1;

    var hospfolderid = tempList.getId();

  }catch(e){

    var testflag = 0;

    //フォルダがないので作成する

    var temphospfolder = DriveApp.createFolder(hospname);

    rootfolder.removeFolder(temphospfolder);

    oyafolder.addFolder(temphospfolder);

    var hospfolderid = temphospfolder.getId();

  //移動先を変数に格納

  movetarget = DriveApp.getFolderById(hospfolderid);

  movefiles = DriveApp.getFileById(filesId);

  tempparents.removeFile(movefiles);

  movetarget.addFile(movefiles);

  //作成したドキュメントのIDを返してあげる

  fileId = filesId;

  var tempman = "https://docs.google.com/a/hmw.gr.jp/spreadsheets/d/" + filesId;

  return tempman;

//整形した日付を返す関数

function getDate(){

var date = new Date();

var year = date.getFullYear();

var month = date.getMonth() + 1;

var date = date.getDate();

if (month < 10) {

month = "0" + month;

}

if (date < 10) {

date = "0" + date;

}

var strDate = year + "/" + month + "/" + date;

    Logger.log(strDate)

return strDate;

このスクリプト群は、予め用意してあるテンプレートファイルを複製して、テンプレートファイルに設定していある名前付き範囲のそれぞれの項目に、フォームデータを当てはめ、所定のフォルダに格納し、そのファイルのIDを返す関数である。また、同時に組織名（hospname) に応じて、親フォルダ内にフォルダを組織名で作成するルーチン、作成したファイルを移動するためのルーチンなどなどを含めて、一連の処理を自動化しているものである。

故に特筆すべき点は、特にないが、こういったルーチンを用意しておくことで、入力者に二度手間をさせること無く、また受けては自動的に生成されたドキュメントを元に印刷回覧へと繋げられる為、時間的節約にもつながるものと考えられる。

自動CC追加スクリプト

    var FORM_DATA = e.response.getItemResponses();

    hospname = FORM_DATA[1].getResponse();

    //ccdbからhospnameを持って、ccメールアドレスを取得する。

    var sheet = SpreadsheetApp.openById("CCの行き先が登録されているスプレッドシート");

    var sheetman = sheet.getSheetByName("CC一覧").getRange("A2:G").getValues();

    for(var i = 0;i < sheetman.length;i++){

      if(sheetman[i][0] == hospname){

        //フォーム内容とCC一覧の項目がヒットしたら、ccデータがあるやつを全て変数に含める

        for(var j = 1;j < 6;j++){

          if(sheetman[i][j] == ""){

          }else{

            cc =  cc + sheetman[i][j] + ",";

      }else{

このスクリプトは、いちいちCCを入れるのが面倒で、定型的にフォーム送信時にCCでお知らせしたい場合に作成したスクリプトです。

別途、CC先を登録しておいたデータベースを用意しておき、スクリプトは特定の項目がフォームで選択されていた場合、その選択項目名と同じものがヒットするレコードにあるCC先をループで変数ccに突っ込んでいくというものです。後は、この変数ccをMailAppでCC送信先として指定すれば良いだけです。

CC先登録データベースは非常に単純なもので、A列にフォームの特定の選択項目のリストと同じだけのレコードを用意し、BからGまでの6列にそれぞれの項目に該当するCC先メアドを格納してあります。最大６個まで指定できるようにとりあえずしておきました。

カンマ区切りで一括指定する必要性があるので上記ソースで紫色の部分にあるように、カンマを文字列に追加しています。

当然ですが、フォーム上とCCデータベースの項目名の数や名前は完全に一致させておかないといけません。

トリガー設置上の注意点

実は、Googleフォームを改造する上で一番嵌ってしまったのがトリガーの設置。個人レベルで使うのであれば、全く問題にならないが、会社などで使用する場合には注意が必要である。

このトリガーであるが、スクリプト作成者が通常は設置するものだが、この設置者のメールアドレスがトリガーによって作動するスクリプトの実行者になるので、「メールアドレスのFrom」や「自動生成ドキュメントのオーナー」などは全て、トリガー設置者のものになってしまう。自分が作り自分が担当であればコレで問題ないが、作るのは自分だが動かし運用するのは他人となると、非常に面倒。何しろ、設置者のメールアドレスから飛んできてることになっているから。

しかもこのトリガー、自分で設置したトリガー以外は見えないので、誰かがトリガーを設置している場合、共同編集者はそのトリガーの存在に気がつかない。見えないのだから。よって、ダブってトリガーを設置していると２回プログラムが発動することになる。

また、このトリガーであるが、現在のプロジェクトのトリガーと全てのトリガーと２種類あるが、ここでもちょっとした問題。

同じフォルダ内に２つのフォームファイルがあり、どちらも用途は違えど同じようなスクリプトを仕込んである。この時、片方に全てのトリガーにてスクリプトトリガーを仕込む。もう片方も同じようにスクリプトトリガーを仕込むわけだが、この時、前者はScriptMan1()、後者はScriptMan2()としてある。しかし、全てのトリガーにて後者でScriptMan2()を設置すると、前者のフォームのスクリプトトリガーもScriptMan1()に変更されてしまう。バグなのか仕様なのか知らないが、非常に迷惑な仕様になっているので、必ずスクリプトトリガーは個別に現在のプロジェクトのトリガーに新規追加をすること。当たり前だが、このケースの場合、後者はトリガー発動しても前者は発動されなくなる。

ということで、ここでおさらい

自動返信時のFromアドレスやファイル自動生成時のオーナーは、トリガー設置者のメールアカウントのアドレスが適用され、オーナーとなる
他人が設置したスクリプトトリガーは見えない
すべてのプロジェクトのトリガーにてトリガーを設置すると、予期せぬことが起きるので、使わない。

スプレッドシートにデータを書き込む上での注意点とテクニック

回答保存先スプレッドシートに追加情報を書き込む

Googleフォームは回答データをスプレッドシートに蓄積することが出来ますが、それとは別に設置者側でこのデータにさらに情報を加えて、タスク管理的な用途で使うケースは多々あるかと思います。其の場合、当たり前ですが、ユーザにその部分の情報を入れさせるわけにもいかないので、回答先スプレッドシートにカラムを追加していくわけです。このカラムの追加は既存の回答シート内であれば、どこに追加しても問題ありませんが、シート名の変更はしてはいけません。

さて、その追加情報をユーザの送信時に書き込ませるテクニックですが、この方法は、少々問題があり、オススメしたいのは、次項の「別途スプレッドシートに情報を書き込むスクリプト」です。まずは該当部分のソースから。

    Utilities.sleep(20000);

    sheet = SpreadsheetApp.openById("開きたいスプレッドシートのID");

    sheetman = sheet.getSheetByName("スプレッドシートのシート名");

    lastrow = sheetman.getLastRow();

    sheetman.getRange("K" + lastrow).setValue("書き込みデータ１");

    sheetman.getRange("L" + lastrow).setValue("書き込みデータ２");

    sheetman.getRange("M" + lastrow).setValue("書き込みデータ３");

この方法では２箇所問題がある。以下に問題点とフローを列挙する。

わざわざsleepを入れているのは、実は書き込みタイミングによってはフォーム側の入力内容がスプレッドシートに反映する前にスクリプトが読みに行ってしまうケースがあった。そこで、sleepを入れている
また、フォーム側のデータ入力反映は常にスプレッドシートの最終行に追加されることから、getLastRowで位置を取得。これしか方法がない。
そして、追加の書き込みをカラム指定で行わせる
しかし、複数の人間が書き込む場合には、時として、最終行が他人のデータであることが考えられる。そうなるとデータに整合性がなくなったり、他人のデータに上書きされて、自分のデータ行は空っぽということも十分あり得る。

ということで、この方法は曖昧すぎる点と、複数の人間が同時に書き込みをしないという条件がついてしまうため、好ましくない。しかし、追記の場合にはフォームが入れてくる情報がどのタイミングで入り、どの位置に入るのかが分かり得ないため、このようなスクリプトになっている。そこで、これらを解決するにいは次項のやり方でやるほうが手間は掛かるが、面倒がすくなくなる。

あえて回答保存せず、別途スプレッドシートに情報を書き込むスクリプトを作る

この方法は、スプレッドシートに自動保存はせず、スクリプトでフォームデータを拾ってスプレッドシートにデータを書き込ませる方法で、追記させるような方法よりも安全確実で失敗がない。しかし、スクリプトできちんと書き込み処理を行わせるまでを記述しないと行けないので手間は掛かる。しかし、後に発生するであろう面倒事を回避できるので、フォームのスプレッドシートへの保存は使わずこの方法を推奨したい。しかも、この場合、どんなスプレッドシートでもどんなシート名でも問題ないので、管理上も自由度が上がる。

function sendForms(e){

   var FORM_DATA = e.response.getItemResponses();

   var edit_1st = FORM_DATA[0].getResponse();

   var edit_2nd = FORM_DATA[1].getResponse();

   var edit_3rd = FORM_DATA[2].getResponse();

   var sheet = SpreadsheetApp.openById("値を書き込みたいスプレッドシートのID");

   var sheetman = sheet.getSheetByName("値を書き込みたいシートの名前");

   sheetman.appendRow(edit_1st, edit_2nd, edit_3rd);

上記のコードでは、getLastRowを使わず、書き込みにはappendRowのみを利用している。

この方法では、フォームが本来担当しているデータの書き込みを全てスクリプトでやらせるため、追記という面倒な手間がない。その代わり、丁寧にgetResponseで拾ったデータを順番通り入れて上げる必要性は生じる。また、フォーム上でのデータは全て配列で帰ってくるが、上から0で始まる点で注意。２個めの質問はつまり、１となる。

最後に、appendRowだが、このメソッドは確実に最終行にデータを入れてくれる便利なもので、他人のデータを上書きする恐れがない上に、ロックする必要性もない。また、メソッドの引数はそれぞれ１番目から順番にA列、B列、C列といった具合に並んでいるので、それぞれに値を指定してあげれば良い。もちろん、数式を指定することも可能であるので、フォーム以外のデータをついでに設定してしまうことも容易である。

ユニークIDを基に後から別のフォームでの入力内容を回答先スプレッドシートに追記したい

時として、２種類のフォームから同じ回答先スプレッドシートにデータを追記したい場合がある。例えば、１つ目が計画書の送信フォームとした場合、２つ目がその実行結果レポートを送るフォームといった場合である。この時、２つのフォームのデータの殆どは共通しており、違う点といったら、其の際のいくつかの項目（ドライブのファイルへのURLやらそのフォームでだけ利用する項目など ex:実行結果レポート内容など）。しかし、これらをバラバラのスプレッドシートで管理をしていては、効率が悪い。そこで、１つ目のフォームで送信時にユニークなIDを発行し、それを次のフォームで入れてもらうように作れば、２つのフォームでの入力内容は、ユニークIDでもって追記が可能となる。

しかし、この実装は思っているよりもずっと大変である。以下にそれを列挙する。

自動返信メール機能を実装する必要性がある。
ユニークIDの発行を行う為の仕組みが別途必要であり、１つ目のフォーム送信時にユニークIDをスプレッドシートに書き込んでおく必要性がある。
ユニークIDを２つ目のフォームで送信時にスプレッドシート上を検索するような仕組みが必要。
ユニークIDを間違って入力した場合に、エラーメールが飛ぶように実装する必要性がある。これは、フォームでは入力内容のチェックが出来ないため。Validationくらい出来るようにして欲しいものである。
２つ目のフォームで、誤ったIDが入力されて送信されても、スプレッドシートにはその内容が残ってしまうので、それを見つけて削除か、フラグを用意してNG処理させておく必要性がある。
もう既にそのIDで送信済みの場合には、送信済みである旨の処理（通知メールや書き込みデータの削除など）を実装する。

一度実装してしまえば、後は楽なのだが、実装するまでが結構大変な作業である。かなり多くのモジュールを作る必要性があるためである。とても長いものになってしまうので、今回は割愛します。以下に完結にどのような機能として実装したかを記述しておきます。

メインのスクリプトであるonFormSubmit(e)に対して全て記述する。この関数の一番の目的はメール自動返信スクリプトである。
ユニークIDはプロジェクトプロパティにて管理する。一つ送信する度に、１を加算して再びプロジェクトプロパティに格納する。
特定のスプレッドシート上でユニークなIDを基にレコードを抽出する関数を作っておく（DLookup的なもの。実際には、For文でセコセコ作ってるシロモノですが）。
別途フラグとして、エラー検出フラグと送信済みフラグの２つを用意しておく。よって、本メールスクリプトは正常時と合わせて３パターンのメールを送信することになる。
ユニークID他、入力者や入力日など同一の内容を２つ目のフォームで入力させ、１つ目で入れたデータと一致するかをチェックする仕組みを導入し、書き込み間違いの場合には、エラー検出フラグを立てる。
既に同一IDで内容を送信済みの場合には送信済みとして本人にメールが通知されるように装備
誤った内容であっても２つ目のフォームには其の際のデータがスプレッドシートに残る為、ユニークIDを見つけ出してIDをNGに置き換えるルーチンを別途挟んだ。

※ちなみに、この場合も、フォームにデータの入力をさせず、スクリプトで書き込みをさせる場合には、NG置き換えだのといった処理は不要になる。

ポイント

自分の場合、スプレッドシートには全てスクリプトでの読み書きのみとしている。その為、フォーム上にデータが蓄積されていると都合悪いだけでなく、不要なデータが溜まることを防ぎたい。そこで、onOpen()にて、起動時にフォームデータを削除するFormApp.getActiveForm().deleteAllResponses();を入れてある。
メール送信時に、選択した施設名やタイプなどによって、自動的にCCを追加するルーチンを用意しておくと、とても便利である。この場合も別にCC送信先を格納したスプレッドシートを用意しておき、スクリプトから参照させるようにしておく。すると、フォーム送信時に内容が、CC先にも送られるようになって便利。
自分の場合、更に紙の書式を別途作るのが面倒くさいということもあって、スプレッドシートに「名前付き範囲」で指定したセルに、フォームのデータを流し込んで、自動的に印刷用のスプレッドシートを作成するようにしている。また、その作成したファイルへのURLをメール本文に記載するようにもしている。どうやら、ワープロ文書でも同じようなことが出来るらしいが、面倒だったので今回はスプレッドシートを下敷きに作ってる。
Googleフォームが回答先として自動生成するスプレッドシートは旧式のスプレッドシートである。その為、新しいスプレッドシート形式で制限緩和された状態で、さらに新しい関数も使いたいとなると具合が悪い。というか使えない。なので、予め新しいスプレッドシートを用意しておいて指定する方法がある。だが、それやるくらいなら、自分は初めから手動でスクリプトで既存のスプレッドシートに記述するルーチンを書く。これが最強だ。

限界点を超えた場合

正直な所、Googleフォームはどんなに頑張っても柔軟なシステム作成には向いていない。極めて固定的な内容で特定の内容を集計するには向いているが、これで色々文書生成やマネージメントを後ろでやらせたり、ユーザの選択項目や内容をValidationすることも出来ない。となると、結構限界点が低い。ここが、Googleフォームが使えない点といういことで活用されていない理由にもなっていると思われる。自分みたいにかなりトリッキーなテクニックを駆使してまで、限界点を引き上げて対応なんて普通やらない。

では、限界点を超えた場合にはどうしたら良いか？となると、以下の２点が挙げられる。

Googleフォームをカスタマイズしてガジェット化する

Googleフォームの型だけを利用して、XMLガジェットにして使う方法である。メリットデメリットがあるので、正直な所腕がある人じゃないのであれば、あまりオススメできないテクニックである。

ガジェット化するので、CSSやデザインなどは自由度が高くなる。
但し、スクリプトトリガーは使えなくなる。
フォーム送信ボタンを残して於けば、今まで通りスプレッドシートにデータが溜まる。
CSSを用いることはできるが、慎重にカスタマイズしなければいけない。根気のいる作業である。
ガジェットなので、Google Sites以外でも利用しようと思えばできる。

自力でJavaScriptで色々実装出来る人は良いが、そもそもGoogle APIサービスに於いて、Google Docs関係のAPIはJavaScriptでのサポートをしていないので、機能連携を考えると、全く美味しくない。見た目が美しくなるくらい。

XML化についての手順は以下の通り。

ガジェット化したいフォームを一旦ライブフォームで表示する
HTMLソースを全部一旦取得する
テキストエディタで、XMLガジェットの型を用意する。
型の中にそのまま2.で取得したHTMLをはりつける。
あとは、CSSカスタマイズやいらないタグを削除していく。
作成したXMLファイルをどこかにアップロードして、埋め込みたいページに埋め込む

これで完了。コピペ状態だとCSSも参照できなくなって、非常に見た目の悪いものが表示されます。

UiAppを用いてGASガジェット化する

もはや、フォームですらなくなっているが、フォームの機能を実現しつつ自由度高めでいろいろ出来るのが、Google Apps ScriptのUiAppを用いたスクリプトガジェットで作成する方法である。

Google Apps Scriptのみで作成する為、割りと簡単と思いがちだが、このUiAppってものすごく扱いにくいもので、Ui自体もスクリプトで描かなくてはいけないため、実際にはかなり難易度が高いです。但し、フォームのような制約が全くないので、腕があれば自由自在に様々なUiコンポーネントを使って、送信前のチェックやら送信後にやらせることなどが、出来る幅が広がります。但し、欠点がいくつか。

スクリプトであるため、１回はユーザ自身にスクリプト実行の承認をしてもらわないといけない
Google Sites内でしか使用することができない。ウェブアプリケーションとして公開しても同じ。
自由度が高いと言っても、通常のアプリケーションのように自在に画面遷移してといった動作は不可能。
ドキュメントが充実していない為、つくろうと思うとかなり躓くと思われる。