見出し画像

在庫確認できる「あるかな書店」のつくり方 by Googleフォーム

※有料ですが全文読めます。読後、参考になるようでしたらサポートしていただけると大変喜びます。

きょう欲しい本があるとき、いつもの本屋の本棚を、気軽に在庫確認できたらいいなぁと思い、Googleフォームなどで簡易に在庫確認できる「あるかな書店」を作りました。今回はそのつくり方を共有します。

レシピ Googleのアプリのみ、メールで在庫確認

使用するアプリは

Googleフォーム
Googleスプレッドシート
Google app script
Gmail

です。

機能は

Googleフォームで、在庫入力し、
Googleスプレッドシートで入力情報を受け取り、
Google app scriptで適宜情報処理して、
Gmailで在庫情報を自動返信する

です。

Googleフォームをつくろう

まず、Googleアカウントを取得している前提ですが、
Googleフォームのサイトに行きます。
https://www.google.com/intl/ja_jp/forms/about/

フォーム作成のページに行ったら
下記のように、タイトル、フォーム説明文、メールアドレス、作品タイトルを作成します。

画像1

メールアドレスの作成は下記のように上部メニューの歯車アイコンをクリックすると、モーダルでメールアドレスのチェックボックスがあるので、チェックします。

画像2

これで、Googleフォームの項目は完成です。次に入力情報をまとめるGoogleスプレッドシートを作成します。下記のように回答ボタンを押すと、Googleスプレッドシートのアイコンがあるので、クリックするとGoogleスプレッドシートが作成されます。入力情報はここに保存されます。

画像3

スプレッドシートはこんな感じになります。

画像4

赤枠、シート「フォームの回答1」が出来上がります。
青枠、シート「uketori」「zaiko」を新規作成します。(最左の+ボタンを押すと新規作成できます)

GoogleスプレッドシートをGoogle app scriptで加工しよう

Googleスプレッドシートは3つのシートで構成されています。

「フォームの回答1」
「uketori」
「zaiko」

「フォームの回答1」はGoogleフォームの入力情報が保存されます。新しい情報はシート内に新規の行を挿入する形で追加されます。こちらはマスター情報なので、何もいじらず、別シート「uketori」「zaiko」で適宜情報処理します。

シート「uketori」は「フォームの回答1」の情報を転記し、処理などで使うためのシートです。

画像5

中身は上記のように、「フォームの回答1」を転記してるだけです。転記させるときに、Google app scriptでプログラミングで転記処理させています。

別シートからの転記なら、スプレッドシートの関数やセル内に=A2など参照させればと良いのでは?という案もありますが、マスターである「フォームの回答1」は情報が新規挿入されるため、参照がズレてしまい、転記処理が難しいなと思い、Google app scriptで対応しております。Importrange関数などならできるかもです。

転記から送信処理_Google app script

Google app scriptは上部メニュー「ツール」、「スクリプトエディタ」から作成できます。

画像6

すると、下記のようなページが新規作成されます。

画像7

右下のコードが書かれている部分に、適宜コードを書いていきます。

下記は、一連の流れをすべて実行するコードの全体像です。コメントアウトに説明を記載しています。コピペでも対応できると思います。

function autoreply() {
 
 //自動返信メールの件名
  
 var title = "お問い合わせありがとうございます。"; 
  
 //自動返信メールの本文 \nは改行。 
 var body
 = "お問い合わせありがとうございます。\n"
 +"下記のとおりお問い合わせを受け付けました。\n\n"
 + "----------\n";

 //自動返信メールの本文2 本文1と本文2の間に入力内容が入る 
 var body2
 = "----------\n\n"
 + "確認した結果、在庫状況(候補書籍)をお伝えします。\n\n";
  
 //自動返信メールの本文3 在庫状況のあとに挿入する 
 var body3
 = "\n----------\n\n"
 + "上記、在庫状況となります。お取り置きご希望の場合は当メールにてご返信くださいませ。\n\n"
 + "あるかな書店 店主\n\n";
 

//後の処理で使うため、変数を設定。(フォームと名称を一致させる必要があります)  
 var name = '作品タイトル';
 var mail = 'メールアドレス';
 var address = "";
 var zaiko_serach = "";
 var zaiko_result = "";
 var body_zaiko_value = "----------\n\n"

 //シート「お問い合わせフォーム」
 var SS = SpreadsheetApp.openById('ここにスプレッドシートのIDを入力'); 
 var sheet = SpreadsheetApp.getActiveSheet();
 var row = sheet.getLastRow();
 var column = sheet.getLastColumn();
 var range = sheet.getDataRange();
  
 //シート「uketori」 
 var sheet_uketori = SS.getSheetByName('uketori'); 
 var row_uketori = sheet_uketori.getLastRow();
 var column_uketori = sheet_uketori.getLastColumn();
 var range_uketori = sheet_uketori.getDataRange();
  
 //シート「zaiko」 
 var sheet_zaiko = SS.getSheetByName('zaiko'); 
 var row_zaiko = sheet_zaiko.getLastRow();
 var column_zaiko = sheet_zaiko.getLastColumn();
 var range_zaiko = sheet_zaiko.getDataRange();
 var columnBVals = "";
 var LastRow = "";

  
 for (var i = 1; i <= column; i++ ) {
 
 //スプレッドシートの入力項目名(1行目)
   var item = range.getCell(1, i).getValue(); 
   
 //スプレッドシートの入力値(フォーム入力された値)
   var value = range.getCell(row, i).getValue(); 
 
 //シート「uketori」に転記
 sheet_uketori.getRange(row_uketori+1, i).setValue(value); 
  
 //本文(body)に、フォームの入力項目を追加  
   body += "■ "+item+"\n";
 
 //本文にフォームの入力内容を追加
   body += value + "\n\n";
 
 //フォームの入力項目が「作品タイトル」の場合は、変数nameに入れる
   if ( item === name ) {
     name = value;
   }
 //フォームの入力項目が「メールアドレス」の場合は、変数addressに入れる
   if ( item === mail ) {
     address = value;
   }  
 }
 
 //シート「zaiko」のA2に作品タイトルの値を入力
 range_zaiko.getCell(2, 1).setValue(name);
 
 //シート「zaiko」のB列の値の数を取得する
 columnBVals = sheet_zaiko.getRange('B:B').getValues(); // B列の値を配列で取得
 LastRow = columnBVals.filter(String).length;  //空白を除き、配列の数を取得
 
  
 //for文で在庫候補を取得する
   for (var i = 2; i <= LastRow; i++ ) {
 
 //在庫候補をメール本文テキストの成形する
   var zaiko_results  = range_zaiko.getCell(i, 2).getValue(); 
   
     body_zaiko_value += "■"+zaiko_results+"\n";
     
 }
  

//本文1に本文2、在庫候補を追加
  
  body += body2;
  body += body_zaiko_value;
  body += body3;

 
//宛名=address、件名=title、本文=bodyで、メールを送る
 GmailApp.sendEmail(address,title,body,
                    {
    from:'ここにメールアドレスを入力',name:'あるかな書店店主'
  }
                   );
}
  

