見出し画像

非エンジニアがGASで請求書(PDF)発行を自動化してみた

ikedon

このnoteは何？

非エンジニアである僕が自分や他人の作業をGASを使って、自動化することを目標に頑張った内容の記録

このnoteの目次

1. 自動化の目的及び実現したいこと
2. 参考にした記事
3. 参考記事を元に頑張ったこと

1. 自動化の目的

目的は、請求書のPDF化を1枚1枚手動でやっていると時間が取られすぎるということで、PDFの発行という単純作業を自動化することによって、他に費やす時間を確保すること

今回の実装で解決すべきこと
月に1回の実行で、その月の名前のフォルダを作成
作成したフォルダに発行したPDFを先ほど作成したフォルダに格納する

2. 参考にした記事

今回はPDF化するという部分で、たもけん様の以下の記事を参考にいたしました。

GAS（Google Apps Script）：スプレッドシートの特定の範囲をPDFにする

また、PDF化の処理は結構重い処理みたいで、いわゆる6分の壁というものにぶつかったので、まえしょー様の以下の記事も参考にしました。

[GAS]実行時間6分の壁を越えよう(不死鳥関数編)

3. 参考記事を元に頑張ったこと

1. PDF化について、
PDF化について課題を感じた点はループ処理した時に、リクエストが集中するためか、きちんとPDF化することができないことがある点でした。
そのため、PDF化する際にsleepを挟み、PDF発行による処理で重たくならないように、猶予を与えてやらなければならないことでした。

sleep処理をしないと以下のようなものが出来上がる

画像1

なので。適切にsleep処理で、リクエストの集中を回避してあげる必要がありました。
10秒程度sleepすると良いという記事もあったのですが、検証の結果6800ミリセカンドあれば、問題なく発行できることがわかりました。

2. 6分の壁について
これがかなり大変でした💦
そもそも6分の壁とは？
GASの1回の実行時間が6分までという上限が存在し、6分を超えると強制的にタイムアウトしてしまうというものです。
6800ミリセカンドのsleepを設けた結果1件処理するのにおおよそ8~9秒程度かかっており、おおよそ30件ちょっと処理すると止まってしまうという状況でした。

そのタイムアウトまでで一度処理を終了し、その後再開するということが必要になるという点で2つ目の記事を参考にしました。
この問題を解決するには、6分を超える前にスクリプトの中でトリガーを設定して、トリガーによる再実行を促すようにするというのが趣旨になっていました。
スクリプトを実行して設置したトリガーがうまく発動してくれるところまでは確認しましたが、設置したトリガーのスクリプト内でトリガーを設置するという点がうまくいきませんでした。

スクリプトを実行して設置したトリガーをAとする
Aによるスクリプト実行
Aの実行で設置されるトリガーをA'とする
A'のトリガーの設置までは確認済み
A'によるスクリプト実行は失敗

トリガーを確認すると、「原因は不明です」と出てしまい、何度テストしても全くうまくできませんでした。
どなたかここについて知見をお持ちの方がいらっしゃいましたら、ぜひご教示いただけますと幸いです。

さて、とはいえ、「できませんでした」というわけにもいかず、どのようにしたかというと
スクリプトを実行する際に必要な回数分のトリガーを設置することで回避することに成功しました。
例)：65回処理する必要があるとしたら、20回ずつの処理を1つの単位としてスクリプトでの実行を含めて4回実行するという感じ
どうやらスクリプトを実行した際に設置したトリガーは問題なく発動するような状況でした(おまかんだったらすいません)

以下は記述したスクリプトです
(かなり自分が使っているspreadsheetに依存している内容なので、あまり有用ではないかもしれませんが)​

// PDFを出力するspreadsheetを指定する
const ss = SpreadsheetApp.getActiveSpreadsheet();
// PDF出力するシートを指定 
const invoice = ss.getSheetByName( 'invoice' );
const ssId = ss.getId();
const sheetId = invoice.getSheetId();

