GASで自動化の第一歩！売上データを効率よく入力する方法【スプシのトリセツ】

/** * 在庫管理シート用 売上情報一括入力ダイアログ */function onOpen() { SpreadsheetApp.getUi() .createMenu('在庫管理2') .addItem('売上情報入力2', 'showSaleInputDialog') .addToUi();}function showSaleInputDialog() { var htmlContent = ` <style> body { margin: 16px; font-family: Arial, sans-serif; } label { display:block; margin-top:8px; font-size:14px; } input { display:block; margin-top:4px; width:88%; height:24px; font-size:14px; padding:2px; } button { margin-left:6px; margin-top:4px; height:28px; font-size:14px; } .btn-area { margin-top:12px; text-align:right; } button.ok { width:70px; } button.cancel { width:90px; } </style> <form id="saleForm"> <label>管理番号:<input name="id"></label> <label>販売日（MM/DD）:<input id="saleDate" name="saleDate"></label><button type="button" onclick="setToday()">今日</button> <label>売却価格:<input name="price" type="number"></label> <label>発送料:</label> <input name="shipping" type="number"> <div class="btn-area"> <button type="button" class="ok" onclick="submitForm()">OK</button> <button type="button" class="cancel" onclick="google.script.host.close()">キャンセル</button> </div> </form> <script> function setToday() { var today = new Date(); var mm = String(today.getMonth() + 1).padStart(2, '0'); var dd = String(today.getDate()).padStart(2, '0'); document.getElementById('saleDate').value = mm + '/' + dd; } function submitForm() { var form = document.getElementById('saleForm'); if (!form.id.value || !form.saleDate.value || !form.price.value || !form.shipping.value) { alert('全ての項目を入力してください'); return; } var data = { id: form.id.value.trim(), saleDate: form.saleDate.value.trim(), price: form.price.value.trim(), shipping: form.shipping.value.trim() }; google.script.run.withSuccessHandler(function(){ google.script.host.close(); SpreadsheetApp.getUi().alert('完了', '売上情報の入力が完了しました。', SpreadsheetApp.getUi().ButtonSet.OK); }).processSale(data); } </script> `; var html = HtmlService.createHtmlOutput(htmlContent) .setWidth(480) .setHeight(400); SpreadsheetApp.getUi().showModalDialog(html, '売上情報一括入力');}function processSale(form) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var id = form.id; var values = sheet.getRange('B3:B' + sheet.getLastRow()).getValues(); var rowIndex = values.findIndex(function(r){ return r[0] === id; }); if (rowIndex < 0) { SpreadsheetApp.getUi().alert('エラー', '管理番号「' + id + '」が見つかりませんでした。', SpreadsheetApp.getUi().ButtonSet.OK); return; } var targetRow = rowIndex + 3; var parts = form.saleDate.split('/'); var year = new Date().getFullYear(); var saleDate = new Date(year, parseInt(parts[0], 10) - 1, parseInt(parts[1], 10)); sheet.getRange(targetRow, 5).setValue(saleDate); sheet.getRange(targetRow, 6).setValue(parseFloat(form.price)); sheet.getRange(targetRow, 8).setValue(parseFloat(form.shipping));}