LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@Umi_large(ぴちぴち)

共用施設の受託解析の電子受付システムをGoogleのサービスで組み立てる-(3) Google Apps Scriptで作ったウェブアプリケーションにアクセスするためのQR codeをSpreadSheetの対応するセルに反映する

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

(2) Google SpreadSheetとGoogle Apps Scriptを使った依頼管理と引換用ウェブアプリの作成

やりたい事

  1. Google Apps Scriptのエディターメニューから作成したウェブアプリケーションのURLをQRcodeにして利用方法を簡単にしたい
  2. ウェブアプリケーションはGoogle SpreadSheetに対して働かせるものなので、作ったQRcodeはSpreadSheet上の対応する行のセルにリンクか何かの形で置いといて参照しやすいようにしたい

QRcodeの作成方法

私は信号処理とか記号処理についてはサッパリわからないので、QRcodeがどういう法則で作られているものかわかりませんが、あの愛知県のデンソーさんの開発部門が開発したものだそうです。
QRコードとは？｜QRコードドットコム｜株式会社デンソーウェーブ

どうやらQRcodeに圧縮するデータの構成や桁数や誤り訂正機能のレベルによって生成するべきQRcodeの大きさが違うそうですが、なるほど1から生成させるシステムを作るのは難しい。

調べてみるとQRcodeを生成するAPIを利用するようですが、「スプレッドシート QRcode」で検索すると、結構な数でIMAGE関数を使ってAPIを利用している投稿が多いです。

僕がこれから使おうと思っているAPIを使ったものに直すと以下のような関数になります。
利用するAPIサービスのページ：QR code API - Foundata GmbH.さまから

IMAGE関数によるQRcode生成APIの利用（MicrosoftのExcelにはない関数です）
/* &や#といった一部のクエリに用いられる文字はそのままだとスマホのバーコードリーダーで読んでくれないことがあるのでURLエンコードに直します */
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150%26data="&[QRcodeにしたいデータが入っているセル]

この方法を使えば確かに思ったセルにQRcodeが入って目的は達成されますが、この方法を取るべきではない理由がいくつかあります。

  1. Google Spreadsheet上の関数なので、誰かがこの関数を使ったシートを開ける度に、誰かがこの関数を使ったセルをクリックする度に、関数が再演算を行いその度にAPIが呼び出される(100個のセルに機能できる状態でこの関数が使われると、そのシートを閲覧するだけでも100回APIが呼び出される)(ハッキリ言ってかなり迷惑)
  2. しかも呼び出されるAPIの実行時間間隔は不明(コントロールすらできない)

おカネも払ってないのに年間数千数百の依頼に対応するために利用させていただくAPIが、シートを開ける度に数千数百回と数秒間で叩かれるのはちょっと無理だと思いました。

なので以下の手順で目標を達成することにしました。

  1. Google Apps ScriptでQRcodeを記録したいSpreadsheetの特定のシートだけを読み込んで、QRcodeにしたいデータをプールする
  2. 条件に合致し、まだQRcodeを作成していない行だけを配列として抽出
  3. QRcodeを生成してくれるAPIを使う。結果をバイナリデータにする。
  4. バイナリデータからPNGを作成してGoogle Driveの指定したフォルダに保存
  5. 保存したPNGファイルのファイルIDを取得し、ファイルIDとファイルURLを対応するSpreadsheetのセルに書き込む

QRcodeの生成したログをとるために「Log_QRgenerate」というシートを新たに作りましょう。

(この手順は深澤 豪さまの投稿を参考にして考えさせて頂きました。)
Googleスプレッドシートで簡単にQRコードを生成してファイルとして保存する - 深澤 豪さま

作った関数たち(QRcode生成／保存部から)

