【GAS】Googleフォームに掲示板と返信メールを追加して署名活動フォームを作る

googleフォームに掲示板と返信メールの機能をGASとスプレッドシートの関数を使って、次のような署名活動フォームを作ってみました。

署名者数の合計や達成状況などをフォームに掲示します。
また、署名に対しての返信メールに達成状況などを掲載します。

このようなフォームを作っていきます。
次の2つの記事からの続きにもなります。

GASで組んだ関数updateBbsとdiyReplyを今回は使います。

応用例その9
おわりに

応用例その9

フォーム

次のような掲示板(タイトルと説明)のあるフォームを作ります。

①タイトル：ここでは、目標

スプレッドシート

フォームとリンクしたスプレッドシートを作ります。

フォームとリンクしたスプレッドシートに、次の2枚のシート(ここでは、シート1とシート2)を追加します。

シート1はGAS(updateBbs)で使うシートです。掲示板に表示したい内容を記します。
必ず、①タイトル(目標)をセルB1に入れてください。
COUNTA関数で回答数(賛同者数)を数えています。
※数式で表示([Ctrl]+@)しています。

シート2はGAS(diyReply)で使うシートです。返信メールに掲載したい内容を記します。
※数式で表示([Ctrl]+@)しています。

コピペ用のシートの内容です。

タイムスタンプ	お名前	住所	メールアドレス

タイトル	目標
掲示内容	="ただいまの賛同者数："&COUNTA('フォームの回答'!A:A)-1&" 目標の達成率："&TEXT((COUNTA('フォームの回答'!A:A)-1)/100,"0%")

質問	タイムスタンプ	お名前	住所	メールアドレス
最新の回答
OFF：メールを送らない
件名	ご署名をありがとうございます
本文	=C2&" 様、現在、【"&(COUNTA('フォームの回答'!A:A)-1)&"名】の方に賛同をいただけました。ありがとうございます。"
TO:	=E2
CC:(省略可)
BCC:(省略可)
NAME:(省略可)
REPLY TO:(省略可)

GAS

3つのGAS、updateBbsとdiyReply、そしてmyFunctionをフォームのスクリプトエディタへ登録します。

次のGASをスクリプトエディタにコピペします。

function updateBbs(SheetNo){
//アクティブフォーム
const form = FormApp.getActiveForm();
//選択肢シート：SheetNoは必要なら変更する(一番左のシートが0、2番目が1、3番目が2、、、)
if(!(SheetNo>1)) SheetNo = 1;
const msgSheet = SpreadsheetApp.openById(form.getDestinationId()).getSheets()[SheetNo];
//シートの質問、説明などを配列で取得する
const question = msgSheet.getRange("B1").getDisplayValue();
const message = msgSheet.getRange("B2").getDisplayValue();
//フォームの質問、説明などの取得
const items = form.getItems();
for (let i = 0; i < items.length; i++) {
//対象の質問を探して見つけたら説明を更新
if (items[i].getTitle() === question) {
items[i].asSectionHeaderItem().setHelpText(message);
break;
}
}
} //Ver1.1.0 for maintenance
//
function diyReply(SheetNo) {
// アクティブなフォームとスプレッドシート
const form = FormApp.getActiveForm();
if (!(SheetNo > 1)) SheetNo = 1; // SheetNoは必要なら変更する（一番左のシートが0、2番目が1、3番目が2…）
const ss = SpreadsheetApp.openById(form.getDestinationId());
// 回答シートとメール用シートを取得
const answerSheet = ss.getSheets()[0];
const mailSheet = ss.getSheets()[SheetNo];
// フォームのすべての回答を取得し、最後の（最新の）タイムスタンプを取得
const formResponses = form.getResponses();
const lastResponse = formResponses[formResponses.length - 1];
const formTimestamp = lastResponse.getTimestamp();
// 回答シートのA列（タイムスタンプ）から、最新のフォーム送信と一致する行を探す
const sheetTimestamp = answerSheet.getRange(2, 1, answerSheet.getLastRow() - 1).getValues();
let targetRow = -1;
for (let i = 0; i < sheetTimestamp.length; i++) {
if (sheetTimestamp[i][0] instanceof Date && sheetTimestamp[i][0].getTime() === formTimestamp.getTime()) {
targetRow = i + 2; // 実際の行番号
break;
}
}
if (targetRow === -1) {
Logger.log("回答が見つかりませんでした。");
return;
}
// 見つけた行の回答内容をメール用シートに転記
const lastCol = answerSheet.getLastColumn(); // 最大列数を取得
mailSheet.getRange(2, 2, 1, lastCol).setValues(answerSheet.getRange(targetRow, 1, 1, lastCol).getDisplayValues());
SpreadsheetApp.flush(); // sync;sync;syncのおまじない（省略可）
// B12セルが"ON"の場合、C12に編集リンクを出力
mailSheet.getRange('C12').setValue(mailSheet.getRange('B12').getValue() === 'ON' ? lastResponse.getEditResponseUrl() : "");
// メール用シートの項目（B3-B11）を配列に取得
const mailTbl = mailSheet.getRange('B3:B11').getDisplayValues().map(elm => elm[0]);
// FROM:（B11）が未設定ならアクティブユーザーのメールアドレスを使用
if (!mailTbl[8]) mailTbl[8] = Session.getActiveUser().getEmail();
// "OFF"でなければメール送信
if (mailTbl[0] !== 'OFF') {
GmailApp.sendEmail(mailTbl[3],mailTbl[1],mailTbl[2],{from:mailTbl[8],cc:mailTbl[4],bcc:mailTbl[5],name:mailTbl[6],replyTo:mailTbl[7]});
}
} //Ver4.0.0 for maintenance
//
function myFunction() {
updateBbs(1);
diyReply(2);
}