アンケート調査や申し込み手続きで回答を入力してもらうのに、Googleフォームは非常に有用だ。スプレッドシートに回答を転写してくれるので、入力された値を集計したり、入力値をもとに何かスクリプトを走らせることもできる。スプレッドシートのGASには、わざわざフォームから送信されたとき用のトリガが用意されている。

フォームを利用者の入力インターフェースにして、スプレッドシートのデータを使って条件分岐したいときに、予め一部の回答が入力された状態のフォームに利用者を誘導したい。

実はけっこう簡単にできる。フォームの回答画面はURLの最後がviewformになっているので、この後ろに?で
解答欄のID=予め入力したい回答
をつけてやればよい。
解答欄のIDを確認する簡単な方法は、Chromeでフォームの回答画面を表示(プレビューでいい)して、解答欄のIDを確認したいテキストボックスなどを右クリックする。次いで検証を押すと画面右側にカラムが表示される。ハイライトされている部分からnameを探す。一般のテキストボックスだとname=”entry.数値”となっている。この “entry.数値” が解答欄のIDである。
nameが描いてある場所がわからない場合は、検証のカラムでCtrl + Fを押してentry.を検索することを勧める。inputタグの中に入っているので、<inputで始まるのも目印になる。

(フォームのURL)/viewform?entry.1234=hoge
というアドレスにアクセスすると、解答欄のIDが entry.1234 である場所に予めhogeと書かれた状態で始まる。
複数の解答欄に予め回答を入れておくには&でつないでいけばいい。
(フォームのURL)/viewform?entry.1234=hoge&entry.5678=fuga

アドレスを加工すればフォームの初期値を指定できることを応用すれば、スプレッドシートのHYPERLINK関数を使って、初期値を入れたフォームへのリンクを張れる。
スプレッドシートの文字列を結合するとき&を使うことを思い出せば、A1セルの値がhogeである場合、ほかのセルに以下のように入力すれば、上で述べた例と同じくnameがentry.1234である解答欄に回答hogeを初期値として設定できる。
=HYPERLINK(” (フォームのURL)/viewform?entry.1234= “&A1,”スプレッドシートに表示される文字列”)
さらに、A2セルにfugaという文字列が入っている場合、上で述べた複数の解答欄に予め回答を入れておく場合を次のように表現することができる。
=HYPERLINK(” (フォームのURL)/viewform?entry.1234= “&A1&”&entry.5678=”&A2,”スプレッドシートに表示される文字列”)

参考
GOOGLEフォームの初期値

これもFormのGASで設定できないものだろうか。

Google Apps Scriptはスプレッドシートだけでなく、ドライブ、ドキュメントなどほかのG Suiteでも使える。スプレッドシートにまとめた情報をもとに、ドキュメントで報告書を自動生成することも可能だ。

これまで手作業で作成していた報告書をGASで作成するにあたって、2つ手の込んだことを求められた。ひとつには、セルを結合して少々複雑な形をした表をつくることだった。ふたつには、単なるテキストではなく、上付きや下付き、斜体といった書式設定を伴う形で表に文書を書き込むことだった。

ひとつ目の「セルを結合した表」は少々厄介であった。GASのリファレンスを読んでみても、セルの結合に相当するMethodがないのだ。試しにドキュメントでセルの結合をした表を作ってGASで読み込んでみると、セルを結合してできたセルは、TableCell.getColSpan()の値が1より大きい値になっていた。しかし、setColSpan()というMethodは存在しない。merge()も期待したような動作をしない。ドキュメントではなくHTMLファイルとして出力することはできそうだが、いまはドキュメントを書き換えたいので不適当だ。
結局のところ、あるドキュメントファイルにあらかじめセルを結合した表を用意しておいて、新しいドキュメントにコピーするという手法をとることにした。TableRowのレベルで挿入していけば、下の図のような2行を単位とした繰り返し構造の表がつくれる。

ふたつ目の書式を維持した文書を挿入するのは簡単である。上で挿入した表のセルに対して、転写したい文書のParagraphをappendしてやればよい。

