LoginSignup
2
2

More than 3 years have passed since last update.

@Umi_large(ぴちぴち)

共用施設の受託解析の電子受付システムをGoogleのサービスで組み立てる-(2) Google SpreadSheetとGoogle Apps Scriptを使った依頼管理と引換用ウェブアプリの作成

Last updated at Posted at 2019-09-28
[前の投稿]

(1) Google Formを使った依頼の収集

やりたい事

  1. Google Formで送られてくる依頼はGoogle SpreadSheetにたまってくれるようになったので、たまった依頼レコードごとに開始と終了日時を管理
  2. 依頼された解析が物理的にサンプルを返す必要があるものだった場合に、引換用の引換票(もしくはそれに相当する記録)を引き換えにする事で間違ったユーザーにサンプルが返されないようにする
    (No more 顔パス、No more 人の記憶依存のシステム)

依頼レコードの管理

前回のフォームとシートを利用して依頼レコードがたまっているので、各依頼レコードに対してそれぞれ固有の依頼IDと作業開始日時・終了日時を記録させましょう。

これは前回作った「フォームの回答引用」シートを改変するか、さらに引用する「作業用」シートを作成して対処します。
E8EBD17D-8AAF-4299-BA33-25490FA5D81B.jpeg

依頼IDはフォームの回答日と何番目の依頼であるかを盛り込んだものを適当に関数で作成させました。

依頼IDの列
="[依頼名のイニシャル]"&concatenate(text([タイムスタンプのセル],"yyyymmdd"),"-",text(row()-1,"000"))

作業者はサンプル受付と作業開始の際に、それぞれの列に日付を入力してもらうことにしました(WindowsならCntr+：,MacintoshならCommand+：で入力してくれます)。

引換用インターフェースの作成

引換ステップは契約に対する納品が行われる非常に重要なステップなので、なるべく手打ちで管理されることは避けたいなと思い、Google Apps Scriptによって引換専用のインターフェースを作ることにしました。

Google Apps Scriptが何か知らない人は以下の記事がとてもわかりやすいので参考にしてください。
Google Apps Script 入門 - @t_imagawaさま
Google Apps Script は何が強くてどんなときに使うべきか自分なりのプラクティスをまとめてみた - tanabeeさま

引換と同時に作業用シートの引換日のセルに日時が記録され、またアクセスした人や引換したものの依頼IDのログが取れるように設計します。「Log」のシートをあらかじめ用意しておきましょう。

また引換業務を行うスタッフのみが引換機能を利用する権限を持つようにしたいので、引換用インターフェースにアクセスしたアクセスがスタッフであるかを照合できるように「AuthorizedList」のシートを用意しておきます。
7FE51CC7-42F1-4572-9D30-EE68425032E9.jpeg

Google Apps Script(GAS)のはじめ方(Standalone Project)

まずはhttps://script.google.com/home/myを開いてみてください。

これまで自分が作ったGASのプロジェクトが表示されます。
97E7ED88-94A6-4F6A-96A9-0DA7B109C59B.png

「新規スクリプト作成」に進むと新しい「無題のプロジェクト」がDriveのホームディレクトリに作成され、GASのウェブ上のエディターが起動します。

F394FBD3-5227-4F20-84A1-25D36B06EF19.png

ここに次に示していくようなスクリプトを書き込んでいくと、実行した時にスクリプトで命令する内容が行われるようになっています。

Google Apps Script(GAS)の構成

  1. 現在時刻を呼び出し格納
  2. アクセスしてきたアカウントの情報(ID?address?)を格納
  3. 作業用SpreadSheetを参照し、引換日時を出力するセルを指定する(指定するための値を引換の際に入力する依頼IDなどから導出・参照させる)
  4. 引換用インターフェースにアクセスしたアカウントが必ず引換業務をするスタッフのものであるか確認させる
  5. アクセスしたアクセスがスタッフで間違いなければ作業用SpreadSheetの指定のセルに格納させた現在時刻を出力、そうでなかったりすでに引換済みの場合はアクセスログのみを出力する