making_QRcode_fromSS()
function making_QRcode_fromSS() {
  var sheetid = "1NVOgZXrRBxGdrBklHVwR3GaUNrR4AlLhgh7JqvzcxVI"; //特定のspreadsheet id
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName('作業用(フォームの回答引用)');  //spreadsheet idとシート名を直接指定
  var LogSheet = SpreadsheetApp.openById(sheetid).getSheetByName('Log_QRgenerate');
  var lastRowss = ss.getLastRow(); //シートの最終行番号を取得
  var lastRowlog = LogSheet.getLastRow(); //シートの最終行番号を取得
  
  var ssdata = ss.getRange(1,1,lastRowss+1,13).getValues(); //鋳型となるスプレッドシートデータを丸ごと取得(起点row,起点col,row範囲,col範囲)
  var ssextdata = []; //データ記録のためにからの配列を用意しておく
  
  /* QRcodeを生成させるデータがある行で条件に合うものを抽出*/
  var i = 1;
  while(i <= lastRowss){
    if(ssdata[i][11].length == 0 && ssdata[i][12].length == 0 && ssdata[i][7].toString().length != 0){  //依頼IDがあり、まだQRcodeの作成が終わってないもの
      //(Time,該当行,依頼ID,氏名)この順の配列をプールしていきたい
      var datetime = new Date(); //アクセス時刻取得
      ssextdata.push([datetime,i+1,ssdata[i][7],ssdata[i][2]]);  //該当行を丸ごと抽出し、そのときの行番号と配列を結合してプールする
    };
    if(ssdata[i][0].toString().length == 0) break; //空行に達したらループを終了
    i = i+1;
  };
  
  Logger.log(ssextdata)
 
  /*QRcodeの共通配列(今回はhttps://script.google.com/macros/s/AKfycbx1uDYeWUdKUzehlo5oFm--ttW7-4-xbeL2qQvD99_bmYuLryPA/exec)*/
  var applicationURL_common1 = "https://script.google.com/a/keio.jp/macros/s/"
  var applicationURL_unique = "AKfycbx1uDYeWUdKUzehlo5oFm--ttW7-4-xbeL2qQvD99_bmYuLryPA"
  var applicationURL_common2 = "/exec\?"
  
  var folderId = '1--CenC4HkPLy_FZRlD-nJJes_zSbJqkJ'; //保存先クラウドフォルダのID
  
  var j = 0;
  while(j <= ssextdata.length-1){
    
    /* doGet(e)で値をgetさせるURLを作成しQRcodeのAPIに渡すためのURLを生成(これにアクセスすると内容に従った処理が行われるようなWebアプリを別に作っておく）*/
    var fetchUrl = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" + applicationURL_common1 + applicationURL_unique + applicationURL_common2 +
      "no\=" + ssextdata[j][2] + "\%26row\=" + ssextdata[j][1] + "\%26col\=" + 11;
    
    var getImage = UrlFetchApp.fetch(fetchUrl); //QRcodeのAPIに渡す
    var QRimage = getImage.getBlob().setName("".concat(ssextdata[j][2],"_",ssextdata[j][3],"_",ssextdata[j][1]) + ".png") //APIの結果をバイナリで取得し指定したファイル名のPNGにする
    var fileDrive = DriveApp.getFolderById(folderId).createFile(QRimage); //ファイルを作成・保存 
    var fileId = fileDrive.getId(); //作成したファイルのIDを取得。
    Logger.log("j="+j+", "+fetchUrl); 
    
    fileDrive.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW) //SpreadsheetのIMAGE関数が作成したファイルにアクセスできるよう閲覧権限を変更
    var fileId_image = "http://drive.google.com/uc?export=view&id=".concat(fileId); //SpreadsheetのIMAGE関数に渡すファイルURLを生成
    
    /*作成したファイル(QRcode)を指定のSpreadsheetの特定のセルに反映させる*/
    ss.getRange(ssextdata[j][1], 12).setValue(fileId); //ファイルIDをセルに入力
    ss.getRange(ssextdata[j][1], 13).setValue(fileId_image); //作成したQRcodeを表示させるIMAGE関数をセルに入力
    
    j = j+1;
    sleep(1000) //Sys.sleepを1sec入れて、APIの秒間呼び出し回数が超えないように配慮する
  }
  /*ログをシートに残す*/
  datetime = new Date()
  LogSheet.appendRow([datetime, ssextdata.length, lastRowss]); //Logシートにアクセルログを入力させる   
}