上記、Google app scriptの処理は

シート「フォームの回答1」の情報をシート「uketori」に転記

「uketori」のB列「作品タイトル」の値を、シート「zaiko」のセルA2に転記

「zaiko」のセルA2の情報を元に、C列にある在庫情報から、部分一致する値をB列に表示(この処理はスプレッドシート関数のQuery関数で処理)

「zaiko」のB列に表示された在庫情報をメール本文に追記して、送信する

といった感じです。

※スプレッドシートIDは、スプレッドシートのURLの下記の部分です。

画像8

上記コードを記載したら、このGoogle app scriptをいつ実行するかを決めます。なにをきっかけにするか?つまりトリガーはなにか?決めます。

画像9

上部メニュー「編集」の「現在のプロジェクトのトリガー」を選択します。

新規ページで下記のようなページが立ち上がります。

画像10

赤枠を押すと、トリガーの設定ができます。

画像11

「イベントの種類を選択」で「フォーム送信時」を選択して保存します。

Gmailで自動返信する場合は、アカウントの許可が必要です。下記のような注意画面が表示されます。

画像12

赤枠を押します。(自身で作ったので安全ですので安心してください。)

画像13

自身のアカウントでGoogle app scriptでGmailを使用するのを「許可」します。「許可」を押して完了です。

これでGoogleフォームから新規送信された場合にGoogle app scriptが発動して、自動返信までされます。

シート「zaiko」について

上記のGoogle app scriptの処理で触れたシート「zaiko」ですが、下記のようになってます。

画像14

列AのセルA2に、「確認ワード」として、フォーム入力の作品タイトルが転記されます。
列Bの「タイトル部分一致ヒット」のB2以降に、列Cの「書籍名」の在庫情報から「確認ワード」と部分一致している書籍を表示させています。

上記画像の例ですと、

作品タイトル「ひと」という情報に対し、在庫情報で「ひと」が含まれている書籍名2作品をB列に表示させています。この2作品が自動返信されます。

検索するときに役立つQuery関数

シート「zaiko」はC列の在庫情報を、AB列で検索ワード、検索結果を表示させるシートです。

B列に検索結果を表示させるとに役立つスプレッドシート関数がQuery関数です。この関数は、あるデータ郡から検索したい値に一致する情報を抽出することができます。詳しくはこの記事が分かりやすいですが、実際書いてる関数は、

=if(len(A2)>1,IF(isna(if(len(A2)>1,Query(C:C,"Where C Like '%"& A2 &"%'"),Query(C:C,"Where C Like '"& A2 &"%' "))),"在庫はございませんでした。",if(len(A2)>1,Query(C:C,"Where C Like '%"& A2 &"%'"),Query(C:C,"Where C Like '"& A2 &"%' "))),"在庫はございませんでした。")

です。。
エラー時の処理などで、if文をif文で括ったりと、自分も書いてて若干分からなくなってきたのですが、つまりは、「A2の文字がC列にあれば、B列に抽出列挙してね」といった感じです。


以上です。

実装する場合の不備不明などございましたらお気軽にご連絡ください。


本屋さんへのWEB相談・支援承っております

本屋さんや出版社さん、酒屋さん、小売店舗さん、美容院さん、個人病院さん、個人事業さんなど、街場の商いや「個人やまだ小規模だけどもっとWEBをいろいろやりたい」かたなどのご相談、支援など承っておりますので、お気軽にご相談ください。※企業のWeb担当も承っております。


Twitterもやってます

Twitterで更新告知しているので、本と本屋とWebについての情報を知りたい方どうぞお気軽にフォローください。


この記事が気に入ったら、サポートをしてみませんか?
気軽にクリエイターの支援と、記事のオススメができます!
note.user.nickname || note.user.urlname

サポートは今後のより良い情報発信に活用させていただきます。

スキありがとうございます!
6
「心は、動かさないとサビる。」 短編・言葉・名言など「短くも深い、心動く」作品やクリエイターを紹介するBooks365を運営。http://books365.biz 「フリーのWEB担当者」として制作・改善・運用・WEBマーケティング。https://web-365.biz/