GAS 請求書PDFを作成する
参考にしたURL
参考、写経、改変させていただいた。
今回やりたいこと
スプレッドシートからポチっとやってPDFを作成したい。
設計思想
メール下書きツールができたので、それを応用して、メール下書きする部分の処理をPDF作成に置き換えれば良いのでは？という着想。
PDFの作成方法は参考URLを写経、改変し、PDF作成部分は別の関数として呼び出すという構造にした。
フォルダ・ファイル・シートの構成
こんな感じ。テスト環境なので、請求管理台帳と生成されるPDFの置き場所を同じにしているが、本番ではフォルダIDで適当に調整すればいいかな～。
シートは、データの差し込み元となるシートと、データが差し込まれる請求書フォーマットのシートに分かれている。請求書の見栄えは本番環境ではもうちょとなんとかする。上図はわかりやすいように、配列とrangeの数字を下のほうに振ってある。このあたりは今後スマートで良い方法を模索していきたい。
コード
例によって、まずは動けば良いという愚直コードになっております。
function createPdf については、参考URLからそのまま使わせていただきました。
/**
* スプレッドシートから、「請求台帳」シートのデータを「請求書フォーマット」シートに差し込んでPDFを作成する。
*/
function savePdf() {
/* スプレッドシート本体とシートの定義 */
const ss = SpreadsheetApp.getActiveSpreadsheet()//コンテナバインドでactiveなスプレッドシート本体。
const dataSheet = ss.getSheetByName('請求台帳');//dataSheetの定義 請求台帳のシートとする。
const pdfSheet = ss.getSheetByName('請求書フォーマット');//dataSheetの定義 請求書フォーマットのシートとする。
/* PDF作成用でデータ定義 */
const ssId = ss.getId();//PDF作成用のスプレッドシート本体のIDを取得する。
const shId = pdfSheet.getSheetId();//PDF作成用のシートIDを取得する。
const folderId = "your ID";//PDFの保存先★フォルダーIDを入力してください★
/* 請求書フォーマット 差し込みデータの初期化（データを消す） */
pdfSheet.getRange(3, 2).setValue("");//御中
pdfSheet.getRange(6, 5).setValue("");//請求番号
pdfSheet.getRange(7, 5).setValue("");//請求日
pdfSheet.getRange(8, 5).setValue("");//請求金額
pdfSheet.getRange(9, 5).setValue("");//入金期限
pdfSheet.getRange(10, 5).setValue("");//内容
SpreadsheetApp.flush();//初期化setをApplies all pending Spreadsheet changes.
const ui = SpreadsheetApp.getUi();
const response = ui.alert('請求書PDFを作成しますか？', ui.ButtonSet.YES_NO);
let j = 0; //請求書PDF作成の有無カウント用
if (response == ui.Button.YES) {
ui.alert('請求書PDFを作成中です。そのままお待ちください。\n\n★シートを触らないでね！');
/* 値の取得・投稿したい内容をスプシから取得 */
const lastRow = dataSheet.getLastRow();
/* 作成するかしないかの判定 */
for (let i = 2; i <= lastRow; i++) {
if (dataSheet.getRange(i, 1).getValue() === "請求書を作成する") {
/* 請求書PDFに差し込む各項目を取得 */
const values = dataSheet.getRange(i, 1, 1, 10).getValues();
const billingNumber = values[0][1]; //請求番号
let billingDate = values[0][2]; //請求日
billingDate = Utilities.formatDate(billingDate, "JST", "yyyy年MM月dd日");//日付の表示形式変換
const billTo = values[0][3];//請求先
const billContent = values[0][4];//請求内容
const billingAmount = values[0][5];//請求金額
let paymentDeadline = values[0][8];//入金期限
paymentDeadline = Utilities.formatDate(paymentDeadline, "JST", "yyyy年MM月dd日");//日付の表示形式変換
/* 請求書フォーマットへ値をset */
pdfSheet.getRange(3, 2).setValue(billTo);//御中
pdfSheet.getRange(6, 5).setValue(billingNumber);//請求番号
pdfSheet.getRange(7, 5).setValue(billingDate);//請求日
pdfSheet.getRange(8, 5).setValue(billingAmount);//請求金額
pdfSheet.getRange(9, 5).setValue(paymentDeadline);//入金期限
pdfSheet.getRange(10, 5).setValue(billContent);//内容
SpreadsheetApp.flush();//setした内容をApplies all pending Spreadsheet changes.
/* 関数createPdfを実行し、PDFを作成して保存する */
createPdf(folderId, ssId, shId, billingNumber);
/* 作成後の処理 */
dataSheet.getRange(i, 1).setValue("請求書作成済");//下書きが作成できたら、H列は「下書き作成済」とセットする。
j += 1;
}
}
if (j > 0) {
ui.alert(`請求書PDFを ${j}件 作成しました。\n\nこのスクリプト終了後に、自分でフォルダを開いて確かめてください。\n\nすぐに下書きが反映されないときは、更新ボタンを押すか、しばしお待ちください（30秒～1分程度）`);
} else {
ui.alert(`「請求書を作成する」の指定が 0件 のようです。\n請求書PDF作成をキャンセルします。`);
}
}
else {
ui.alert('請求書PDF作成をキャンセルしました。');
}
}
//PDFを作成し指定したフォルダーに保存する関数
//以下4つの引数を指定する必要がある
//1: フォルダーID (folderId)
//2: スプレッドシートID (ssId)
//3: シートID (shId)
//4: ファイル名 (billingNumber)
function createPdf(folderId, ssId, shId, billingNumber) {
//PDFを作成するためのベースとなるURL
let baseUrl = "https://docs.google.com/spreadsheets/d/"
+ ssId
+ "/export?gid="
+ shId;
//★★★自由にカスタマイズしてください★★★
//PDFのオプションを指定
let pdfOptions = "&exportFormat=pdf&format=pdf"
+ "&size=A4" //用紙サイズ (A4)
+ "&portrait=true" //用紙の向き true: 縦向き / false: 横向き
+ "&fitw=true" //ページ幅を用紙にフィットさせるか true: フィットさせる / false: 原寸大
+ "&top_margin=0.50" //上の余白
+ "&right_margin=0.50" //右の余白
+ "&bottom_margin=0.50" //下の余白
+ "&left_margin=0.50" //左の余白
+ "&horizontal_alignment=CENTER" //水平方向の位置
+ "&vertical_alignment=TOP" //垂直方向の位置
+ "&printtitle=false" //スプレッドシート名の表示有無
+ "&sheetnames=false" //シート名の表示有無
+ "&gridlines=false" //グリッドラインの表示有無
+ "&fzr=false" //固定行の表示有無
+ "&fzc=false" //固定列の表示有無;
//PDFを作成するためのURL
let url = baseUrl + pdfOptions;
//アクセストークンを取得する
let token = ScriptApp.getOAuthToken();
//headersにアクセストークンを格納する
let options = {
headers: {
'Authorization': 'Bearer ' + token
}
};
//PDFを作成する
let blob = UrlFetchApp.fetch(url, options).getBlob().setName(billingNumber + '.pdf');
//PDFの保存先フォルダー
//フォルダーIDは引数のfolderIdを使用します
let folder = DriveApp.getFolderById(folderId);
//PDFを指定したフォルダに保存する
folder.createFile(blob);
}ハマったところ・大変だったところ
当初のコードでは、数字があわね～となっていた。PDFは生成できるんですよ、でもね、PDFのファイル名はA0001なのに、中身はA0004のものになる、というずれが生じていた。
ノンプロ研に助けを求めたところ、シンプルにしてテストだ！デバッグで変数チェックだ！とアドバイスをいただいた。
結果として、原因は複数あったが、
・大きな二つのコードを合体したことによる齟齬。
・初期化せずに前回の残りを取得していた。
・SpreadsheetApp.flush(); でApplies all pending Spreadsheet changes. していなかった。
ここが大きな点だったと思う。
forの中でシートの定義の必要はないので、頭にもってきたり、
デバッグで変数の動きや値は取れているのにPDF生成時には反映されていない、いや、シートの転記の時点で反映されていない？とデバッグで突き詰めていくところは大変勉強になった。
デバッグ
デバッグのやり方、いまの認識はこう。
デバッグ 1.ブレークポイントの設定
今回は最初に変数の動き、値がセットされる前後で怪しいところを見たいので、請求書フォーマットに値が差し込まれる前後にポチっとやって、ブレークポイントを設けた。
上図だと、34行と42行の左横に紫の丸が付いている。行の横にカーソルを合わせればぽちっとできる。
原因を探っていくうちに、PDF生成の前後などにもポイントを置いて動きを見たりした。
デバッグ 2.デバッグを押す
デバッグ 3.うまくデバッグできない
あれれ、6行目で止まっちまった。わからん。uiがシート側で「はい」を押さないといけないようだ。デバッグに邪魔なので、コメントアウトするなどした。
デバッグ 4.ミニマムでデバッグ用のコードを用意する。
どこまでミニマムにすればいいかわからんで、まずはui系統をコメントアウトにしたデバッグ用のスクリプトを動かしてみた。
デバッグではなくて、まずは実際にどんなPDFが作成されるか何パターンか試したところ、どうも変数iの値がおかしいような気がする。しかし、これは後からわかったことだが、変数は合っていた。
あ～～～～～～う～～～～～～ん～～～～ここからどう見ていって、原因突き止めればいいんだ？？？？
デバッグ 5.うんうん悩む
ここでiは3 うん、それはあってる。あってるよね？もう何もわからなくなりつつある。
createPdfを呼び出してるのがアカンのか？もうそのまま中に入れちゃう？
あ～だこ～だと確認しているうちに、変数はOKなのだが、二回目にforが回って値をsetされるべきところが反映されていないことがわかった。
なんで？
シートの定義？何度もforの中で回す意味のないものは最初ほうで定義しておく。
初期化も重要だ。残っているデータを読み取って、気が付かずにずれていたなんてことも。
しかし、コンソールログやデバッグ上は値が取れているのに、シートにセットされない、ずれる、意味が分からない。
シートの定義か？
メール下書きとPDF作成をがっしゃんこして大きくなっているので、部品に分解して、それぞれのシンプルな動きを確認し、組み立て直すのが良さそう。
このあたりは、ノンプロ研TAのYさんに夜分遅くにdiscordに付き合っていただき、大変勉強になった。感謝してもしきれない。
デバッグ 6.各パーツ点検、ミニマム化、シンプル化
改めて見直していくと、そもそも、特定のシートを正しく取得できていないようだった。
ド頭で定義ができていなかった。ショック。
改めてSpreadsheetオブジェクト・Sheetオブジェクト・SpreadsheetAppを確認する。
GAS本で言うと、246ページあたりから。
今回はコンテナバインドなので、SpreadsheetApp.getActiveSpreadsheet() でいいはずである。そののち、それぞれのシートを定義すればいいはず。
const ss =SpreadsheetApp.getActiveSpreadsheet()//コンテナバインドでactiveなスプレッドシート本体
console.log(ss.getName());うん、これは合ってる。次はシートだ。
const dataSheet = ss.getSheetByName('請求台帳');//dataSheetの定義 請求台帳とする。
console.log(dataSheet.getName());うん、合ってる。なんでさっきエラー出た？？？
const ss = SpreadsheetApp.getActiveSpreadsheet()//コンテナバインドでactiveなスプレッドシート本体
console.log(ss.getName());
/*シートを取得する*/
const dataSheet = ss.getSheetByName('請求台帳');//dataSheetの定義 請求台帳とする。
console.log(dataSheet.getName());
const pdfSheet = ss.getSheetByName('請求書フォーマット');//dataSheetの定義 請求台帳とする。
console.log(pdfSheet.getName());うん、全部あってる。さっきはなぜ.......。
いったん、この状態で動かして、PDFが生成されることを確認した。
次は初期化だ。
ここで、SpreadsheetApp.flush(); が入っていなかったからでは？とアドバイスを受ける。flush 初耳だ。
お、お、お、お前だったのか～～～～必要だったのは～～～～～～！！！！！
Applies all pending Spreadsheet changes. ですよ。つよい。
デバッグ 7.再度組み立て
あとはなんやかんやして、パーツごとにオーバーホールして、各部品の動きをチェックして、ガタツキを直しつつ組み立て。この時に冗長なところ削り、同類項で括れるところはまとめたり、forの中で何度も定義する必要のないものは頭に持ってきたり。定義したものを呼び出した時の間違いがないか再チェック。
完成
は～～～、まずは、当初狙った動きをするスクリプトになった～～～～～。
仕組み的な部分は出来たので、あとは実戦用にカスタマイズしていくか。
おつかれ～～～～～！
今後の改善点
コードの組み立てにおけるKiss
Keep it simple stupid.
当初、雑に二つのおおもとのコードを合体しただけだったので、意味のないletやforの中でのシートの定義など、不整合で美しくない部分があった。また、大元となるシートの定義が怪しい部分があったので反省。全体として、きちんと整合させて、流れに沿って処理を動かす必要がある。
上位概念の獲得と実装
キープロパティで見出し行を取得すれば列の入れ替えにも対応できるらしい（具体的なこと、仕組み、どのように実際にコードに書くのかは現時点で不明）。上位概念を獲得してリファクタリングしたい。
処理時間
さくっと動いて、負荷の少ないものにしたい。
6分の壁もだんだん現実的にこわくなってきた。
いまは少ない件数だからなんとかなってるけど。
まずは、動いたので、ヨシ！
いただいたサポートで、書籍代や勉強費用にしたり、美味しいもの食べたりします！