Skip to content

Instantly share code, notes, and snippets.

@kwgch

kwgch/complete.html

Last active June 1, 2019 02:28
Show Gist options
  • Save kwgch/942595bca5c7668fe3b97c86eed171d9 to your computer and use it in GitHub Desktop.
Save kwgch/942595bca5c7668fe3b97c86eed171d9 to your computer and use it in GitHub Desktop.
Download ZIP
GASで簡易承認ワークフロー ref: https://qiita.com/kwgch/items/e2f666806503af4bb695
Raw
complete.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
処理が完了しました
</body>
</html>
Raw
file0.js
var URL = "https://script.google.com/macros/s/*********************************************/dev";
var sheetName = "フォームの回答";
function sendFormMail(e){
// 追加行
var row = e.range.getRow();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
var name = e.namedValues["氏名"];
var user = getUserBy("name", name);
// 承認者
var authorizer= getUserBy("id", user['authorizer_id']);
var address = authorizer['mail'];
var cols = ["タイムスタンプ","氏名","申請日","休暇取得申請書（自","休暇取得申請書（至","休暇種類","事前連絡","理由"];
var body="";
cols.forEach(function(col){
body += col;
body += ":"
body += e.namedValues[col];
body += "\n";
});
body += "url:"
body += URL;
body += "?row=" + row;
body += "&name=" + encodeURI(authorizer['name']);
MailApp.sendEmail(address,"休暇申請",body);
}
Raw
file1.js
function getUserBy(key, value){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sh = spreadsheet.getSheetByName('ユーザー');
var values = sh.getDataRange().getValues();
var keys = headerKeys(sh);
for (var i = 0; i < values.length; i++) {
var row = values[i];
row = rowToHash(row, keys);
if (row[key] == value) {
return row;
}
}
}
// ヘッダ行を取得
function headerKeys(sh) {
return sh.getRange(1,1,1, sh.getLastColumn()).getValues()[0];
}
//行の情報をオブジェクトに変換
function rowToHash(array, keys) {
var hash = {};
array.forEach(function(value, i) {
hash[keys[i]] = value;
})
return hash;
}
////////////////////
// 追記しました
function getRowData(row){
Logger.log(row);
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
Logger.log(sheet);
var values = sheet.getDataRange().getValues();
Logger.log(values);
var rowData = values[row-1];
var body={};
rowData.forEach(function(col,idx){
body[values[0][idx]] = col;
});
return body;
}
Raw
file3.js
// 承認ページ表示時
function doGet(e) {
//必要な値を画面に持たせておく
var row = e.parameter.row;
var name = e.parameter.name;
var html = HtmlService.createTemplateFromFile("shonin");
html.row = row;
html.name = name;
html.url = URL;
html.method = "get";
return html.evaluate();
}
Raw
file4.js
function doPost(e) {
var shonin = e.parameter.shonin;
var row = e.parameter.row;
var name = e.parameter.name;
// シートに承認を記入
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
var values = sheet.getDataRange().getValues();
var rowData = values[row-1];
var idx = rowData.length;
var status = "";
var timestamp = Utilities.formatDate( new Date(), 'Asia/Tokyo', 'yyyy/MM/dd hh:mm:ss');
var cur = -1;
rowData.some(function(col,i){
if (!col){//空の列を取得
idx = i;
return true;
}
});
idx++;
if (shonin == 1) {
status = "承認";
} else {
status = "却下";
}
sheet.getRange(1, idx).setValue("状態");
sheet.getRange(row, idx).setValue(status);
idx++;
sheet.getRange(1, idx).setValue("承認者");
sheet.getRange(row, idx).setValue(name);
idx++;
sheet.getRange(1, idx).setValue("処理日次");
sheet.getRange(row, idx).setValue(timestamp);
// 承認者をメールに記載するため再取得
values = sheet.getDataRange().getValues();
if (shonin == 1) {
// 次の承認者をさがす
var user = getUserBy("name", name);
Logger.log(user);
var authorizer_id = user['authorizer_id'];
if (authorizer_id) {//上位承認者がいる場合
// 次の承認者にメール送信
var authorizer = getUserBy("id", authorizer_id);
　　　　sendMail(values, row, authorizer, true);
}
} else {
// 申請者にメール送信
var name = sheet.getRange(row, 2).getValue();
var user = getUserBy("name", name);
sendMail(values, row, user, false);
}
var html = HtmlService.createTemplateFromFile("complete");
return html.evaluate();
}
function sendMail(values, row, user, approved){
var rowData = values[row-1];
var body="";
rowData.forEach(function(col,idx){
if (!col) return true;
var key = values[0][idx];
body += key;
body += ":";
if (['申請日','休暇取得申請書（自','休暇取得申請書（至'].indexOf(key) > -1) {
body += Utilities.formatDate( col, 'Asia/Tokyo', 'yyyy年M月d日');
} else if (['処理日時'].indexOf(key) > -1) {
body += Utilities.formatDate( col, 'Asia/Tokyo', 'yyyy年MM月dd日 hh:mm:ss');
} else {
body += col;
}
body += "\n";
});
body += "url:"
body += URL;
body += "?row=" + row;
body += "&name=" + encodeURI(user['name']);
var address = user['mail'];
var title = approved ? "休暇申請" : "休暇申請が却下されました";
MailApp.sendEmail(address, title, body);
}
Raw
shonin.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
table, td, th {
border-collapse:　collapse;
padding: 0px;
border: 1px black solid;
}
td {
margin: 5px;
}
</style>
</head>
<body>
<h1>休暇申請承認</h1>
<? var html = ''; ?>
<? var json = getRowData(row); ?>
<table>
<? for(key in json){ ?>
<? if (!json[key]) break; ?>
<tr>
<td><?= key ?></td>
<? if (['申請日','休暇取得申請（自','休暇取得申請（至'].indexOf(key) > -1) { ?>
<td><?= Utilities.formatDate( json[key], 'Asia/Tokyo', 'yyyy年M月d日'); ?></td>
<? } else { ?>
<td><?= json[key] ?></td>
<? } ?>
</tr>
<? } ?>
</table>
<form action="<?= url ?>" method="post">
<input type="radio" name="shonin" value="1" checked="checked">承認
<input type="radio" name="shonin" value="０">却下
<input type="hidden" name="row" value="<?= row ?>">
<input type="hidden" name="name" value="<?= name ?>">
<input type="submit" value="送信">
</form>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment