【目次】
- きっかけは集団健康管理
- 何ができるの
- 作り方 1/5 ~Google Form編~
- 作り方 2/5 ~Google Spreadsheet編~
- 作り方 3/5 ~GASコピペ編~
- 作り方 4/5 ~ひたすら承認編~
- 作り方 5/5 ~GASトリガー編~
- 参考
きっかけは集団健康管理
私が所属するサークルでは、コロナやインフルなどの感染症拡大を防ぐために、参加者に毎日の検温を課していたことがあります。
もちろん検温データをとって終わりではなくて、検温データの管理をする必要があります。
管理者は
・データを毎日チェックして、
・体調不良者がいないか確認して、
・検温していない人にLINEで検温を呼びかける
という仕事がありました。
私もその管理者になったことがありますが、毎日何人もの検温データをチェックするのは、かなり骨の折れる仕事でした。
そこでこの業務を簡単にできないかと思い、Google Apps Script を利用し、業務効率化を図りました。
何ができるの？
フォームの作成
ID、名前、LINE名（任意）をスプレッドシートに登録して、「フォームを作成」のボタンを押すと、スプレッドシートの内容を反映したGoogleフォームの作成ができます。
(名前はダミーです。)
メールの送信
Google Form で集めた回答者の検温データに基づいて、毎日夜の11時から0時に次のようなメールが送られます。
つまり毎日
・高熱の人
・微熱の人
・低体温の人
・検温してくれなかった人一覧
がメールで送られるわけです！
もし誰も検温しなかったら次のようなメールが送られます。
体温は半角数字のみで入力してください！
作り方 1/5 ~Google Form編~
検温データを集めるGoogle Formを作りましょう！
- 名前を入力してもらう項目を、1問目に作成。このとき、質問の形式を「プルダウン(Dropdown)」にして、適当な値を入れる
- 体温を聞く項目を、2問目に作成。 質問の形式は「記述式（短文）」にする
- 全ての項目を「必須」にしてデータの漏れがないようにする
- 送信（Send)で、フォームのURLを作成して、専用のライングループなどに流す
作り方 2/5 ~Google Spreadsheet編~
データを貯蓄するスプレッドシートを作成しましょう！
フォームの「回答」のところから「スプレッドシートで表示」を選択し、新しくGoogle Spreadsheet を作成しましょう！
回答を集計するシートについて
- シートの名前を「フォームの回答１」から「temperature」に変更
- D列の列名のところに「nameID」、E列の列名のところに「isToday」を入力
検温する人を記載するシートについて
- 新たなシートを作成し、名前を「member」としてください
- 1列目に「ID」、「氏名」、「LINE名」と列名を記載
- 2列目以降に、検温する人のID、氏名、LINE名（任意）を記載
- [挿入] → [図形描画] でボタンとなる図形を作成
- 「フォームを作成する」ボタンのメニューから[スクリプトを割り当て]を選択し、「makeForm」と入力
- 同様に「メールを送信する」ボタンのメニューから「sendMail」と入力
この時点でボタンを押しても、関数が作成されていないため、うまくいきません。これから関数を作成していきましょう。
作り方 3/5 ~GASコピペ編~
検温データの分析や、メールの送信をするシステムを作りましょう！
・スプレッドシートの「拡張」から「Apps Script」に飛んでください。
・スクリプトエディタに書いてある既存のコードを消して、下のコードを書いていきましょう。
//情報を集約した関数
function getInfo(){
return {
"sheetid":"*********",
"formid":"*********",
"mail":"*********",
"konetsu":37.5,
"binetsu":36.9,
"teitaion":35.5,
}
}
まずこちらは今後使う重要な値をまとめた関数となります。
Googleスプレッドシートの編集ページは以下のようなURLとなっています。
https://docs.google.com/spreadsheets/d/*********/edit?gid=0#gid=0
ここの「*********」の部分が、スプレッドシートのIDとなっています。このIDによって、他のGoogleスプレッドシートと区別されます。
プログラムではこのIDをもとにsheetを識別するので、コピペしてsheetidの部分に貼り付けておきましょう。
同様にGoogleフォームのURLも、
https://docs.google.com/forms/d/*********/edit
のようになっているため、「*********」の部分をformidに貼り付けておきましょう。
また分析結果を送信するメールアドレスをmailの部分に記載し、
高熱、微熱、低体温の基準となる温度をそれぞれ入力してください。
これができたら続いて次のプログラムを書いていきましょう。
//フォームを作成
function makeForm(){
//シートを取得
const sheetid = getInfo()["sheetid"]
const formid = getInfo()["formid"]
const spreadsheet = SpreadsheetApp.openById(sheetid);
const nameSheet = spreadsheet.getSheetByName('member');
// データ取得（A列とB列、2列分をまとめて取得）
const lastRow = nameSheet.getLastRow();
const data = nameSheet.getRange(2, 1, lastRow - 1, 2).getValues(); // 2行目から取得（見出し除外）
const combinedList = [];
for (let i = 0; i < data.length; i++) {
const [val1, val2] = data[i];
// 欠損値チェック（null または 空文字）
if (!val1 || !val2) {
Logger.log(`欠損値が検出されました（行: ${i + 2}, A: "${val1}", B: "${val2}"）`);
return; // 作業停止
}
const combined = `${val1}. ${val2}`;
combinedList.push(combined);
}
// 最後に結果をログ出力
Logger.log(`新しい選択肢：${combinedList}`);
//ドロップダウン形式の質問を取得（最初の1問目）
const form = FormApp.openById(formid);
const items = form.getItems(FormApp.ItemType.LIST);
const listItem = items[0].asListItem()
//新しい選択肢を定義
const newChoices = combinedList
//選択肢を更新
listItem.setChoiceValues(newChoices);
Logger.log("ドロップダウンの選択肢を更新しました");
}
これによって「member」シートの内容を反映したフォームを作成することができます。
保存して実行し、フォームの選択肢が更新されていることを確認しましょう。
これができたら、最後に次を書いていきましょう。
//欠損値があるか確認
function hasMissing(){
//シートを取得;
const sheetid = getInfo()["sheetid"]
const nameSheet = SpreadsheetApp.openById(sheetid).getSheetByName("member");
const lastRow = nameSheet.getLastRow();
const data = nameSheet.getRange(2, 1, lastRow - 1, 2).getValues();
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j++) {
if (data[i][j] === "" || data[i][j] === null) {
Logger.log(`欠損値: 行 ${i+1}, 列 ${j+1}`);
return true;
}
}
}
return false;
}
function getIdDict(nameSheet){
// データ取得（A列とB列、2列分をまとめて取得）
const lastRow = nameSheet.getLastRow();
const data = nameSheet.getRange(2, 1, lastRow - 1, 2).getValues();
// 辞書を作成
const result = {};
for (let i = 0; i < data.length; i++) {
const [id,name] = data[i];
//キーを「(id). (氏名)」の形式にする;
key = `${id}. ${name}`
//値をIDにする;
result[key] = id;
}
Logger.log(result)
return result;
}
function isToday(){
//体温データの読み込み;
const sheetid = getInfo()["sheetid"]
const spreadsheet = SpreadsheetApp.openById(sheetid);
const tempSheet = spreadsheet.getSheetByName('temperature');
const nameSheet = spreadsheet.getSheetByName('member');
//今日の日付を取得し、文字列化;
const today = new Date();
const today_str = String(Utilities.formatDate(today, 'JST', 'yyyy/MM/dd'))
// 変数を設定;
const lastRow = tempSheet.getLastRow()
const dayData = tempSheet.getRange(2, 1, lastRow - 1, 1).getValues(); // 2行目から取得（見出し除外）
const nameData = tempSheet.getRange(2, 2, lastRow - 1, 1).getValues();
const isTodayData = tempSheet.getRange(2, 5, lastRow - 1, 1).getValues();
var nameid = getIdDict(nameSheet)
for(let j=0; j<dayData.length; j++){
//もしすでに0が入力されていたら記入を終了;
if(isTodayData[j] == "0"){
Logger.log(`終了します。（行: ${j+1}）`);
break
}
// 回答日を取得し、文字列化;
const day_str = String(Utilities.formatDate(new Date(dayData[j]), 'JST', 'yyyy/MM/dd'))
const col = j+2 //行指定
const name = nameData[j] //氏名指定
if(day_str == today_str){
//本日の日付と一致している場合、isTodayの列に1を記入する;
tempSheet.getRange(col,5).setValue(1)
//本日の日付のデータにIDを記入;
tempSheet.getRange(col,4).setValue(nameid[name])
} else{
tempSheet.getRange(col,5).setValue(0) //本日の日付と一致しない場合、isTodayの列に0を記入する;
}
}
}
//微熱の体温、高熱の体温、低体温の体温を記入;
function analyzeData(tempSheet,nameSheet){
//高熱の体温を記入;
var konetsu = getInfo()["konetsu"]
//微熱の体温を記入;
var binetsu = getInfo()["binetsu"]
//低体温の体温を記入;
var teitaion = getInfo()["teitaion"]
//欠損値がある場合、終了
if(hasMissing()){return}
//日付を今日のに更新
isToday();
const tempLastRow = tempSheet.getLastRow();
const tempData = tempSheet.getRange(2, 1, tempLastRow - 1, 5).getValues();
const nameLastRow = nameSheet.getLastRow();
const nameList = nameSheet.getRange(2, 1, nameLastRow - 1, 1).getValues().flat();
//isTodayの値が「1」の行だけフィルタ
const filteredTempData = tempData.filter(row => row[4] === 1);
//idリストを抽出
const idList = filteredTempData.map(row => row[3]);
const noKenonIdList = nameList.filter(item => !idList.includes(item));
//高熱の人のidリストを抽出
const konetsuData = filteredTempData.filter(row => row[2] >= konetsu);
const konetsuIdList = konetsuData.map(row => row[3]);
//微熱の人のidリストを抽出
const binetsuData = filteredTempData.filter(row => row[2] >= binetsu && row[2] < konetsu);
const binetsuIdList = binetsuData.map(row => row[3]);
//低体温の人のidリストを抽出
const teitaionData = filteredTempData.filter(row => row[2] <= teitaion);
const teitaionIdList = teitaionData.map(row => row[3]);
Logger.log("分析が終了しました。")
return [noKenonIdList, konetsuIdList, binetsuIdList, teitaionIdList]
}
//IDから氏名（LINE名）を取得する関数
function getDisplayName(nameSheet){
// データ取得（A列とB列、2列分をまとめて取得）
const lastRow = nameSheet.getLastRow();
const data = nameSheet.getRange(2, 1, lastRow - 1, 3).getValues();
// 辞書を作成
const result = {};
for (let i = 0; i < data.length; i++) {
const [id,name,line] = data[i];
result[id] = `${name}(${line})`;
}
Logger.log(result)
return result;
}
function makeContent(){
//データの読み込み
const sheetid = getInfo()["sheetid"]
const spreadsheet = SpreadsheetApp.openById(sheetid);
const tempSheet = spreadsheet.getSheetByName('temperature');
const nameSheet = spreadsheet.getSheetByName('member');
//分析データを読み込み;
const [noKenonIdList, konetsuIdList, binetsuIdList, teitaionIdList] = analyzeData(tempSheet,nameSheet)
//表示する名前のリストを取得;
const displayName = getDisplayName(nameSheet)
//表示するリストを作成
const noKenonList = noKenonIdList.map(key => displayName[key])
const konestuList = konetsuIdList.map(key => displayName[key])
const binetsuList = binetsuIdList.map(key => displayName[key])
const teitaionList = teitaionIdList.map(key => displayName[key])
const noKenonLen = noKenonIdList.length
const memberLen = Object.keys(displayName).length
const [konetsu, binetsu, teitaion] = ["konetsu", "binetsu", "teitaion"].map(key => getInfo()[key])
var content
if(noKenonLen == memberLen){
content = "検温してくれた人はいませんでした。"
} else if(noKenonLen == 0){
content = `高熱(${konetsu}以上)の人↓\n${konestuList}\n\n微熱(${binetsu}以上)の人↓ \n${binetsuList}\n\n低体温(${teitaion}以下)の人↓\n${teitaionList}\n\n全員検温しました。`
} else{
content = `高熱(${konetsu}以上)の人↓\n${konestuList}\n\n微熱(${binetsu}以上)の人↓ \n${binetsuList}\n\n低体温(${teitaion}以下)の人↓\n${teitaionList}\n\n${memberLen}人中${noKenonLen}人検温していません。(${Math.floor((noKenonLen/memberLen)*100)}%)\n↓検温していない人たち\n${noKenonList}`
}
Logger.log("メールの内容を作成しました。")
return content
}
//メールの送信先、微熱の体温、高熱の体温を設定
function sendMail(){
// メールアドレスを記入
const recipient = getInfo()["mail"]
//件名を記入
const subject = '検温結果報告';
//内容を記入
const body = makeContent();
//メールを送信;
GmailApp.sendEmail(recipient,subject,body);
Logger.log(`${recipient}へメールを送信しました。`)
}
これによって、検温結果を分析しメールを送信することができます。
流れとしては以下のようになっています。
-
analyzeData()で以下の分析を実行
- hasMissing()で、memberのシートでIDと名前の欠損値がないことを確認
- isToday()で、本日回答されたデータならば、memberのシートに記載されたIDをnameIDの列に入力し、isTodayの列に1を入力。もし本日回答されたデータでなければ、isTodayの列に0を入力
- isTodayの列が1のデータのみを選択し、検温していない人のIDリスト、高熱の人のIDリスト、微熱の人のIDリスト、低体温の人のIDリストを作成
-
これにもとづいて、makeContent()でメール内容を作成
-
最後にsendMail()でメールを送信
作り方 4/5 ~ひたすら承認編~
実行をすると求められる承認を、ひたすらしていきます。
・左上の「実行」を押すと、「承認が必要です」と出るので、「権限を承認」を選択
・ポップアップが出てきたら、自分のGoogleアカウントを選択
・別のポップアップが出てきたら「許可」を選択
「このアプリはGoogleで許可されてません」...?
・「詳細」を選択してください。（「安全なページに戻る」ではない！！！！）
・さらに「安全ではないページに移動」してください。
・別のポップアップが出てきたら「許可」を選択
怖がらなくても大丈夫です！手順をちゃんと踏めば間違いなく実行できます！！
最後にプログラムを実行して、最初のようなメールがしてしたメールアドレスに届いているか確認してみてください！
作り方 5/5 ~GASトリガー編~
いよいよ最後！夜11時から0時に実行される設定を行いましょう！
・左側にある時計のマークから、[トリガー(Triggers)]へ移動してください。
・右下にある[トリガーを追加(Add Trigger)]を選択してください。
・下のように設定してください！
▶︎ 実行する関数を選択 → sendMail
▶︎ 実行するデプロイを選択 → Head
▶︎ イベントのソースを選択 → 時間主導型
▶︎ 時間ベースのトリガーのタイプを選択 → 日付ベースのタイマー
▶︎ 時刻を選択 → 午後11時〜午前0時
（英語表記は写真参照）
これで完成です！！
お疲れ様でした！！
健康にお気をつけて〜〜〜〜！！！
参考
・【GAS】Google Apps Script からメールを送信する
https://note.com/su3_hokkaido/n/n6213a6dec475
・GASでGoogleフォームの値を取得する（フォームを指定）
https://walking-elephant.blogspot.com/2021/01/gas.formapp.html