// 今月を表すフォルダ名を指定するシートとセルの値を取得
const provider = ss.getSheetByName( 'provider' );
const thisMonth = provider.getRange( 1 , 7 ).getValue();

// PDFを出力する対象者の一覧が記載されているシート
const user = ss.getSheetByName( 'user' );
// 実行回数を指定するための値をセルで指定していたので、それも取得
const userLR = user.getRange( 1 , 5 ).getValue();

// 再開する時にどこから取得すれば良いのかを記録しておくためのシート
const funcSheet = ss.getSheetByName( 'func' );
// 1回で何回PDF発行の処理をするかを指定するセル：暫定的に20と指定
const maxCount = funcSheet.getRange( 2 , 3 ).getValue();

// 月の名前とフォルダIDを保存しておくためのシート
const recordSheet = ss.getSheetByName( 'record' );
const recordLR = recordSheet.getLastRow();

function createFolder(){
 // 1回目の処理
 // 一旦全てのトリガーを削除(この処理以外のトリガーはないので全削除にしました)
 deleteTrigger();
 // 最終行の次の行に実行した際の月名を記録する
 recordSheet.getRange( recordLR + 1 , 1 ).setValue( thisMonth );                      
 // IDからフォルダを取得
 let invoiceFolder = DriveApp.getFolderById( '×××××××××××××××××××××××××××××××××' );　
 // 取得した場所に、指定名称でフォルダ作成
 let newfolder = invoiceFolder.createFolder( thisMonth + "請求書" );　                
 // 先ほど作成したフォルダのIDを取得
 var folderid = newfolder.getId();　
 // ループ処理する際の開始番号を指定する
 var startNumber = 2;
 // 処理を中止して、再開する時にどこのフォルダに格納したら良いのかを指定するために先ほど取得したフォルダIDを月名の1右列のセルに入力
 recordSheet.getRange( recordLR + 1 , 2 ).setValue( folderid );
 // 1回目のスクリプト内での処理を実行
 firstExe( startNumber , folderid , thisMonth );
 
}

// 1回目の処理、2回目とは異なり上限に達した時にトリガーを設置する
function firstExe( startNumber , folderid , thisMonth ){
 // 2行目から20回の処理を行う
 for ( let i = startNumber ; i <= startNumber + maxCount ; i ++ ){
   // ループ回数が20回目になったらトリガーを設置する
   if ( i == startNumber + maxCount ){
     // トリガーを設置
     createTrigger();

   }else{
     // 請求書をinvoiceのシートに表示及びPDFの発行
     createInvoice( i , folderid );
   }
 }
 return ;
}

// 2回目以降の処理
function secondalyExe(){
 // フォルダIDを取得
 var folderid = recordSheet.getRange( recordLR , 2 ).getValue();
 // 前回がどこで終わったのかを取得
 var zenkai = funcSheet.getRange( 1 , 3 ).getValue();
 // ループ処理の開始位置を指定
 var startNumber = zenkai + 1 ;
 // 1個のトリガーで20回と指定しているので、65回処理が必要だとすると4個トリガーを設置して最後15回分余計に処理するのを防ぐ
 if( startNumber + maxCount > userLR ){
   var lastNumber = userLR ;
 }else{
   var lastNumber = startNumber + maxCount ;
 }
 for ( let i = startNumber ; i <= lastNumber ; i ++ ){
   createInvoice( i , folderid );
 }
}

// 請求書をspreadsheet上に表示させる
function createInvoice( i , folderid ){
 // ユーザーIDを取得
 let userNumber = user.getRange( i , 5 ).getValue();
 // 請求書にユーザーIDを出力、請求書に請求情報を表示(表示の仕方はspreadsheetにクエリ関数を使っている)
 invoice.getRange( 3 , 11 ).setValue( userNumber );
 // PDFのタイトルに入力するためにユーザー名を取得
 let userName = invoice.getRange( 3 , 12 ).getValue();
 // PDF化のリクエスト数を緩和するためにスリープ
 Utilities.sleep( 6800 );
 // PDF発行
 exportSheetToPDF( i , folderid , userName ); 
 // 再開する行目を記録
 funcSheet.getRange( 1 , 3 ).setValue( i ); 
}

