応援クリックよろしくお願いします！
以前、売上記録と連動した請求書フォーマットをスプレッドシートで作成しました。
参考：スプレッドシートで売上と連動する請求書フォーマット作った
今回はこの請求書フォーマットからPDFを連続生成するスクリプトの紹介です。
もっと仕事をサボりたい業務を効率化したい一心でGASを勉強してプログラム書いてみました。
使用したスプレッドシートはこちら。必要に応じてコピーしてお使いください。
PDFを一括で大量に自動作成したい
①プルダウンの取引先一覧リストを請求書の宛先のセルに入力
事前に取引先は別シートでリスト登録しておきます。
for文で名前を１つずつ取得して請求書の宛先欄に入力。
②PDFを作成。（請求額が0円の場合は作成しない）
売上があればPDF を作成します。
コード
//スプレッドシートを取得
const ss = SpreadsheetApp.openById("シートIDを記入");
const sheet = ss.getSheetByName('請求書/自動');
const nameList = ss.getSheetByName('取引先一覧');
const listRows = nameList.getLastRow() - 1;//取引先一覧の行数
const listRange = nameList.getRange(2, 1, listRows, 1);//取引先一覧の範囲を取得
const names = listRange.getValues();//取引先一覧を取得
const folder = DriveApp.getFolderById('フォルダID記入');//PDF保存フォルダ
const invoiceMonth = sheet.getRange('A8').getValue();//請求書yymmを取得
const invoiceyymm = Utilities.formatDate(invoiceMonth, "JST", "yyMM");//日付を文字列に変換
function AutoPdf() {
// 請求先リストから1件づつPDFを作成
for (let i = 0; i < names.length; ++i) {
processAutoPdf(i);
}
}
//ループ処理
function processAutoPdf(i) {
let name = names[i][0];
sheet.getRange('A4').setValue(name);//リストの値を順に入力
SpreadsheetApp.flush();//スプレッドシートに値の変更を反映させる
let key = ss.getId();
let gid = sheet.getSheetId();
let token = ScriptApp.getOAuthToken();
//スプレッドシートのPDFURL
let url = "https://docs.google.com/spreadsheets/d/"
+ key
+ "/export?gid="
+ gid
+ "&exportFormat=pdf&format=pdf";
let options = {
headers: {
"Authorization": "Bearer " + token
}
}
let response = UrlFetchApp.fetch(url, options);
let file = response.getBlob();
let price = sheet.getRange('B10').getValue();//請求金額を取得
if (price == 0) {
//請求金額0円の場合は作成しない
console.log(`${names[i]}様は0円なので請求書なし`);
Utilities.sleep(10000);//10秒待機
} else {
//請求書保存先フォルダ
file.setName(invoiceyymm + "請求書_" + names[i] + "様.pdf");
folder.createFile(file);//PDFファイルを作成しフォルダに保存
console.log(`「${invoiceyymm}請求書_${names[i]}様.pdf」を生成しました。`);
Utilities.sleep(10000);//10秒待機
}
}
注意点と対策
PDFに変換するLoopでエラーコード429が発生
urlfetchでPDFを連続生成すると、処理の多さで以下のようなエラーが出ます。
Exception: Request failed for https://docs.google.com returned code 429.対策として、ループ中にUtilities.sleep(10000); で10秒待機させてから次のループ処理に進ませることにしました。
GASの6分の制限時間を超えて無限にPDFを生成する
GASのスクリプトの処理時間は6分以内という制限があり、6分以上経つと自動的に処理が中断されてしまいます。
今回のケースだと1処理に10秒ちょいかかってるので32〜33回くらいでタイムアウトします。
そこでGASで用意されているHtmlServiceを使って、GoogleAppsScriptとしてではなく、JavaScriptとして実行することでリストが何件あろうとも無限にPDFをつくることができました。
10秒かかる processAutoPdf を何回も実行させているだけなので実行時間は10秒ということになります。
では早速作ります。
左上のボタンからHTMLをクリックして、index.htmlファイルをつくります。
下記の通りコードを追加します。
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!= HtmlService.createHtmlOutputFromFile('css').getContent(); ?>
</head>
<body>
<!-- ここから -->
<button onClick="doAutoPdf()">実行</button>
<script>
let i =0;
let lastRow = <?!= listRows ?> -1;
function doAutoPdf(){
if (i > lastRow) return;
google.script.run.withSuccessHandler(doAutoPdf).processAutoPdf(i);
i += 1;
}
</script>
<!-- ここまでを追加 -->
</body>
</html><?!= 変数 ?> と記入することでGAS側で書いた変数をhtmlに持ってくることができます。
（html側に持ってくるにはGAS側のコードも追記が必要ですが、後ほど記述します）
google.script.run はGASのコードを実行する関数。
.withSuccessHandler() はGASのコードが正常に完了した際に()内の処理が実行されます。
processAutoPdf(i)関数が正常に処理したら、doAutoPdfを呼び出しています。
つぎにGAS側に以下のコードを追加します。
function doGet() {
let template = HtmlService.createTemplateFromFile('index');
template.listRows;
return template.evaluate();
}doGet関数は作成したWebアプリケーションのURLにアクセスがあったときに、自動でスクリプトを実行させる関数です。
template.listRows; と記載することでhtml側でlistRowsの変数を受け取れます。
これで準備完了です。
右の上の、「デプロイ」「新しいデプロイ」をクリック。
「種類の選択」から「ウェブアプリ」をクリック
説明文と、アクセスできるユーザーを選択したらデプロイボタンを押します。
WEBアプリのURLができるのでアクセスします。
実行ボタンを押すと、スクリプトが実行されてPDFの生成が無限にできます。
おわりに
今回初めてGASを使って業務効率化の開発をしてみました。
業務でよくスプレッドシートは使うし、Google関連のサービスもよく使うので勉強して自動化処理を取り入れていこうと思います。
GASの勉強には事務職たらこ(@taraco1230)さんが出されているUdemyの教材がわかりやすくとても参考になりました。
Google Apps Script（GAS）の基礎を完全習得 -初心者歓迎-【爆速で習得しちゃおう】
6分の壁を超えて無限に処理する方法は以下の記事を参考にさせていただきました。
制限時間をこえて6分以上GASを実行する方法【Google Apps Script】
応援クリックよろしくお願いします！
