【GAS】4.Googleフォームとスプレッドシートを連携する方法
この記事は全文が無料である。読んだ後に幸せな気分になっていた場合はぜひお金を落としていってほしい。
前回までは非常に基礎的な部分を扱ったり、カスタムメニューの使い方を記すなど、初歩的なところにとどめた。今回からは具体的に使用される頻度の高いGoogleフォームで実際にどのように使うのかを体験していこうと思う。
4-1. Googleフォームを動的に生成する
Googleフォームを自動で生成することは、定期的にフォームを作り替える、または作り直す必要がある場合に有効である。トリガーを用いて完全に時間主導で生成することもできるし、手動のタイミングで生成することもできる。
今後、更なる応用を知りたい人向けに、Googleの公式ドキュメントを掲載する。公式ドキュメントとは、一般的には、モジュールとして用意されている関数を全て掲載しているページを指す。理論上は公式ドキュメントを全て読めばその言語のマスターになれる。
しかし現実はそう甘くない、というか人間はそう頭良くない。実体験を交えながら学んでいくことが重要である。以下にあるのがそのURLである。
これは、クラスの中でもフォームに関するクラスに飛ぶ。
4-1-1. 生成する方法
まずは、FormAppクラスのcreate関数で、フォームを作成する。作成されたフォームは、スプレッドシートを動かしているGoogleアカウントのものになる。すなわち、Googleドライブを開けば見ることができる。しかし、URLはコードの中で取得できるので、その必要はない。
今まで記述したコードは一旦消そう。onOpen.gsは人畜無害なので放置でもよい。
function createForm(){
var form = FormApp.create("テスト");
}
これを実行しようすると、いつも通り権限の承認が必要となる。今までと比べて、「Googleフォームを勝手に作成する」という権限をGoogleに渡している。以前載せた方法で変更することができたら、試しに実行してみても良い。実行するたびにフォームが作成されてしまうのでちょっと注意だ。
これだけだと味気ないので、URLを取得して、スプレッドシートに書き込むことくらいはやっておく。
function createForm(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("シート1"); // 初期状態ならこの名前のはず
var formTitle = "テスト"; // 動的に変更することももちろん可能
var form = FormApp.create(formTitle); // create関数を実行した上で、作成したフォームを格納する
var formId = form.getId(); // あらゆるGoogleのものに一意に付けられたID
var formUrl = form.getPublishedUrl(); // 閲覧用URL
var formEditUrl = form.getEditUrl(); // 編集用URL
// シート1に追加する
sheet.appendRow([formTitle,formId,formUrl,formEditUrl]);
}
少しいじってみた。
基本的にはURLを取得できれば事足りる。IDは現実の世界で使うことはまずないが、Googleフォームに限らずコードの中でIDを使うことがたまにあるので、扱ってみた。スプレッドシートの中で閲覧用と編集用の二つのURLを扱っていけばよいので、Googleドライブから開く必要は基本的にないだろう。
4-1-2. 質問の内容を動的に変更する
上の例は、ある意味では「静的」である。つまり、生成されるフォーム自体は毎回一緒、というか、実はまだフォームを作っただけで内容はいじっていないに等しい。「動的」というのは、入力された内容などによって結果が変わるようなものを指す。少し抽象的な言葉だ。
では、「ユーザーからの入力」をどのように受け付けるか。これが実は本質である。以前にやったように、ポップアップで一つ一つ受け付ける方法も考えられるが、需要を鑑みれば非効率である。
ここでは、シートに書き込むことによりGASに情報を渡そう。
GASはスプレッドシートに書き込まれた内容を好きなだけ読むことができる。そして、ユーザーもスプレッドシートには好きなだけ書き込むことができる。
新しいシートを作成する。せっかくなので、名前もいじっておこう。「フォーム作成」くらいにしておこう。そして、次のように文字を入れてほしい。数字は半角だ。
このような表を作成する。これをユーザーからの入力として、スクリプトに渡すという寸法である。A列は一言一句同じにしてほしい。
そして、図形描画によって実行ボタンを作っておこう。「挿入」→「図形描画」→「適当な図形を作成」→「保存して終了」→右上の点々をクリック
こうすると、にこちゃんマークをクリックするだけでスクリプトを実行できてしまう。間違えてクリックすると実行されてしまうが、ポップアップでフェールセーフを挟めば完璧である。
function createForm(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("シート1"); // 初期状態ならこの名前のはず
var formSheet = ss.getSheetByName("フォーム作成"); // 一番上で定義しておくのがマナー?
var formTitle = "動的フォーム"; // テキトーでよい
var form = FormApp.create(formTitle); // いったん作る。
var formId = form.getId();
var formUrl = form.getPublishedUrl(); // 閲覧用URL
var formEditUrl = form.getEditUrl(); // 編集用URL
// 質問追加。
// データをとりたい範囲を指定(まだデータはgetしない)して、値を配列の配列としてもらう。
var questionsData = formSheet.getRange("A1:C6").getValues();
Logger.log(questionsData); // イメージするために表示してみよう。
var count = addQuestionsToForm(form,questionsData); // 関数としてまとめてしまう。
// countには質問の数が入る。後ほど使う予定。
sheet.appendRow([formTitle,formId,formUrl,formEditUrl,count]);
}
function addQuestionsToForm(form,data) { // 引数は「もらったもの」という感じなので、名前は関数の外と統一する必要はない。
var count = 0;
for (var i=0;i<data.length;i++) { // iを0として定義して、iがdataの長さ(6)以下のとき、iを1ずつ増やしながら
if (i === 0) {
continue; // 最初の行は飛ばそう。
}
var title = data[i][0];
var includeQuestion = data[i][1];
var isRequired = data[i][2];
if (includeQuestion == 1) {
if (title === "氏名") { // 短文記述、制限あり
var item = form.addTextItem();
item.setValidation(FormApp.createTextValidation()
.setHelpText('半角または全角スペースを含めてください。')
.requireTextMatchesPattern(".*[ ]+.*")
.build()
); // スペースがない場合はエラーが出るように。
} else if (title === "年齢") { // 数値
var item = form.addTextItem();
item.setValidation(FormApp.createTextValidation()
.setHelpText('半角数字を入力してください')
.requireNumber()
.build()
); // 半角数字を要求する。
} else if (title === "好きな色") { // 選択
var item = form.addMultipleChoiceItem();
item.setChoiceValues(['赤','青','黄','黒','白']);
} else if (title === "好きな食べ物") { // 短文自由記述
var item = form.addTextItem();
} else if (title === "その他") { // 長文自由記述
var item = form.addParagraphTextItem();
}
item.setTitle(title);
if (isRequired == 1) {
item.setRequired(true);
}
}
}
return count;
}
このほかにも、上で載せたclass Formの公式ドキュメントを見ると、プルダウンであったり、チェックボックスだったり、いろいろと追加できることが分かる。
また、addQuestionsToForm関数の一番上でform.setCollectEmail(true);と記述すると、メールアドレスを収集するようになる。
4-1-3. GASから動的にいじれない設定(回答者にメールでコピーを送信する、など)を保ちたい場合
大体の設定はスクリプトから操作できるようになっている。しかし、回答者にフォームのコピーを送信するという設定だけ、なぜかスクリプトからはできないようになっている。権限の問題なのか、Googleの怠慢なのかは分からないが。
この場合は、フォームの設定の「回答のコピーを回答者に送信」というところで「常に表示」もしくは「リクエストされた時のみ」をあらかじめ選択したフォームを用意しておき、それを毎回コピーしていく形を取ることになる。面倒だ。
function createForm(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("シート1"); // 初期状態ならこの名前のはず
var formSheet = ss.getSheetByName("フォーム作成"); // 一番上で定義しておくのがマナー?
var formTitle = "動的フォーム2"; // 変更
//var form = FormApp.create(formTitle);
var form = copyForm(formTitle);
var formId = form.getId();
var formUrl = form.getPublishedUrl(); // 閲覧用URL
var formEditUrl = form.getEditUrl(); // 編集用URL
// 質問追加。
// データをとりたい範囲を指定(まだデータはgetしない)して、値を配列の配列としてもらう。
var questionsData = formSheet.getRange("A1:C6").getValues();
Logger.log(questionsData); // イメージするために表示してみよう。
var count = addQuestionsToForm(form,questionsData); // 関数としてまとめてしまう。
// countには質問の数が入る。後ほど使う予定。
sheet.appendRow([formTitle,formId,formUrl,formEditUrl,count]);
}
function addQuestionsToForm(form,data) { // 引数は「もらったもの」という感じなので、名前は関数の外と統一する必要はない。
form.setCollectEmail(true);
var count = 0;
for (var i=0;i<data.length;i++) { // iを0として定義して、iがdataの長さ(6)以下のとき、iを1ずつ増やしながら
if (i === 0) {
continue; // 最初の行は飛ばそう。
}
count++;
var title = data[i][0];
var includeQuestion = data[i][1];
var isRequired = data[i][2];
if (includeQuestion == 1) {
if (title === "氏名") { // 短文記述、制限あり
var item = form.addTextItem();
item.setValidation(FormApp.createTextValidation()
.setHelpText('半角または全角スペースを含めてください。')
.requireTextMatchesPattern(".*[ ]+.*")
.build()
); // スペースがない場合はエラーが出るように。
} else if (title === "年齢") { // 数値
var item = form.addTextItem();
item.setValidation(FormApp.createTextValidation()
.setHelpText('半角数字を入力してください')
.requireNumber()
.build()
); // 半角数字を要求する。
} else if (title === "好きな色") { // 選択
var item = form.addMultipleChoiceItem();
item.setChoiceValues(['赤','青','黄','黒','白']);
} else if (title === "好きな食べ物") { // 短文自由記述
var item = form.addTextItem();
} else if (title === "その他") { // 長文自由記述
var item = form.addParagraphTextItem();
}
item.setTitle(title);
if (isRequired == 1) {
item.setRequired(true);
}
}
}
return count;
}
function copyForm(title) {
var originalFormId = ''; // IDをコピペする。
var originalForm = DriveApp.getFileById(originalFormId); // 元のフォームをドライブのファイルとして開く
var newFormFile = originalForm.makeCopy(title); // コピーを作成し、新しいタイトルを設定
var newForm = FormApp.openById(newFormFile.getId());
// それをGoogleフォームとして開く
return newForm;
}
copyFormという新しい関数を用意した。特に、originalFormIdというところは、コピーしたいフォームのIDをコピペする。
まずは、テストで最初に作った「テスト」のフォームを編集用のリンクから開き、設定に行き、下のように設定を変更しよう。
そして、テストのIDをコピペして、
これをやって、実行すると、設定が保持されて、新しいフォームを作成することができる。DriveAppという新しいAppを要求しているので、いつものように権限をGoogleに渡す必要がある。
4-2. スプレッドシートに連携する
作ったフォームはせっかくだから同じスプレッドシートで管理したいものである。用途としては、定期的に作るフォームをまとめることが考えられる。
4-2-1. 回答をリンクするスプレッドシートを指定する
まずは、フォームをスプレッドシートにリンクする方法を書く。Googleフォームを開いた状態で、「回答」のところから「スプレッドシートにリンク」みたいなところを押せば、新しくそれ用のスプレッドシートが作成されるか、もしくは「既存のシートを選択」を選択すれば、指定したスプレッドシートに新しく作ることができる。それをスクリプトで制御する方法を書く。
function createForm(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("シート1"); // 初期状態ならこの名前のはず
var ui = SpreadsheetApp.getUi();
var formSheet = ss.getSheetByName("フォーム作成"); // 一番上で定義しておくのがマナー?
var questionsData = formSheet.getRange("A1:C6").getValues();
var formTitle = questionsData[0][0]; // シートから取得するようにしてみた
if(ui.alert(
'確認',
formTitle + 'という名前のフォームを作成しますがよろしいですか?',
ui.ButtonSet.YES_NO
) === ui.Button.NO)return;
var form = copyForm(formTitle);
var formId = form.getId();
var formUrl = form.getPublishedUrl(); // 閲覧用URL
var formEditUrl = form.getEditUrl(); // 編集用URL
// シートのリンクをこのスプレッドシートに設定する。
form.setDestination(FormApp.DestinationType.SPREADSHEET,ss.getId());
// データをとりたい範囲を指定(まだデータはgetしない)して、値を配列の配列としてもらう。
Logger.log(questionsData); // イメージするために表示してみよう。
var count = addQuestionsToForm(form,questionsData); // 関数としてまとめてしまう。
// countには質問の数が入る。後ほど使う予定。
Logger.log(count);
sheet.appendRow([formTitle,formId,formUrl,formEditUrl,count]);
Utilities.sleep(2000); // ちょっとだけ待ってみる、意味があるかはあまり分からない
SpreadsheetApp.flush(); // リロードもしてみる。意味があるかはあまり分からない
var sheets = ss.getSheets();
var firstSheet = sheets[0]; // リンクされたシートは一番前に挿入される。
firstSheet.getRange(1,count+3,1,4).setValues([["何かしらする列",formId, formEditUrl,count]]);
firstSheet.hideColumns(count+6);
firstSheet.hideColumns(4,count-2);
firstSheet.setName(formTitle);
}
createForm関数を書き換えた。
なんかたまに正常に動作しないことがある。sleepの時間を延ばすなどすると直ることがある。ごめん。
4-2-2. リンクされたシートを削除する関数
ところで、今作ったシートを削除しようとしてみてほしい。
なぜか面倒なことに、自分で接続を切って、そのうえで削除しなければならないらしい。しかも、「フォームのリンクを解除する」というのは同じタブの中にある。更に、これはスクリプトから解除することができない。なんということだ。初心者殺しである。いや、もちろん3つくらいの手間を踏むことを許容するなら全然良いのだが。私はそれすら面倒がるタイプなので、なんとか裏技を見つけた。
まず、全く別のスプレッドシート(「ゴミ箱用スプレッドシート」と呼んでいる)に、リンクを移すことで、間接的にこのシートからリンクを解除する。そのうえで、シートを削除する。という工程を踏めば削除できることを見つけた。
function onOpen() {
var ui = SpreadsheetApp.getUi(); // UIに関するもの
// カスタムメニューを追加
ui.createMenu('追加メニュー') // メニューの中の表示名
.addItem('シートを削除する','deleteSheet')
.addToUi();
}
function deleteSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var sheetName = sheet.getName(); // 条件分岐で用いる
var ui = SpreadsheetApp.getUi();
if (ui.alert(
'確認',
'このシートと、元となるGoogleフォームがゴミ箱に移動されます。(消去はされません。)\nよろしい場合は「OK」を押してください。',
ui.ButtonSet.YES_NO
) == ui.Button.NO) return;
var count = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0].filter(function(cell) {
return typeof(cell) === "number";
})[0]; // 練りに練った方法
var formId = sheet.getRange(1,count+4).getValue();
var form = FormApp.openById(formId);
DriveApp.getFileById(formId).setTrashed(true);
// ゴミ箱用スプレッドシートをあらかじめ用意し、そこに移す。
var trashSsId = "";
// URLの/d/から次の部分↑
form.setDestination(FormApp.DestinationType.SPREADSHEET, trashSsId);
ss.deleteSheet(sheet);
}
onOpen関数をこのように変えた上で、deleteSheet関数を用意する。(前のaaaaa関数などは削除してしまって構わない。)IDがどこか分からない場合はググってくれ。/d/ あ い で ぃ ー /こんな感じになっているはずだ。
そして、削除したいシートを開いたうえで、これを実行すると、削除される。ワンボタンになる気持ちよさはやってみないと分からない。
4-3. Googleフォームの内容に応じてメールの自動送信を行う
組織の運営として必要となる機能が、メールの送信である。メールというのは取り消しが基本的にはできないから、我々のように自動化してみてダメだったらやり直す、というのが効かない厄介な部分である。入念なデバッグと設計が求められる部分だ。
4-3-1. メール送信クラス
統合されていない、というより、わざわざ分化されていて、二つのクラスが存在している。MailAppとGmailAppである。
以降は後者のMailApp関数を扱う。また、スクリプトもこれまでのものとは分けておこう。使い方によってプログラムを分割するのはプログラマーとしての作法である。適当にsendEmail.gsとでもしておこう。
連想配列として、引数を渡せばメールを送信してくれる。例えば、
toに送信先、subjectとhtmlBodyに何か文字を指定して実行するだけでメールが送信されてしまう。簡単に実行されないように注意しなければならない。
function sendEmail() {
MailApp.sendEmail({
to: "", //送信先のメールアドレスを文字列として入れる
subject: "い",
htmlBody: "あ"
});
}
4-3-2. トリガーとの連携
トリガーの重要な機能の一つとして、Googleフォームを受け取ったときに、送信された内容に対応して関数を組むことができる。それを紹介する。まず、Scriptエディタを開き、「トリガー」を開く。そして、「トリガーを追加」し、「実行する関数を選択」で「sendEmail」、「イベントの種類を選択」で「フォーム送信時」を選択する。そして「保存」。これは、フォームの種類を問わない。要は、そのスプレッドシートにリンクされたフォームのすべてが対象である。
また、このトリガーは引数を取る。すなわち、sendEmail関数に引数を渡してくれる。具体的には、プロパティを複数持つオブジェクトを渡す。
↑ここの少し下にスクロールしたところにフォーム送信時のことが書いてある。まずは、sendEmail関数を以下のように書き換える。
function sendEmail(e) {
}
慣習により、このような場合には「e」という一文字を用いることが多い。もともとはエラーメッセージなどを格納するErrorのeだったが、汎用的に使われるようになった。そして、このeは複数の子供要素を持っている。
triggerUidはほとんど使うことはないだろう。rangeとvaluesはよく使うことになる。namedValuesでも多分良い。画像の例ではないが、多分メールアドレスも持っているだろう。
試しに、Logger.logで表示してみる関数も書いておく。実行してみるときは、せっかくだから試しにフォームを送信してみると良い。
function sendEmail(e) {
Logger.log(e.values);
Logger.log(e.range.getSheet().getName()); // シート名 = 今回はフォームのタイトル
}
e.valuesが唯一持っていないのが、フォームのタイトルである。地味に大事なものを持っていない。これを解消するために、e.range(回答を載せた範囲)から逆説的にシートを取得し、無理やり取得する。フォームのシートのタイトルを今回のようにフォームのタイトルと同じに設定していないとこれはできない。多分。
4-3-3. メールの作り方(この場合はエディタになるので、2通り)
大きく分けて二つある。クオーテーションマーク( ' か " )と \n (改行記号)を使う方法か、バッククォート( ` )(多分@のキーをshiftすると打てる)を使う方法。前者は古典的な方法である。後者は最近新しく用意されたやりかたらしく、``ではさむことで改行をエディタ上のものでなく、文字として読んでくれるし、式展開をすることができるから、可読性が高い。ただ、初心者にとって読みやすいかどうかわからないし、古典的なやり方でも十分やりやすいので、お任せする。
次の二つはほぼ同じふるまいをする。
function sendEmail(e) {
// e.values からフォームの回答を取得
var timestamp = e.values[0];
var email = e.values[1];
var name = e.values[2];
var age = e.values[3];
var favoriteColor = e.values[4];
var favoriteFood = e.values[5];
var other = e.values[6];
// メールの件名
var subject = "フォームの回答を受け付けました";
// 古典的な文字列結合を使用してメール本文を自然言語で作成
var htmlBody = "以下の内容でフォームの回答を受け付けました。\n\n" +
"送信日時: " + timestamp + "\n" +
"氏名: " + name + "\n" +
"年齢: " + age + "歳\n" +
"好きな色: " + favoriteColor + "\n" +
"好きな食べ物: " + favoriteFood + "\n" +
"その他: " + other + "\n\n" +
"この度はご協力いただきありがとうございました。";
htmlBody = htmlBody.replace(/\n/g,"<br>");
// MailApp を使用してメールを送信
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: htmlBody // bodyだと、プラットフォームによっては半角76文字で自動改行が入ってしまうため、htmlBodyで指定するのが望ましい。
});
}
function sendEmail(e) {
// e.values からフォームの回答を取得
var timestamp = e.values[0];
var email = e.values[1];
var name = e.values[2];
var age = e.values[3];
var favoriteColor = e.values[4];
var favoriteFood = e.values[5];
var other = e.values[6];
// メールの件名
var subject = "フォームの回答を受け付けました";
// テンプレートリテラルを使用してメール本文を自然言語で作成
var htmlBody = // 改行や空白がそのまま入ってしまうため、イレギュラーだがこのようにインデントすると想定通りのふるまいをしてくれる
`${name} さま
以下の内容でフォームの回答を受け付けました。
送信日時: ${timestamp}
氏名: ${name}
年齢: ${age}歳
好きな色: ${favoriteColor}
好きな食べ物: ${favoriteFood}
その他: ${other}
この度はご協力いただきありがとうございました。`;
htmlBody = htmlBody.replace(/\n/g,"<br>");
// MailApp を使用してメールを送信
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: htmlBody
}); // 他にも様々なプロパティが存在する。
}
htmlBodyにするにあたって、やらなければならないことがある。それは、\nであろうが``内の改行(同じだが)であろうが、HTML内では解釈されないから、代わりに<br>というHTMLタグに直してあげないといけない。今回は簡潔にhtmlBody = htmlBody.replace(/\n/g,"<br>");で済ませた。//の間に挟まれた文字(\n)を、g(全部)、<br>という文字列に変換するということだ。これをCtrl + Sで保存したのち、フォームに回答すると、フォーム自体の機能のほかに、メールで届くはずだ。
今回扱った機能のみであれば正直コピーの送信だけで問題ないが、添付ファイルを送ったり、別の情報を付随させるなど、幅を利かせることもできるから、トリガーを用いるのは有効である。
以上がGoogleフォームとGAS、スプレッドシートを接続する方法である。筆者は月に5,6回のペースで、動的に内容が変わるGoogleフォームを作成する必要があるので、とても効率が上昇した。
しかしながら、自動化することももちろん大事なことだが、人間の手による修正の方が正確であり、かつ速いことだってあることを忘れてはならない。自動化など所詮そんなものである。
最後に、役に立ったという方はぜひお金を落としていってほしい。最近はPayPayという便利なものがあってすごい世の中になってきていることを感じる。私は最近は現金を使う機会がめっきり減ってしまった。
ここから先は
¥ 100
この記事が気に入ったらサポートをしてみませんか?