// 全件削除
function deleteTrigger() {
 var allTriggers = ScriptApp.getProjectTriggers();
 for (var i = 0; i < allTriggers.length; i++) {
   ScriptApp.deleteTrigger(allTriggers[i]);
 }
}

// トリガーの設置
function createTrigger(){
 // セルで何個トリガーを設置するべきを指定している
 var kaisu = funcSheet.getRange( 3 , 3 ).getValue() - 1 ;
 // 必要なトリガーの数だけループ
 for ( let i = 1 ; i <= kaisu ; i ++ ){
   var date = new Date();
   // 必要な個数のトリガーが同時に処理を開始するとやばそうと思ったので、開始時刻をずらすために変数iで開始時刻を変える
   date.setMinutes(date.getMinutes() + i * 5 );  
   ScriptApp
   .newTrigger('secondalyExe1')
   .timeBased()
   .at(date)
   .create();
 }
}

// これ以下で請求書のテンプレートシートに対象者のIDを入力し、名前や請求情報を表示、表示内容をPDF化するという処理をしているのだが、記載すると引用のルールに反する可能性がある改変が含まれているので、記載しません。
function exportSheetToPDF( i , folderid , userName ){  
 
 // スプレッドシートをPDF出力するためのURL
 const url = 'https://docs.google.com/spreadsheets/d/' + ssId + '/export?'
 
 // PDF出力のオプションを設定
 const options = 'exportFormat=pdf&format=pdf'
 + '&gid=' + sheetId  //PDFにするシートの「シートID」
 + '&portrait=true'  //true(縦) or false(横)
 + '&size=A4'         //印刷サイズ
 + '&fitw=true'       //true(幅を用紙に合わせる) or false(原寸大)
 + '&gridlines=false' //グリッドラインの表示有無
 // 範囲を指定したかったので、引用元とは改変しております
 + '&range=A1%3AJ69'   //★POINT★セル範囲を指定
 // %3A はコロン(:)を表す
 
 const requestUrl = url + options;
 
 //API使用のためのOAuth認証
 const token = ScriptApp.getOAuthToken();
 
 const params = {
   'headers' : {'Authorization':'Bearer ' + token},
   'muteHttpExceptions' : true
 };
 
 const response = UrlFetchApp.fetch(requestUrl, params);

 //Blobオブジェクトを作成
 const blob = response.getBlob();
 // ファイル名を指定したいので、引用元とは改変しております
 blob.setName( thisMonth + '_' + userName + '.pdf'); //PDFファイル名を設定
 
 //指定のフォルダにPDFファイルを作成
 const folder = DriveApp.getFolderById( folderid );
 folder.createFile(blob);
 
}

反省

・6分の壁の解決策を模索している時に、プロパティに言及されていたのですが、現在の私では理解することができず…
・処理スピードを向上させるという観点からするとあまりにも多くのセルを参照しているのではないか？という気持ちもあり、今後はセルを参照しなくてもいいような形式の実装を目指したい
・今回はやらなくて良かったのですが、そのうちメールを送信するとかも出てきそうなので、その際には添付方法についても勉強したいと思います。
・明細書などの発行も必要になるかも…そうすると処理すべき内容が増えるので、さらに複雑化しそうで頭が重い
・宣言句(?)letとかvarとかconstとかの使い分けがうまくできてなくて、最初はletとconstを使っていたのですが、ifやforの外ではletで宣言したものが引き継がれないということを知り、急遽varを使うなどをしているので、入り乱れていてダサい

まあ、今後このような形で自分が実装したものをどんどんnoteに備忘録的に残していきたいと思います。

いいなと思ったら応援しよう！