作ったQRcodeのファイルはいろんなGoogle SpreadsheetのAdd-onでも使えるように共有設定を「リンクを知っている全員が閲覧者」にしているのと、Spreadsheet上に記録するURLとして少し特殊なものをも利用しているのが個人的にはオリジナルでミソなところです。

それとsleep()という本来Google Apps Scriptにはない関数を使っています。

JavaScriptにはシステムスリープの関数がないようですが、Google Apps ScriptにはUtilities.sleep()というのがあります。

しかし、あんまり使用頻度の高いものでは無いので今回のスクリプトを書くまで忘れていました。そのため別の関数をとしてちょっと色をつけてライブラリから利用できるように加工しました。

sleep()
/*システムスリープ*/
function sleep(time_length){
  var first = new Date(); //開始時刻
  Utilities.sleep(time_length);
  Logger.log("sleep="+first);
};

最終的にはこの関数を実行する方法を時間間隔トリガーにするのと、Spreadsheetを開けた時に専用のメニュータブを追加し中身をクリックすることをトリガーとすることにしました。

専用のメニュータブは以下の関数を実行させることで追加できます。

onOpen()
function onOpen(){
  var tabname = "QRcode";
  var newmenu = [{name: "QRcode Generator", functionName: "making_QRcode_fromSS"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu(tabname,newmenu); //メニューを追加
}

これらの関数でできたスクリプトを作るのですが、onOpen()関数に関してはGoogle SpreadsheetのContainer Bound Scriptとして作成されたプロジェクト上でしか使えないという仕様みたいですね。

なので、Spreadsheetのツールタブから「スクリプトエディタ」を選択して、そこで開いたプロジェクト上に先の関数を入れてスクリプトを完成させます。
A1C092B3-501D-46C8-BB81-97977DC3C372.jpeg

完成したスクリプト

QRcodeGenerator.gs
function onOpen(){
  var tabname = "QRcode";
  var newmenu = [{name: "QRcode Generator", functionName: "making_QRcode_fromSS"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu(tabname,newmenu); //メニューを追加
}
 
/*システムスリープ*/
function sleep(time_length){
  var first = new Date(); //開始時刻
  Utilities.sleep(time_length);
  Logger.log("sleep="+first);
};
 
/*QRcodeをAPIで作って、spreadsheetの特定のセルに記入させる*/
function making_QRcode_fromSS() {
  var sheetid = "1NVOgZXrRBxGdrBklHVwR3GaUNrR4AlLhgh7JqvzcxVI"; //特定のspreadsheet id
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName('作業用(フォームの回答引用)');  //spreadsheet idとシート名を直接指定
  var LogSheet = SpreadsheetApp.openById(sheetid).getSheetByName('Log_QRgenerate');
  var lastRowss = ss.getLastRow(); //シートの最終行番号を取得
  var lastRowlog = LogSheet.getLastRow(); //シートの最終行番号を取得
  
  var ssdata = ss.getRange(1,1,lastRowss+1,13).getValues(); //鋳型となるスプレッドシートデータを丸ごと取得(起点row,起点col,row範囲,col範囲)
  var ssextdata = []; //データ記録のためにからの配列を用意しておく
  
  /* QRcodeを生成させるデータがある行で条件に合うものを抽出*/
  var i = 1;
  while(i <= lastRowss){
    if(ssdata[i][11].length == 0 && ssdata[i][12].length == 0 && ssdata[i][7].toString().length != 0){  //依頼IDがあり、まだQRcodeの作成が終わってないもの
      //(Time,該当行,依頼ID,氏名)この順の配列をプールしていきたい
      var datetime = new Date(); //アクセス時刻取得
      ssextdata.push([datetime,i+1,ssdata[i][7],ssdata[i][2]]);  //該当行を丸ごと抽出し、そのときの行番号と配列を結合してプールする
    };
    if(ssdata[i][0].toString().length == 0) break; //空行に達したらループを終了
    i = i+1;
  };
  
  Logger.log(ssextdata)
 
  /*QRcodeの共通配列(今回はhttps://script.google.com/macros/s/AKfycbx1uDYeWUdKUzehlo5oFm--ttW7-4-xbeL2qQvD99_bmYuLryPA/exec)*/
  var applicationURL_common1 = "https://script.google.com/a/keio.jp/macros/s/"
  var applicationURL_unique = "AKfycbx1uDYeWUdKUzehlo5oFm--ttW7-4-xbeL2qQvD99_bmYuLryPA"
  var applicationURL_common2 = "/exec\?"
  
  var folderId = '1--CenC4HkPLy_FZRlD-nJJes_zSbJqkJ'; //保存先クラウドフォルダのID
  
  var j = 0;
  while(j <= ssextdata.length-1){
    
    /* doGet(e)で値をgetさせるURLを作成しQRcodeのAPIに渡すためのURLを生成(これにアクセスすると内容に従った処理が行われるようなWebアプリを別に作っておく）*/
    var fetchUrl = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" + applicationURL_common1 + applicationURL_unique + applicationURL_common2 +
      "no\=" + ssextdata[j][2] + "\%26row\=" + ssextdata[j][1] + "\%26col\=" + 11;
    
    var getImage = UrlFetchApp.fetch(fetchUrl); //QRcodeのAPIに渡す
    var QRimage = getImage.getBlob().setName("".concat(ssextdata[j][2],"_",ssextdata[j][3],"_",ssextdata[j][1]) + ".png") //APIの結果をバイナリで取得し指定したファイル名のPNGにする
    var fileDrive = DriveApp.getFolderById(folderId).createFile(QRimage); //ファイルを作成・保存 
    var fileId = fileDrive.getId(); //作成したファイルのIDを取得。
    Logger.log("j="+j+", "+fetchUrl); 
    
    fileDrive.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW) //SpreadsheetのIMAGE関数が作成したファイルにアクセスできるよう閲覧権限を変更
    var fileId_image = "http://drive.google.com/uc?export=view&id=".concat(fileId); //SpreadsheetのIMAGE関数に渡すファイルURLを生成
    
    /*作成したファイル(QRcode)を指定のSpreadsheetの特定のセルに反映させる*/
    ss.getRange(ssextdata[j][1], 12).setValue(fileId); //ファイルIDをセルに入力
    ss.getRange(ssextdata[j][1], 13).setValue(fileId_image); //作成したQRcodeを表示させるIMAGE関数をセルに入力
    
    j = j+1;
    sleep(1000) //Sys.sleepを1sec入れて、APIの秒間呼び出し回数が超えないように配慮する
  }
  /*ログをシートに残す*/
  datetime = new Date()
  LogSheet.appendRow([datetime, ssextdata.length, lastRowss]); //Logシートにアクセルログを入力させる   
}

どんな感じで動いてくれるか結果

onOpen()関数のおかげで、ヘルプタブの隣にスクリプトの機能を実行するメニューが登場しました。

43F0ED1C-3C05-4EDA-891D-CF3811EDC222.jpeg

これをクリックして実行することで、黄色の列にQRcodeファイルのファイルIDと共有リンクが追加されました。

D04CDB42-042F-46B4-AC32-123590EB1419.jpeg

ちゃんと指定したフォルダにQRcodeが保存されます。

9C99DB27-3D42-4EEC-BE5E-96059AA49B5B.jpeg

次回に課題になる事

色々やりましたが今回作ったものはQRcodeを生成するスクリプトです。

本来目指している引換業務にこのQRcodeを利用するには、依頼してきたユーザーさんだけにこのQRcodeをフィードバックして引換票として実際に使えるように配布しないといけないんですよね。

なので次の課題はそのフィードバックシステムなんですが、これは「Autocrat」というアドオンがとても便利なので次回にご紹介したいと思います。

1610CE68-AD8D-4D3C-BB4B-117379682E5E.jpeg

2
1
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
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?