次に示す例では、元になるドキュメントファイルの1行目 (getCHild(0) またはpp[0] )から5行目(getCHild(4)またはpp[4])に書式をいじった文書が入っている。これを段落ごとに読みだしている。また、7行目( getCHild(6))にはテンプレートにしたい表がある。
appendParagraphする文書をどこのドキュメントファイルに置いておくか、スプレッドシートにでもまとめておけば、表の繰り返し単位ごとに適切なドキュメントファイルを読みだして、表の各セルを埋めることができる。
appedTableやappendParagraphをする際には、copy()を付けてやる必要がある。また、 appendParagraphをする際には、 .merge()をつけないと、余分な改行がセル内に残る。

function tryTable(){
  var doc_from = DocumentApp.openById('元になるドキュメントファイルのID'),
  doc_to = DocumentApp.openById('書き込み先になるドキュメントファイルのID'),
  pp = doc_from.getBody().getParagraphs();

  var body = doc_to.getBody();
  //表をつくる
  var table0 = body.appendTable(doc_from.getBody().getChild(6).copy());
  table0.getRow(0).getCell(0).clear().appendParagraph(pp[0].copy()).merge();
  table0.getRow(0).getCell(1).clear().appendParagraph(pp[1].copy()).merge();
  table0.getRow(0).getCell(2).clear().appendParagraph(pp[2].copy()).merge();
  table0.getRow(0).getCell(3).clear().appendParagraph(pp[3].copy()).merge();
  table0.getRow(1).getCell(0).clear().appendParagraph(pp[4].copy()).merge();

  //表のテンプレートを下に伸ばす。例として10回繰り返し。
  for (var i = 0; i < 10; i++) {
    table0.appendTableRow(table0.getRow(0).copy());  
    table0.appendTableRow(table0.getRow(1).copy());
  }
}

参考
【Microsoft Office 英語】日本語/英語の対照表（Excel, Word）

さらに複雑な表を扱う方法を考える。異なるRowのセルを結合したセルではどのようになるだろうか？次の図に2行5列の表のセルを結合した表と、各セルにアクセスする際に使うRowやCellの番地を示した。複雑な形状に結合した表であっても、結合したセルの左上に位置するセルの値が表示されると見なせるようだ。

自分で作成した表の各セルにアクセスする番地を確認したければ、getChild(index)で各セルの値を読み出すのが簡単だろう。BODY>TABLE>TABLE ROW>TABLE CELL>PARAGRAPH>TEXTという順序で各オブジェクトがぶら下がっている。TABLE CELLまで行けばgetText()でセルの中のテキストを表示できる。また、各要素にぶら下がっている要素の数はgetNumChildren()で取得できるので、あとはforループで読みだせばよい。

ところで、セルを結合すると表示されないセルが出てくるが、ファイルから消えているわけではないようだ。BODYに対してgetParagraphs() をすると、上の図で表示されていないTABLE CELL (R1C0やR1C2~4)に対応するはずのParagraphも取得される。
BODYに直接ぶら下がっている要素だけを取り出したいときは、BODYに対して getChild() とgetNumChildren()を使うのがよさそうだ。

Google Apps Script (GAS)を使って、文書ファイルを指定のメールアドレスに送信する。文書ファイルの内容を確認した旨を自動で取得するため、送信されるメールにはGASへのリンクを張っておき、ワンクリックで受領確認の記録が残るようにする。このとき、文書ファイルのファイルID、受信者のアドレス、受領確認日時が記録されるようにする。

STEP1 承認用のリンクを踏んだ時に動くdoGET関数を定義

承認用のリンクをつくるには、まずdoGET関数を含むプロジェクトを定義し、ウェブアプリケーションとして導入する必要がある。ウェブアプリケーションとして導入するには、doGET関数を定義したのちに、スクリプトエディタのメニュー「公開」から「 ウェブアプリケーションとして導入 」を選択する。作業が完了すると、ウェブアプリケーションにアクセスするためのURLが発行される。