上記の要件を満たすために、今回はURLに組み合わせされたパラメータを読み込みながら実行される関数doGet(e)を使いました。

doGet(e)でできることに詳しい投稿があったので参考にしてください。
GASでGetパラメータを受け取ってスプレッドシートに書き込む方法 - @hirohiro77さま

Acception_exchange.gs(前半部分)
var datetime = new Date(); //アクセス時刻取得
  var UserInfo = Session.getActiveUser().getEmail(); //アクセスユーザーのgmail addressの取得
  Logger.log(UserInfo)
  var sheetid = "1NVOgZXrRBxGdrBklHVwR3GaUNrR4AlLhgh7JqvzcxVI" //SpreadsheetのID(各SpreadSheetのURLにある長めの文字列)
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName('作業用(フォームの回答引用)');  //spreadsheet idとシート名を直接指定
  var no = e.parameter.no; //e.parameter.no 依頼ID
  var rownum = e.parameter.row; //e.parameter.row　入力行
  var colnum = e.parameter.col; //e.parameter.col　入力列
  Logger.log("no="&no&", row="&rownum&", col="&colnum)

  var LogSheet = SpreadsheetApp.openById(sheetid).getSheetByName('Log');
  var lastRowss = ss.getLastRow(); //シートの最終行番号を取得
  var ssList = ss.getRange(1,1,lastRowss,11).getValues(); //シートの指定した範囲の値を取得(範囲原点の行番号、範囲原点の列番号、境界の行と原点の距離、境界の列と原点の距離)

この前半部分のスクリプトでは、時刻の格納とアクセスユーザーの格納を行ったあと、アクセス内容からパラメーターを取得(e.parameter.*)しています。

時折出てくる「SpreadsheetApp.openById(sheetid).getSheetByName()」によってSpreadSheetを参照して「作業用」「Log」を読み込み、「作業用」に関しては値の取得も行なっています。

Acception_exchange.gs(後半部分)
/*認証者アカウントリストの読み込み*/
  var authorization = SpreadsheetApp.openById(sheetid).getSheetByName('AuthorizedList');
  var authorizationListlast = authorization.getLastRow();
  var authorizationList = authorization.getRange(1,1,authorizationListlast,2).getValues();

  /*認証者アカウントリストとWebアプリのアクセスアカウントを照合しながら、認証者であれば指定のセルに日付を入力する*/
  var i=0;
  while(i <= authorizationListlast){
    /*アクセスユーザーと認証者が合致して、かつ、まだ引換日がシートに入っていない場合*/
    if(authorizationList[i].indexOf(UserInfo)==1 && ssList[rownum-1][colnum-1].toString().length ==0){
      ss.getRange(rownum,colnum).setValue(datetime);
      LogSheet.appendRow([datetime,'認可されたリクエスト', no, UserInfo]); //Logシートにアクセルログを入力させる
      return ContentService.createTextOutput("User："+UserInfo+"\nNo. "+no+" シートへの登録が完了しました")
    }else{
      /*引換日がシートに入っていた場合*/
      if(ssList[rownum-1][colnum-1].toString().length !=0){
        LogSheet.appendRow([datetime,'すでに行われたリクエスト', no, UserInfo]); //Logシートにアクセルログを入力させる
        return ContentService.createTextOutput("User："+UserInfo+"\nNo. "+no+" すでにシートへの登録が完了しています");
      }
      /*アクセスユーザーと認証者が合致しなかった場合*/
      if(i == authorizationListlast){
        LogSheet.appendRow([datetime,'不正なユーザーからのリクエスト', no, UserInfo]); //Logシートにアクセルログを入力させる
        return ContentService.createTextOutput("認証者が不正です");
      }
      i = i+1;
    };
  };

後半部分ではさらに「AuthorizedList」を読み込みます。

次のWhile文で繰り返しifの条件によって「AuthorizedList」とアクセスユーザーの照合がとれてかつ「作業用」ですでに引換日になんらかの値が入ってない場合に、引換を受理させるメッセージを表示させて引換日を「作業用」シートに反映させます。

