【GAS活用システム③-2】GASデジタル伝票で注文受付・販売管理をする～設定編～
Google Apps Script (GAS)を、もっと身近に、日々の暮らしに。
前回より、【GAS活用システム】の第三弾として、「GASデジタル伝票での注文受付・販売管理」を紹介しています。
前回の【GAS活用システム③-1】は、前置きだけでしたが、今回はがっつりGASデジタル伝票の設定を行っていきます。
設定の全体像
前回の最後に紹介した、「GASデジタル伝票」の設定の全体像です。
繰り返しになりますが、GASは結構長めでも、ほぼほぼコピペでいけますよ。それでは、さっそく「GASデジタル伝票」の設定をしていきましょう。
テンプレートをコピーして保存
これまで紹介したGAS活用システム同様、今回もテンプレートを用意しましたので、まずテンプレートのコピーから行います。
Googleアカウントでログイン後、以下をクリックしてください。
メニューバーが表示されない場合は、ツールバーの一番右の v をクリックすれば、メニューが表示されます。
左上メニューより「ファイル」＞「コピーを作成」を選択します（Googleアカウントでログインしていないと選べません）
左上メニューより「ファイル」＞「コピーを作成」を選択します（Googleアカウントでログインしていないと選べません）
ファイル名を変えて、自分のドライブ上にコピーします。フォルダをマイドライブ以外に変更することも可能です。
【品目集計表】シートの編集
テンプレートをコピーして保存したら、次は【品目集計表】シートを編集します。
はじめに、セルのA1のタイトル「〇〇町内会イベント模擬店」を変更しましょう。ここで入力するタイトルは、後で自動生成する、フォームのタイトルやファイル名としてそのまま使用されます。
続けて、品目名と単価を入力していきます。
テンプレートにはサンプルとして７つの品目が設定されています。品目と単価を上書きしてください。扱う品目が７つより多ければ、4行目から売上合計の行の間に行を追加します。また、７つより少なければ、空白行は残さず、必ず行を削除してください。
【品目集計表】シートの設定は、タイトル、品目名と単価の設定、これだけだけです。
この後、GASが、この【品目集計表】シートのデータを元にフォームを自動生成したり、集計するための関数を自動設定したりするため、上記以外の部分に行を追加したり、列を追加したり、空白セルに文字を入力したりはしないでください。
スプレッドシートは自動保存です。後ほど、品目や単価を変更することも可能なので、仮の設定で大丈夫です。先に進みましょう。
GAS(スクリプト)のコピペと実行
テンプレートのスプレッドシートにはスクリプトが含まれていません。以下の手順でプロジェクトを作成し、スクリプトを設定します。
今回はスクリプトを２つのファイルに分けて登録していきます。
１つ目のファイル「コード.gs」には、フォームからデータが送信された時に実行される処理を記述します。
２つ目は「フォーム作成更新.gs」という名前にし、フォームを新規に作成したり、品目や単価を変更した時にフォームを更新したりするスクリプトを登録します。
スクリプトをコピペ
スプレッドシートのメニューバーから、拡張機能 > Apps Script を選択します。
無題のプロジェクトが生成され、Apps Scriptのスクリプトエディタが表示されました。ファイル：コード.gsに、「function myFunction」が表示されています。
「function myFunction ( ) { }」のところに、上書きする形で下記のコードをコピぺします。
function submitForm(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sh = ss.getSheetByName("注文明細");
//合計金額を計算する数式をセットするセルを取得する
const row = e.range.getRow(); //シートに書き込まれたデータの行番号
const lastCol = sh.getLastColumn(); //シートの最後列
const items = sh.getRange(1,2,1,lastCol-4).getValues().flat();
const prices = sh.getRange(2,2,1,lastCol-4).getDisplayValues().flat();
const desc = "備考";
const adjustmentCell = sh.getRange(row, lastCol-2); //調整金額セル
const totalPriceCell = sh.getRange(row, lastCol-1); //合計金額セル
const messageHeader = "\n"+ e.namedValues[items[0]] +"のご注文内容です";
let messageLine = '';
//注文があった品目と数量のみメッセージとして出力(品目の数だけ繰り返し)
for (let i=1; i < items.length; i++) {
let name = items[i], price = prices[i];
if (e.namedValues[name] != ""){ //注文数が入力されている時のみ
messageLine += "\n" + name + ": " +price +"×"+e.namedValues[name]+"個";
}
}
//備考欄に入力があった場合のみメッセージに追加
if (e.namedValues[desc] != ""){ //備考に入力されている時のみ
messageLine += "\n" + desc+":"+e.namedValues[desc];
}
adjustmentCell.setNumberFormat("[$¥-411]#,##0"); //調整金額セルに通貨書式を指定
//合計金額のセルに合計金額を計算する数式をR1C1形式で入力
totalPriceCell.setFormulaR1C1("SUMPRODUCT(R2C3:R2C[-2],RC3:RC[-2])+RC[-1]")
.setNumberFormat("[$¥-411]#,##0"); //通貨書式を指定
const massageTotal = "\n-------------------\n合計金額は "
+ totalPriceCell.getDisplayValue()+ " です！";
console.log(messageHeader + messageLine + massageTotal);
const token = "★トークンをここに設定★"; //トークンを設定
sendLine(messageHeader + messageLine + messageTotal, token);
}
function sendLine(msg,token){
var options = {
"method": "post",
"Content-Type" : "application/x-www-form-urlencoded",
"payload": "message=" + msg,
"headers":{"Authorization" : "Bearer " + token}
};
UrlFetchApp.fetch("https://notify-api.line.me/api/notify",options);
}なかなかの長さですが、とりあえず全部コピペしてください。あとで一ヶ所だけ修正します（★～★にトークンを設定）。
次は「フォーム作成更新.gs」です。ツールバーの「＋」をクリックして、スクリプトファイルを追加します。
名前に「フォーム作成更新」と入力して、下記のスクリプトをコピペします。
function createUpdateForm() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ssId = ss.getId(); //スプレッドシートIDを取得
const sh0Name = "注文明細" //フォームに紐づくシート名を指定
//品目集計表シートの品目と単価の範囲の値を二次元配列menuとして取得
const sh = ss.getSheetByName("品目集計表");
const lastRowA = sh.getRange(4,1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
const lastRowD = sh.getLastRow();
const menu = sh.getRange(4,1,lastRowA-3,2).getDisplayValues();
let OneTo10 = createNumList(10); //1～10の連番リストを生成
let OneTo20 = createNumList(20, "番号:"); //"番号:01"～"番号:20"のリストを生成
if (ss.getFormUrl()) { //フォームが既に連携されている場合
var form = FormApp.openByUrl(ss.getFormUrl()); //連携しているフォームを取得
form.deleteAllResponses(); //フォームの回答をすべて削除する
//フォーム内の全アイテムを一旦すべて削除
const formItems = form.getItems();
for(let i = 0; i < formItems.length; i++){
form.deleteItem(formItems[i]);
}
form.removeDestination(); // スプレッドシートの連携を削除
// 現在の時刻(時:分)を格納
time = Utilities.formatDate(new Date(),'Asia/Tokyo','HH:mm');
//一番左のシートの名前"注文明細"に、bk_時刻(時:分)をつけるように変更
ss.getSheets()[0].setName(sh0Name+"bk_"+time);
SpreadsheetApp.flush();
console.log("フォームの項目・回答を削除し、連携シートをバックアップしました");
}else{ //フォームが連携されていない場合
console.log("フォームを新規に作成します");
const formTitle = sh.getRange("A1").getValue(); //タイトル(A1)の値を取得
var form = FormApp.create(formTitle).setTitle(formTitle); //フォームを作成
//作成したフォームのファイルをマイドライブからスプレッドシートのあるフォルダへ移動する
const sheetFolder = DriveApp.getFileById(ssId).getParents().next();
DriveApp.getFileById(form.getId()).moveTo(sheetFolder);
}
//はじめに番号札アイテムを作成（番号:01～番号:20の選択肢をもつプルダウンで）
form.addListItem().setTitle("番号札").setChoiceValues(OneTo20);
//メニュー品目の数だけアイテム作成（1～10の選択肢をもつプルダウンで）
for(let i = 0; i < menu.length; i++){
form.addListItem().setTitle(menu[i][0])
.setHelpText(menu[i][1]).setChoiceValues(OneTo10);
console.log(i+1+"つ目の項目、"+menu[i][0]+"をフォームに追加しました");
}
//最後に「備考」アイテム作成（記述式で）
form.addParagraphTextItem().setTitle("備考");
//スプレッドIDを指定してフォームと連携
form.setDestination(FormApp.DestinationType.SPREADSHEET, ssId);
//フォーム連携で追加されたシート側の処理
SpreadsheetApp.flush();
let sh0 = ss.getSheets()[0]; //一番左のシート
const lastCol = sh0.getLastColumn(); //この時点の最終列を取得
sh0.setName(sh0Name); //シート名を変更
sh0.insertColumnsBefore(lastCol,2); //最終列の前(左)に2列追加
sh0.getRange(1,lastCol).setValue("調整金額"); //新しい列の見出し設定
sh0.getRange(1,lastCol+1).setValue("合計金額");
sh0.setFrozenRows(2); //最初の2行を固定
sh0.setColumnWidths(2,menu.length+3,120); //2列目から合計金額の列のサイズ(幅)を120に設定
for(let i = 0; i < menu.length; i++){ //単価を2行目にセット
sh0.getRange(2,i+3).setValue(menu[i][1].slice(1)) //先頭の円記号をとって数値としてセット
.setNumberFormat("[$¥-411]#,##0"); //通貨書式を指定
}
//品目集計表シートの集計するための関数を設定
sh.getRange(4,3,menu.length)
.setFormula("SUM(OFFSET("+sh0Name+"!$C$3:$C,0,MATCH(A4,"+sh0Name+"!$C$1:$1,0)-1))");
sh.getRange(4,4,menu.length).setFormulaR1C1("RC[-2]*RC[-1]");
sh.getRange(lastRowD-2, 4).setFormula("SUM(OFFSET("+sh0Name+"!$C$1:$C,0,"+menu.length+"))");
sh.getRange(lastRowD, 4).setFormula("SUM(OFFSET("+sh0Name+"!$C$1:$C,0,"+(menu.length+1)+"))");
}
function createNumList(toNum,char) { //連番リストを作成する関数
let OneToNum = [];
for(let i = 1; i <= toNum; i++){
if (char == undefined){ //2番目のパラメータ(文字)の指定がない場合
OneToNum.push(i); //1から指定された数まで数値の連番を作成
}else{ //文字を前につけて01から指定数までの連番リストを作成
OneToNum.push(char+String(i).padStart(2, "0"));
}
}
return OneToNum;
}超絶、長いですよね。。。でも大丈夫です。これはコピペで一切変更なしでいけます。最初から最後までまるっとコピーしてペーストするだけです。
「無題のプロジェクト」をクリックして、プロジェクト名を変更しておきます。
ツールバーの保存ボタンでプロジェクトを保存します。
フォーム作成更新のスクリプトを実行
保存後、ファイル作成更新.gsをクリックします。
ファイル作成更新.gsの一番はじめに定義されている関数createUpdateFormが、「デバッグ」と「実行ログ」の間に表示されるのを確認したら、「実行」ボタンをクリックします。
「承認が必要です」と言われます。
新規プロジェクトを実行する時に必要な「実行権限の承認」です。以下の手順で承認しましょう。
→ 「権限を確認」をクリック
→ Googleにログイン画面で、自分のアカウントを選択
→ 「詳細」、「[プロジェクト名]（安全ではないページ）に移動」を順番にクリック
→ 「許可」をクリックすることで、実行権限の承認完了
手順が不安な方は、【GAS活用①-3】のページで、画面ショットつきで説明しているので、そちらを参考にしてください。
承認の手順が終わると、createUpdateForm関数が実行されます。実行ログに以下のようなメッセージが表示されることを確認しましょう（いつもより多めに出力しています）。
GAS(スクリプト)の実行後の確認と設定
それでは、createUpdateForm関数の実行により、何が行われたのか、確認をしながら、追加で設定をしていきましょう。
生成されたフォームの確認
まず、createUpdateForm関数が生成されたフォームを確認してみましょう。スプレッドシートを保存しているフォルダを再表示すると、フォームができているはずです。ダブルクリックでフォームを表示します。
ちなみに、スプレッドシートのメニューバーの ツール ＞ フォームを管理 ＞ フォームを編集、と、たどってもフォームを開くことができます。
以下のようなフォームが表示されると思います。
フォームは、以下のようなルールで生成されています。現物を見ながら確認してみましょう。
一番上に、「番号札」のアイテム（質問項目）があり、回答の形式はプルダウンで、選択肢として番号:01～番号:20までの値が設定されます。
続けて【品目集計表】シートで設定した品目の名前が、フォームのアイテムのタイトルに、単価がアイテムの説明に設定されています。回答の形式はプルダウンで1～10の選択肢が設定されます。品目の数だけこの設定が繰り返されます。
最後に、「備考」アイテムが段落形式で設定されます。
「番号札:01～番号札:20」や「1～10」の選択肢を変更したい場合、または、そもそも番号札を使用しない場合の対処方法については次回、説明します。
フォームの編集
今回は基本的にGASにより自動生成されたフォームをそのまま使用しますが、色や画像などの「テーマ」は自由に設定可能です。
フォームの「テーマをカスタマイズ」を選択して、お好みの色やテーマを選びましょう。
ヘッダーの「画像をアップロード」を選択して、「テーマ」の中から画像を選んだり、画像をアップロードすることができます。画像にあわせて、背景などの色も一緒に変更されますが、個別に色を変更することも可能です。
フォームは自動保存なので、自分で保存する必要はありません（保存ボタンがないので明示的に保存することはできません）。
【注文明細】シートの確認と設定
次は、スプレッドシート側に戻り、フォーム作成と同時にGASによって追加された【注文明細】シートを確認してみましょう。
【注文明細】シートはフォームと連携しています。１行目を確認してみましょう。
１列目に「タイムスタンプ」とあり、２列目から「番号札」、その後、品名が続いていきます。最後の品名の次の列に、「調整金額」、「合計金額」と２つ、フォームにない項目があり、最後に「備考」があります。
「調整金額」、「合計金額」の２列は、GASで追加しています。それ以外の列はフォームと連動しています。
２行目には、商品の単価が設定されていると思います。これも自動では表示されないので、GASで設定しています。
上記を確認したら、交互の背景色だけ設定しましょう。5～6行目くらいまでの範囲を選択します。
メニューの 表示形式 ＞ 交互の背景色 で好みの色を選択します。
フォームからデータが追加されると、この背景色を設定された行が間にどんどん挿入されていきます。
【注文明細】シートで設定するのは、この交互の背景色だけです。
【品目集計表】シートの確認
最後に【品目集計表】シートも確認してみましょう。さて、何が変わったのかおわかりでしょうか？？
売上数、調整金額合計などに、0と表示されるようになりましたね。売上数と書かれた一つ下、C4のセルにカーソルをおくと、以下のような関数が設定されているのが確認できると思います。
少し複雑な関数で、関数の達人でもない限り、すぐにピンときませんね。。この複雑な関数の詳細については、また次回、解説したいと思います。ここでは、このセルに何を表示させたいのかだけおさえておきましょう。
上記の例の場合、C4セルは、【注文明細】シートの注文データのうち、"やきそば"の売上数を集計したい、ということですよね。そのために、この複雑な関数をGASで自動的に設定したわけです。まだ注文が入っていないので、0が表示されていて正解です。
さて、以上で、フォームを新規作成した場合に、createUpdateForm関数が行ったことのすべてを確認できました。
追加で設定したのは、フォームのテーマと【注文明細】シートの交互の背景色だけでしたね。他はGASがやってくれました。
動作確認（フォームからテストデータを送信）
まだLINE通知の設定はしていませんが、どこまでできたのか確認する意味で、ちょっとフォームからデータを入力・送信してみましょう。
フォームの編集画面を開いて、プレビューボタンをクリックするか、
もしくは、スプレッドシートから、ツール > フォームを管理 > 実際のフォームを開く を選択します。
適当にデータを入力して送信ボタンをクリックします。
フォームの送信が終了したら、スプレッドシートの【注文明細】シートを確認します。
データが無事に登録されていますね。タイムスタンプには送信日時が自動設定され、フォームで選択した数がきちんと保存されています。
ですが、よく見るとなぜか3行目ではなくて、4行目にデータが設定されていますね。。実はこれ、謎なのです。。
今回はGASで【注文明細】シートの2行目にも単価を設定し、2行目まで固定表示させているのですが、、、なぜかそうすると4行目からデータが設定されちゃうんですね。。
調べても原因がわからず、特に害はないので、ここは気にせずにいきましょう。どうしても気になるようなら、3行目を手動で削除しても問題ありません。
続けて後ろの方の列を確認します。
フォームと連動していない「調整金額」と「合計金額」にはまだ何も値が表示されていないことが確認できます。
【品目集計表】シートも確認してみましょう。
こちらはスプレッドシートの関数が設定済みなので、もう売上数と売上金額、合計金額などは表示されていますね。
フォーム送信されたら合計金額を計算してLINE通知
ここまで来たらもう残りの設定もやっつけちゃいましょう。
トークンの取得
次に、LINE通知のための設定手順に入ります。LINE通知の送り先となる、「トークン」を取得します。トークンは送り先を判別するための43文字程度の、かなり長い英数字となります。
送り先は自分自身でもLINEグループ宛でもどちらでもいけます。
今回は注文データをグループで共有したいのであれば、LINEグループのトークンを、自分一人に通知するのであれば、自分自身のトークンを取得する、ということになります。
LINE Nofifyの公式サイトにご自分のLINEアカウントでログインして、通知を送りたいLINEグループ、または自分自身に通知するためのトークンを取得しましょう。
細かい手順は、【GAS活用術①-2】LINEグループへリマインド通知を自動送信・その２で画面ショットつきで書いてありますので、参考にしてください。
スクリプトにトークンを貼り付け
トークンを無事取得出来たら、GASにトークンを貼り付けましょう。Apps Scriptのスクリプトエディタが表示し、コード.gsをクリックします。
40行目を表示します。★トークンをここに設定★と書いてある行です。
40行目の" "の中、★～★までを、LINE通知の送り先のトークンで上書きしてください。
注：トークンは文字列になるので、両端を "(ダブルクォーテーション)で囲む必要があります。" "で囲まれた中（★～★までの間）を変更して、" "は消さないようにしてください。
トークンの貼りつけが終わったら、これでスクリプトの変更は終わりです。ツールバーの保存アイコンで保存するのを忘れずに。
フォーム送信時のトリガーを設定する
続けて、スクリプトエディタの画面左端のメニューバーで「トリガー」を選択します。
「トリガーを追加」をクリックします。
トリガーを追加の画面で、「実行する関数の選択」でsubmitForm、「イベントのソースを選択」でスプレッドシートから、「イベントの種類を選択」でフォーム送信時を選択します。
保存ボタンをクリックしてフォーム送信時のトリガーを保存します。
トリガーの設定は簡単でしたね。
最後に、自分自身に送る場合は不要ですが、グループ宛に送る場合は、【GAS活用術①-6】のLINEグループにLINE Notifyを招待するの手順が必要になります。こちらも難しい手順ではありませんので、ぱぱっとやってしまいましょう。
２度目の動作確認（LINE通知を確認）
トリガーの設定が終わったら、これでもうフォームからデータが送信される度に合計金額が自動計算されて、表示されるはずです。さっそく、二度目の動作確認として、再びフォームからテストデータを入力します。
さて、どうでしょう？今度は以下のようなLINE通知、届いたでしょうか？
念のため、合計金額が正しいか、計算してみてくださいね。
続けて、スプレッドシートの【注文明細】シートも確認しましょう。
LINE通知と同じ合計金額が、合計金額の列に表示されていると思います。
さて、一通り、設定が終了したところで、だいぶ長くなってしまったので、今回はこの辺で。
次回は、品目や単価を変更する場合の手順や、番号札を使用しない場合の変更方法、また使用した関数などについて解説していきます。
この記事が気に入ったらサポートをしてみませんか？