下にスクリプトの例を示す。
URLの最後尾にパラメータとして、スプレッドシートの何行目に承認済みの記録を記入するか (8行目 row)と承認者名 (9行目 SV)を入れるようにする。
12行目はスプレッドシートへの書き込みの例である。8行目で求めたrow行のA列からC列に対して、”recipt”という文字列, rowの中身, supervisor中身を記入する。冒頭で述べた、文書ファイルのファイルID、受信者のアドレス、受領確認日時あたりはここを書き換えればいい。
16,17行目で送信するメール本文を作成する。16行目はプレーンテキストの場合、17行目はHTMLテキストの場合である。受信者の環境によって、いずれかが表示される。
19行目でメールを送信する。送信者は、このスクリプトを実行するアカウントである。宛先はrecipient、メールタイトルはsubject、本文は前述のとおりである。
21行目以降は、ウェブアプリケーションとして導入したURLにアクセスした人に表示される画面を示している。
※「ウェブアプリケーションとして導入」したあとにdoGETの中身を変更した場合は、再度 「ウェブアプリケーションとして導入」 を行う。このときProject VersionをNewにしてから「更新」すること。

//ウェブアプリケーションとして導入する
function doGet(e) {
  var recipient = "受信者のメールアドレス",
      SS_id = "情報を記録するスプレッドシートのファイルID",
      SheetName = "情報を記録するスプレッドシートのシート名",
      Sheet = SpreadsheetApp.openById(SS_id).getSheetByName(SheetName),
      data = Sheet.getDataRange().getValues(),
      row = e.parameter.row,
      supervisor = e.parameter.SV;

  //ファイルに書き込み
  Sheet.getRange(row,1,1,3).setValues([["recipt",row, supervisor]]);

  var subject = '回覧通知';
  
  var plaintxt = "上長の承認が得られたので、報告書を回覧します。";
  var html = '<h1>報告書の回覧通知</h1>'+ '<p>上長の承認が得られたので、報告書を回覧します。</p>';
    
  GmailApp.sendEmail(recipient, subject, plaintxt, { htmlBody: html });

  var display = '<p>報告書の承認を受けつけました。N氏に回覧します。ご協力ありがとうございました。</p>';
  return HtmlService.createHtmlOutput(display);
}

STEP2 承認リンクのURLにパラメータを付加してメールで送信する

STEP1で作成したウェブアプリケーションのURLに、スプレッドシートの情報を読みだしてパラメータを付加させる。

下にスクリプトの例を示す。
7行目のsupervisorを承認依頼メールの受信者にする。data[0][2]すなわちシートのC1セルに入っている値を読みだしている。8，9行目も同様にスプレッドシートのB1セル、A1セルの値を読み込んでいる。
11,12行目で、ウェブアプリケーションにアクセスするURLに、パラメータとしてrowとSVを付加している。パラメータSVならdoGET関数でe.parameter.SVとして受け渡される。複数のパラメータは&で結ぶ。※下のスクリプトでは＆amp;となっているが、amp;は除く。
14,15行目はメールに本文を生成している。
17行目は添付したいファイルを呼び出している。ファイルはあらかじめGoogle Driveに入れておく。ファイルIDの調べ方
あとはメールでsupervisorに送る。

//承認依頼のメールを送信する
function myFunction() {
  var SS_id = "情報が記録されているスプレッドシートのファイルID",
      SheetName = "情報が記録されているスプレッドシートのシート名",
      Sheet = SpreadsheetApp.openById(SS_id).getSheetByName(SheetName),
      data = Sheet.getDataRange().getValues(),
      supervisor = data[0][2],
      subject = data[0][1],
      row = data[0][0];
      
  var url = "ウェブアプリケーションのURL(execで終わる)";
      url += '?row=' + row + '&SV=' + supervisor;

  var txt = '承認リンク ' + url;
  var html = '<h1>次のリンクを開いてください</h1>' + '<a href="' + url + '">承認</a>';
 
  var file = DriveApp.getFileById("Google Drive上のファイルのID")
    
    //承認依頼メールを送信する
    GmailApp.sendEmail(supervisor, subject, txt, { htmlBody: html, attachments: file});
}

参考
「Google Apps Script」で社内システムをつくってみた～GASとの出会い編～
GASのワークフローでURLのパラメータで承認・否認を切り替える方法