今回はお客さまから「日々の体調を報告するためのフォームを部署ごとに作り、ある時間になっても提出がない人に自動的にリマインドメールを送れるようにして欲しい。また、各部署の回答内容を全てまとめた集約シートも作って欲しい」とご依頼いただきました。
実際に作成したツールはこちらです。
ご利用される際には、ファイルをご自身のGoogleドライブにコピーしてください。
「フォームの回答」シートと「全社員リスト」シートの内容を比較してその日の未提出者を割り出し、「未提出者リスト」シートに書き出す関数です。
function unsubmitEmployeesList() {
var today = new Date();
var fourDigitToday = Utilities.formatDate(today, 'Asia/Tokyo', 'yyMMdd');
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var unsubmitEmployeesListSheet = spreadsheet.getSheetByName('未提出者リスト');
var title = Utilities.formatDate(today, 'Asia/Tokyo', 'yyyy年M月d日の未提出者');
var titleRange = unsubmitEmployeesListSheet.getRange('B2');
titleRange.setValue(title);
unsubmitEmployeesListSheet.getRange('B5:D100').clearContent();
var submittedEmployeesListSheet = spreadsheet.getSheetByName('フォームの回答');
var lastRowOfSubmittedEmployeesList = submittedEmployeesListSheet.getLastRow();
var allSubmittedDataRange = submittedEmployeesListSheet.getRange(2, 1, lastRowOfSubmittedEmployeesList - 1, 4);
var allSubmittedData = allSubmittedDataRange.getValues();
var submittedEmployeesIdOfToday = [];
for (var i = 0; i < allSubmittedData.length; i++) {
var submittedDate = Utilities.formatDate(allSubmittedData[i][0], 'Asia/Tokyo', 'yyMMdd');
if (submittedDate === fourDigitToday) {
submittedEmployeesIdOfToday.push(allSubmittedData[i][1]);
}
}
var allEmployeesListSheet = spreadsheet.getSheetByName('全社員リスト');
var allEmployeesDataRange = allEmployeesListSheet.getRange('B5:E100');
var allEmployeesData = allEmployeesDataRange.getValues().filter(v => v[0]);
var unsubmitEmployeesArray = [];
for (var i = 0; i < allEmployeesData.length; i++) {
if (submittedEmployeesIdOfToday.indexOf(allEmployeesData[i][3]) < 0) {
var employeeId = allEmployeesData[i][0];
var employeeFullName = allEmployeesData[i][1];
var employeeEmailAddress = allEmployeesData[i][2];
var unsubmitEmployeeInfo = [employeeId, employeeFullName, employeeEmailAddress];
unsubmitEmployeesArray.push(unsubmitEmployeeInfo);
}
}
for (var r = 0; r < unsubmitEmployeesArray.length; r++) {
for (var c = 0; c < unsubmitEmployeesArray[r].length; c++) {
unsubmitEmployeesListSheet.getRange(5 + r, 2 + c).setValue(unsubmitEmployeesArray[r][c]);
}
}
}「メール設定」シートで入力した内容を元に未提出者にリマインドメールを送信する関数です。
管理者メールアドレスには未提出者の一覧がメールで送信されます。
function sendReminderEmail() {
unsubmitEmployeesList();
var today = new Date();
var fourDigitToday = Utilities.formatDate(today, 'Asia/Tokyo', 'yyMMdd');
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var submittedEmployeesListSheet = spreadsheet.getSheetByName('フォームの回答');
var lastRowOfSubmittedEmployeesList = submittedEmployeesListSheet.getLastRow();
var allSubmittedDataRange = submittedEmployeesListSheet.getRange(2, 1, lastRowOfSubmittedEmployeesList - 1, 4);
var allSubmittedData = allSubmittedDataRange.getValues();
var submittedEmployeesIdOfToday = [];
for (var i = 0; i < allSubmittedData.length; i++) {
var submittedDate = Utilities.formatDate(allSubmittedData[i][0], 'Asia/Tokyo', 'yyMMdd');
if (submittedDate === fourDigitToday) {
submittedEmployeesIdOfToday.push(allSubmittedData[i][1]);
}
}
var allEmployeesListSheet = spreadsheet.getSheetByName('全社員リスト');
var allEmployeesDataRange = allEmployeesListSheet.getRange('B5:E100');
var allEmployeesData = allEmployeesDataRange.getValues().filter(v => v[0]);
var reminderEmailsheet = spreadsheet.getSheetByName('メール設定');
var subjectForEmployees = reminderEmailsheet.getRange('B13').getValue();
var bodyForEmployees = reminderEmailsheet.getRange('B16').getValue();
var unsubmitEmployeesNameArray = [];
for (var i = 0; i < allEmployeesData.length; i++) {
if (submittedEmployeesIdOfToday.indexOf(allEmployeesData[i][3]) < 0) {
var unsubmitEmployeesEmail = allEmployeesData[i][2]
MailApp.sendEmail({
to: unsubmitEmployeesEmail,
subject: subjectForEmployees,
body: bodyForEmployees
});
var unsubmitEmployeesInfo = allEmployeesData[i][0] + '. ' + allEmployeesData[i][1];
unsubmitEmployeesNameArray.push(unsubmitEmployeesInfo);
}
}
var supervisorsEmailAddress = reminderEmailsheet.getRange('B5').getValue();
var subjectForSupervisors = '本日の体調管理フォーム未提出者';
var bodyForSupervisors = '本日の体調管理フォームの未提出者は下記の通りです。\n' + 'リマインドメールはすでに送信されております。\n\n' + unsubmitEmployeesNameArray.join('\n');
MailApp.sendEmail({
to: supervisorsEmailAddress,
subject: subjectForSupervisors,
body: bodyForSupervisors
});
}「全社員リスト」シートの内容を参照して、フォームの「社員番号&氏名」の項目のプルダウンを作成する関数です。
「全社員リスト」シートの内容を更新するたびに「フォーム編集用URL設定」シートの「プルダウン項目の更新」ボタンを押す必要があります。
function djListMaker() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var allEmployeesListSheet = spreadsheet.getSheetByName('全社員リスト');
var allEmployeesDataRange = allEmployeesListSheet.getRange('E5:E100');
var allEmployeesData = allEmployeesDataRange.getValues().filter(v => v[0]);
var listItems = Array.prototype.concat.apply([], allEmployeesData);
var formURLsettingSheet = spreadsheet.getSheetByName('フォーム編集用URL設定');
var formURL = formURLsettingSheet.getRange('B3').getValue();
var form = FormApp.openByUrl(formURL);
var items = form.getItems(FormApp.ItemType.LIST);
items[0].asListItem().setChoiceValues(listItems);
}リマインドメッセージを自動送信するための関数を、「メール設定」シートのB10とD10セルに入力した時間で動作させるためのトリガーを設定する関数です。
function delTrigger() {
var triggers = ScriptApp.getProjectTriggers();
for (var trigger of triggers) {
if (trigger.getHandlerFunction() === 'sendReminderEmail') {
ScriptApp.deleteTrigger(trigger);
}
}
}
function setTrigger() {
delTrigger();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var reminderEmailsheet = spreadsheet.getSheetByName('メール設定');
var triggerHour = reminderEmailsheet.getRange('B10').getValue();
var triggerMinutes = reminderEmailsheet.getRange('D10').getValue();
var time = new Date();
time.setHours(triggerHour);
time.setMinutes(triggerMinutes);
ScriptApp.newTrigger('sendReminderEmail').timeBased().at(time).create();
}「設定」シートに入力した内容を下に、各部署の回答シートと、前部署の集約シートを作成します。
一度作成したシートは回答内容がリアルタイムで反映されます。
function aggregateAnswers() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var urlList = spreadsheet.getSheetByName('設定');
var allAnswersSheet = spreadsheet.getSheetByName('集約');
var templateSheet = spreadsheet.getSheetByName('原本');
//既存のシートを削除するプログラム
var allSheets = spreadsheet.getSheets();
for (var i = 0; i < allSheets.length; i++) {
var sheetName = allSheets[i].getSheetName()
if (sheetName !== '設定' && sheetName !== '集約' && sheetName !== '原本') {
spreadsheet.deleteSheet(allSheets[i]);
}
}
//個別のクラスのシートを作成&集約するプログラム
var lastRowOfUrlList = urlList.getLastRow();
var urlListRange = urlList.getRange(8, 2, lastRowOfUrlList - 7, 1);
var urlData = urlListRange.getValues();
urlData = Array.prototype.concat.apply([], urlData);
var classNameListRange = urlList.getRange(8, 5, lastRowOfUrlList - 7, 1);
var classNameData = classNameListRange.getValues();
classNameData = Array.prototype.concat.apply([], classNameData);
var firstColumn = urlList.getRange('B5').getValue();
var lastColumn = urlList.getRange('D5').getValue();
var formulaArray = [];
for (var i = 0; i < urlData.length; i++) {
var url = urlData[i];
var className = classNameData[i];
var duplicatedSheet = templateSheet.copyTo(spreadsheet);
duplicatedSheet.setName(className);
var formulaBox = '=QUERY(IMPORTRANGE("' + url + '","' + firstColumn + ':' + lastColumn + '"),"SELECT *")';
duplicatedSheet.getRange('A1').setValue(formulaBox);
duplicatedSheet.showSheet();
if (i === 0) {
var partOfFormula = 'QUERY(' + className + '!' + firstColumn + ':' + lastColumn + ', "SELECT * WHERE A IS NOT NULL")';
formulaArray.push(partOfFormula);
} else {
var partOfFormula = 'QUERY(' + className + '!' + firstColumn + '2:' + lastColumn + ', "SELECT * WHERE A IS NOT NULL")';
formulaArray.push(partOfFormula);
}
}
var allAnswersformula = '=QUERY({' + formulaArray.join(';') + '}, "select *")';
allAnswersSheet.getRange('A1').setValue(allAnswersformula);
}