各分岐でアクセスごとに「Log」シートに対して[アクセス日時、アクセス結果の概要、依頼ID、アクセスユーザーのGmail Accountアドレス]の配列が最後の行に出力させるようにしています。

完成したスクリプトはGoogle Apps Scriptのエディタメニューから「ウェブアプリケーションとして導入」を行なって、ウェブアプリケーションのURLを必ず取得しましょう。

完成したスクリプト

Acception_exchange.gs
function doGet(e) {
  var datetime = new Date(); //アクセス時刻取得
  var UserInfo = Session.getActiveUser().getEmail(); //アクセスユーザーのgmail addressの取得
  Logger.log(UserInfo)
  var sheetid = "1NVOgZXrRBxGdrBklHVwR3GaUNrR4AlLhgh7JqvzcxVI" //SpreadsheetのID
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName('作業用(フォームの回答引用)');  //spreadsheet idとシート名を直接指定
  var no = e.parameter.no; //e.parameter.no 依頼ID
  var rownum = e.parameter.row; //e.parameter.row　入力行
  var colnum = e.parameter.col; //e.parameter.col　入力列
  Logger.log("no="&no&", row="&rownum&", col="&colnum)

  var LogSheet = SpreadsheetApp.openById(sheetid).getSheetByName('Log');
  var lastRowss = ss.getLastRow(); //シートの最終行番号を取得
  var ssList = ss.getRange(1,1,lastRowss,11).getValues(); //シートの指定した範囲の値を取得

  /*認証者アカウントリストの読み込み*/
  var authorization = SpreadsheetApp.openById(sheetid).getSheetByName('AuthorizedList');
  var authorizationListlast = authorization.getLastRow();
  var authorizationList = authorization.getRange(1,1,authorizationListlast,2).getValues();

  /*認証者アカウントリストとWebアプリのアクセスアカウントを照合しながら、認証者であれば指定のセルに日付を入力する*/
  var i=0;
  while(i <= authorizationListlast){
    /*アクセスユーザーと認証者が合致して、かつ、まだ引換日がシートに入っていない場合*/
    if(authorizationList[i].indexOf(UserInfo)==1 && ssList[rownum-1][colnum-1].toString().length ==0){
      ss.getRange(rownum,colnum).setValue(datetime);
      LogSheet.appendRow([datetime,'認可されたリクエスト', no, UserInfo]); //Logシートにアクセルログを入力させる
      return ContentService.createTextOutput("User："+UserInfo+"\nNo. "+no+" シートへの登録が完了しました")
    }else{
      /*引換日がシートに入っていた場合*/
      if(ssList[rownum-1][colnum-1].toString().length !=0){
        LogSheet.appendRow([datetime,'すでに行われたリクエスト', no, UserInfo]); //Logシートにアクセルログを入力させる
        return ContentService.createTextOutput("User："+UserInfo+"\nNo. "+no+" すでにシートへの登録が完了しています");
      }
      /*アクセスユーザーと認証者が合致しなかった場合*/
      if(i == authorizationListlast){
        LogSheet.appendRow([datetime,'不正なユーザーからのリクエスト', no, UserInfo]); //Logシートにアクセルログを入力させる
        return ContentService.createTextOutput("認証者が不正です");
      }
      i = i+1;
    };
  };
};

次回に課題になる事

今回は管理の大まかな土台とウェブアプリを作っただけなので、まだ管理するためのインターフェース(特にWebアプリ)が扱いやすい形になってはいないことが課題です。

次回ではWebアプリのインターフェースをQRcodeとして設計して、スタッフが持つスマホやバーコードリーダー付きのネットワーク端末で、読むだけでWebアプリが機能して引換認証がされるようなシステムを作っているので書いていきたいと思います。

[次の投稿]

(3) Google Apps Scriptで作ったウェブアプリケーションにアクセスするためのQR codeをSpreadSheetの対応するセルに反映する

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2