haraheterのブログ

企業Googleワークスペースで使えるのか実験

GASおじさんから拝借

//GAS

function doGet(e) {

const items = getAllRecords('商品');

const template = HtmlService.createTemplateFromFile('form');

template.deployURL = ScriptApp.getService().getUrl();

template.formHTML = getFormHTML(e, items);

const htmlOutput = template.evaluate();

htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');

htmlOutput.setTitle('商品発注フォーム');

return htmlOutput;

}

function doPost(e) {

const items = getAllRecords('商品');

// 注文数が0の場合、alert付きでform.htmlを返す

if(isZero(e, items)) {

const template = HtmlService.createTemplateFromFile('form');

const alert = '少なくとも1個以上注文してください。';

template.deployURL = ScriptApp.getService().getUrl();

template.formHTML = getFormHTML(e, items, alert);

const htmlOutput = template.evaluate();

htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');

htmlOutput.setTitle('商品発注フォーム');

return htmlOutput;

}

// 注文数がマイナスの場合、alert付きでform.htmlを返す

if(isNegative(e, items)) {

const template = HtmlService.createTemplateFromFile('form');

const alert = 'マイナス値の入力があります。再度入力してください。';

template.deployURL = ScriptApp.getService().getUrl();

template.formHTML = getFormHTML(e, items, alert);

const htmlOutput = template.evaluate();

htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');

htmlOutput.setTitle('商品発注フォーム');

return htmlOutput;

}

// 商品が在庫切れの場合、alert付きでform.htmlを返す

if(outOfStock(e, items)) {

const template = HtmlService.createTemplateFromFile('form');

const alert = '入力中に在庫切れとなりました。再度入力してください。';

template.deployURL = ScriptApp.getService().getUrl();

template.formHTML = getFormHTML(e, items, alert);

const htmlOutput = template.evaluate();

htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');

htmlOutput.setTitle('商品発注フォーム');

return htmlOutput;

}

// 上記3条件をクリアして「確認画面へ」ボタンが押されたらconfirm.htmlを返す

if(e.parameter.confirm) {

const template = HtmlService.createTemplateFromFile('confirm');

template.deployURL = ScriptApp.getService().getUrl();

template.confirmHTML = getConfirmHTML(e, items);

const htmlOutput = template.evaluate();

htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');

htmlOutput.setTitle('確認画面');

return htmlOutput;

}

// 確認画面で「修正する」ボタンが押されたらform.htmlを返す

if(e.parameter.modify) {

const template = HtmlService.createTemplateFromFile('form');

template.deployURL = ScriptApp.getService().getUrl();

template.formHTML = getFormHTML(e, items);

const htmlOutput = template.evaluate();

htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');

htmlOutput.setTitle('商品発注フォーム');

return htmlOutput;

}

// 確認画面で「発注する」ボタンが押されたらcomplete画面へ

if(e.parameter.submit) {

createOrder(e, items);

updateZaiko(e, items);

sendMail(e, items);

const template = HtmlService.createTemplateFromFile('complete');

template.deployURL = ScriptApp.getService().getUrl();

const htmlOutput = template.evaluate();

htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');

htmlOutput.setTitle('発注完了');

return htmlOutput;

}

function getAllRecords(sheetName) {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const values = sheet.getDataRange().getValues();

const labels = values.shift();

const records = [];

for(const value of values) {

const record = {};

labels.forEach((label, index) => {

record[label] = value[index];

});

records.push(record);

}

return records;

}

function createOrder(e, items) {

// 注文テーブルに単一レコードを追加する

const orderId = generateId();

const order = [orderId, new Date(), e.parameter.email, e.parameter.username];

addRecords('注文', [order]);

// 注文_商品テーブルに複数レコードを追加する

const records = [];

for(const item of items) {

const itemId = item['商品ID'];

const count = Number(e.parameter[itemId]);

if(count > 0) {

const record = [generateId(), orderId, itemId, count];

records.push(record);

}

addRecords('注文_商品', records);

}

function addRecords(sheetName, records) {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const lastRow = sheet.getLastRow();

sheet.getRange(lastRow+1, 1, records.length, records[0].length).setValues(records);

}

function updateZaiko(e, items) {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName('商品');

for(const item of items) {

const itemId = item['商品ID'];

const zaiko = item['在庫数'];

const count = Number(e.parameter[itemId]);

if(count && count > 0) {

const targetRow = sheet.getRange('A:A').createTextFinder(itemId).matchEntireCell(true).findNext().getRow();

const targetCol = sheet.getRange('1:1').createTextFinder('在庫数').matchEntireCell(true).findNext().getColumn();

sheet.getRange(targetRow, targetCol).setValue(zaiko - count);

}

function sendMail(e, items) {

const subject = `ご注文ありがとうございます。`;

let html = `

<thead>

<tr>

</tr>

</thead>

<tbody>

let total = 0;

for(const item of items) {

const itemId = item['商品ID'];

const itemName = item['商品名'];

const unitPrice = item['単価'];

const count = Number(e.parameter[itemId]);

if(count > 0) {

const price = unitPrice * count;

total += price;

html += `<tr>`;

html += `<td class="text-left">${itemName}</td>`;

html += `<td class="text-right">@¥${unitPrice.toLocaleString()}</td>`;

html += `<td class="text-right">${count}</td>`;

html += `<td class="text-right">¥${price.toLocaleString()}</td>`;

html += `</tr>`;

}

html += `<tr>`;

html += `<td colspan="2" class="sum text-right">合計:</td>`;

html += `<td colspan="2" class="sum text-right">¥${total.toLocaleString()}</td>`;

html += `</tr>`;

html += `</tbody>`;

html += `</table>`;

const template = HtmlService.createTemplateFromFile('mail');

template.username = e.parameter.username;

template.orderTable = html;

const htmlBody = template.evaluate().getContent();

const options = {

'name': '発注システム',

'bcc': Session.getEffectiveUser().getEmail(),

'htmlBody': htmlBody,

};

GmailApp.sendEmail(e.parameter.email, subject, 'plain body', options);

}

function generateId(length=8) {

const [alphabets, numbers] = ['abcdefghijklmnopqrstuvwxyz', '0123456789'];

const string = alphabets + numbers;

let id = alphabets.charAt(Math.floor(Math.random() * alphabets.length));

for(let i = 0; i < length-1; i++) {

id += string.charAt(Math.floor(Math.random() * string.length));

}

return id;

}

function getFormHTML(e, items, alert='') {

const email = e.parameter.email ? e.parameter.email : '';

const username = e.parameter.username ? e.parameter.username : '';

let html = `

<label for="email" class="form-label">Email</label>

</div>

</div>

<p class="mb-3">商品の個数を入力してください。</p>

<p class="text-danger">${alert}</p>

<thead>

<tr>

</tr>

</thead>

<tbody>

for(const item of items) {

const itemId = item['商品ID'];

const itemName = item['商品名'];

const unitPrice = item['単価'];

const zaiko = item['在庫数'];

if(zaiko > 0) {

html += `<tr>`;

html += `<td>${itemName}</td>`;

html += `<td>@¥${unitPrice.toLocaleString()}</td>`;

html += `<td>`;

html += `<select class="form-select" name="${itemId}">`;

for(let i = 0; i <= zaiko; i++) {

if(i == Number(e.parameter[itemId])) {

html += `<option value="${i}" selected>${i}</option>`;

} else {

html += `<option value="${i}">${i}</option>`;

}

html += `</select>`;

html += `</td>`;

html += `</tr>`;

}

html += `</tbody>`;

html += `</table>`;

return html;

}

function getConfirmHTML(e, items) {

let html = `

<label for="email" class="form-label">Email</label>

</div>

</div>

<p class="mb-3 fw-bold">以下の内容で発注していいですか？</p>

<thead>

<tr>

</tr>

</thead>

<tbody>

let total = 0;

for(const item of items) {

const itemId = item['商品ID'];

const itemName = item['商品名'];

const unitPrice = item['単価'];

const count = Number(e.parameter[itemId]);

if(count > 0) {

const price = unitPrice * count;

total += price;

html += `<tr>`;

html += `<td class="text-start">${itemName}</td>`;

html += `<td class="text-end">@¥${unitPrice.toLocaleString()}</td>`;

html += `<td class="text-end">`;

html += `<div class="d-flex justify-content-end">`;

html += `<input type="number" style="max-width: 100px; min-width: 60px;" class="form-control text-end" name="${itemId}" required value="${count}" readonly>`;

html += `</div>`;

html += `</td>`;

html += `<td class="text-end">¥${price.toLocaleString()}</td>`;

html += `</tr>`;

}

html += `<tr>`;

html += `<td class="text-end fs-2" colspan="2">合計:</td>`;

html += `<td class="text-end fs-2" colspan="2">¥${total.toLocaleString()}</td>`;

html += `</tr>`;

html += `</tbody>`;

html += `</table>`;

return html;

}

function isZero(e, items) {

let total = 0;

for(const item of items) {

const itemId = item['商品ID'];

const count = Number(e.parameter[itemId]);

if(count) total += count;

}

if(total == 0) return true;

return false;

}

function isNegative(e, items) {

for(const item of items) {

const itemId = item['商品ID'];

const count = Number(e.parameter[itemId]);

if(count && count < 0) return true;

}

return false;

}

function outOfStock(e, items) {

for(const item of items) {

const itemId = item['商品ID'];

const zaiko = item['在庫数'];

const count = Number(e.parameter[itemId]);

if(zaiko < count) {

return true;

}

return false;

}

//confirm.html

<!DOCTYPE html>

<html>

<head>

<?!= HtmlService.createHtmlOutputFromFile('css').getContent(); ?>

</head>

<body>

<? output._ = confirmHTML ?>

</form>

</div>

</body>

</html>

<!DOCTYPE html>

<html>

<head>

<?!= HtmlService.createHtmlOutputFromFile('css').getContent(); ?>

</head>

<body>

<? output._ = confirmHTML ?>

</form>

</div>

</body>

</html>

<!DOCTYPE html>

<html>

<head>

<?!= HtmlService.createHtmlOutputFromFile('css').getContent(); ?>

</head>

<body>

<? output._ = confirmHTML ?>

</form>

</div>

</body>

</html>

//css.html

<style>

.container{

max-width: 600px;

}

</style>

//foam.html

<!DOCTYPE html>

<html>

<head>

<?!= HtmlService.createHtmlOutputFromFile('css').getContent(); ?>

</head>

<body>

<h2 class="text-center m-4">発注フォーム</h2>

<? output._ = formHTML ?>

<button type="submit" class="btn btn-outline-primary" name="confirm" value="true">確認画面へ</button>

</form>

</div>

</body>

</html>

//mail.html

<!DOCTYPE html>

<html>

<head>

<?!= HtmlService.createHtmlOutputFromFile('mail.css').getContent(); ?>

</head>

<body>

<p>このたびはご注文ありがとうございます。</p>

<p>以下の内容でご注文承りました。</p>

<? output._ = orderTable ?>

<p>どうぞよろしくお願いいたします。</p>

</div>

</body>

</html>

//mail.css.html

<style>

table{

border-collapse: collapse;

}

th{

min-width: 60px;

padding: 8px;

border-bottom: 2px black solid;

}

td{

padding: 8px;

border-bottom: 1px #dfdfdf solid;

}

.sum{

border-top: 2px black double;

border-bottom: 2px black solid;

font-size: 20px;

}

.text-left{

text-align: left;

}

.text-right{

text-align: right;

}

</style>