請求書

GAS x freeeAPIライブラリのトリセツ「スプレッドシートから請求書を一括作成しよう」

2022年9月6日 freeeAPI

freeeの法人ベーシックプランまでだと取引のcsvインポート機能はありますが、請求書をcsvファイルから一括作成することはできません。

freeeで作成した請求書は、送付から消込までの流れがスムーズなので、ぜひ個別オプションで良いのでベーシックプランや個人プランでも請求書の一括作成ができるようになってほしいです。

願ってばかりいても実現しないので、freee APIでスプレッドシートから請求書を一括作成するスクリプトを作成しました。

事前準備

今回もfreee APIへのリクエストにはGAS x freeeAPIライブラリを使用します。こちらのライブラリの事前準備と使用にあたっての注意はこちらの記事をご確認ください。

GAS x freeeAPIライブラリのトリセツ「事前準備」

事前準備を終えた上で、必要になってくるのは、いつもの通りアクセストークンと操作対象の事業所IDです。

事業所IDの取得方法は以下で紹介しています。

GAS x freeeAPIライブラリのトリセツ「事業所IDを取得してみよう」

※ちょくちょくバグが見つかっているのでライブラリのバージョンもアップデートしています。ライブラリは常に最新バージョンでの使用をオススメします。

過去の請求書一覧をシートに取得する

まずはどんな構造のスプレッドシートを作成する必要があるかを確かめる意味でも、過去に発行した請求書のデータ一覧をサンプルとしてスプレッドシートに書き出したいと思います。

これもGAS x freeeAPIライブラリを使用すればシンプルなスクリプトで実現可能です。

function demoGetInvoices2Sheet1Year() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  new freeeAPI.invoices(accessToken, company_id).getInvoices2Sheet('請求書一覧（1年分)');
}

getInvoices2Sheet()メソッドの引数に指定した文字列と名称が一致するシートに過去1年分の請求書のデータが書き出されます。

請求書の発行数が多く、もう少し取得期間を絞り込みたい場合は、ライブラリから生成したinvoices操作オブジェクトのqueriesプロパテイ下にあるクエリパラメータへ、絞り込みに用いる値を代入します。

例えば前月1日を取得の起算日としたい場合は…

invoices_freeeAPI.queries.start_issue_date = '2022-08-01';

とします。

以下のコードでは、請求書一覧（先月分)という名前のシートに書き出してみました。

function demoGetInvoices2SheetFromLastMonth() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  const invoices_freeeAPI = new freeeAPI.invoices(accessToken, company_id);
  invoices_freeeAPI.queries.start_issue_date = '2022-08-01'; // yyy-MM-dd形式の前月1日の日付文字列
  invoices_freeeAPI.getInvoices2Sheet('請求書一覧（先月分)');
}

尚、getInvoices2Sheet()メソッドは、引数に指定した名称のシートが存在しない場合は新たに作成して書き出します。

書き出されたデータの表示形式を整える

書き出されたままのシートだと列幅や罫線などの設定がなく、中身が確認しづらいので、お好みで上部メニューから 表示形式 ＞ 交互の背景色 などで見やすいように表示形式を整えます。

視認性が良くなりました（ガチの生データのためほとんどがモザイクで申し訳ありません）。

必要なヘッダ項目だけに絞り込んだシート

過去の請求書データが書き出されたシートのヘッダ項目には、新たに請求書の作成をする時に不要な項目も多数含まれています。また新たな請求書作成時に必須ではないが設定可能な項目などもあります。

この過去の請求書データが書き出されたシートのヘッダ項目から、スプレッドシートからの請求書の作成に最低限必要なヘッダ項目のみを残して、別のシートに雛形を作成します。

請求書の作成に最低限必要であろう（必須ではないが実務上選択したほうが良いものも含む）項目は以下の通りになります。

  • 請求書番号
  • 請求日 (yyyy-MM-dd)
  • 期日 (yyyy-MM-dd)
  • 売上計上日
  • 概要
  • 取引先
  • 請求書に表示する取引先名
  • 敬称（御中、様、(空白)の3つから選択）
  • メッセージ
  • 請求書備考
  • 請求書レイアウト
  • 請求書の消費税計算方法
  • 順序
  • 行の種類
  • 勘定科目
  • 税区分
  • 品目
  • 備考
  • 数量
  • 単位
  • 単価
  • 小計
  • 消費税額
  • 部門
  • メモタグ

先程取得した過去の請求書データの一覧を書き出したシートから、請求書登録シートの雛形として使用できるように上記のヘッダ項目のみを抜粋します。

手動でシートをコピーした上で不要な列を削除しても良いですが、こういうケースはQUERY関数が便利です。

別のシートのA1セルに以下の数式をコピーすることで、請求書の作成に最低限必要なヘッダ項目の過去の入力データの例が確認できます。

=QUERY('請求書一覧（先月分)'!A:BU,"select F,C,H,L,M,N,V,W,X,AN,AO,AP,AQ,AT,AU,BD,BE,BG,BB,AV,AW,AX,AY,AZ,BI,BK")

スプレッドシートのQUERY関数は、データベースとしてスプレッドシートを操作する場合に非常に強力な関数になりますので、ぜひ以下の記事などを参考に習得をオススメします。

いつも隣にITのお仕事｜スプレッドシートのQUERY関数を使う最初の一歩！クエリを理解する
カワムラ｜QUERY関数でDBから抽出する[QUERY関数][Googleスプレッドシート]

スプレッドシートからの請求書の作成で設定可能なヘッダ項目すべてを列挙すると以下になります。順序以降は明細行の項目になります。

注意点としては、GAS x freeeAPIライブラリでは、ヘッダ項目の文字列からfreee APIで指定された請求書POST時のプロパティを変換しています。以下のヘッダ項目の表記を変更してしまうと（例：請求日 (yyyy-MM-dd) → 請求日）その項目のPOSTができず、エラーや登録抜けが発生します。

  • 事業所ID
  • 請求日 (yyyy-MM-dd)
  • 取引先
  • 取引先コード
  • 請求書番号
  • タイトル
  • 期日 (yyyy-MM-dd)
  • 売上計上日
  • 概要
  • 請求書ステータス
  • 請求書に表示する取引先名
  • 敬称（御中、様、(空白)の3つから選択）
  • 取引先担当者名
  • 取引先郵便番号
  • 取引先都道府県
  • 取引先市区町村・番地
  • 取引先建物名・部屋番号など
  • 事業所名
  • 事業所郵便番号
  • 事業所都道府県
  • 事業所市区町村・番地
  • 事業所建物名・部屋番号など
  • 事業所担当者名
  • 支払方法
  • 支払口座
  • 振込専用口座の利用
  • メッセージ
  • 請求書備考
  • 請求書レイアウト
  • 請求書の消費税計算方法
    • 順序
    • 行の種類
    • 数量
    • 単位
    • 単価
    • 消費税額
    • 備考
    • 勘定科目
    • 税区分
    • 品目
    • 部門
    • メモタグ
    • セグメント１ID
    • セグメント２ID
    • セグメント３ID

各種マスタを取得する

請求書登録用のシートには勘定科目などを指定する必要があります。これをミス無く指定するために、事前に各種のマスタデータをスプレッドシートに取得します。

この件に関しては、以下の記事で紹介しています。

GAS x freeeAPIライブラリのトリセツ「勘定科目・取引先・メモタグの等マスタ項目をスプレッドシートに取得しよう」

取り急ぎ請求書登録時に使用する頻度が高い、以下の項目のマスタをシートに書き出します。

  • 勘定科目
  • 取引先
  • 品目
  • 部門
  • メモタグ
  • 税区分
/* 勘定科目マスタシートに勘定科目をすべて取得する関数 */

function getAccountItemsMaster() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  freeeAPI.accountItems(accessToken, company_id).getAccountItems2Sheet('勘定科目マスタ');
}

/* 取引先マスタシートに取引先をすべて取得する関数 */

function getPartnersMaster() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  freeeAPI.partners(accessToken, company_id).getPartners2Sheet('取引先マスタ');
}

/* 品目マスタシートに品目をすべて取得する関数 */

function getItemsMaster() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  freeeAPI.items(accessToken, company_id).getItems2Sheet('品目マスタ');
}

/* 部門マスタシートに部門をすべて取得する関数 */

function getSectionsMaster() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  freeeAPI.sections(accessToken, company_id).getSections2Sheet('部門マスタ');
}

/* メモタグマスタシートにメモタグをすべて取得する関数 */

function getTagsMaster() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  freeeAPI.tags(accessToken, company_id).getTags2Sheet('メモタグマスタ');
}

/* 税区分マスタシートに税区分をすべて取得する関数 */

function getTaxesMaster() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  freeeAPI.taxes(accessToken, company_id).getTaxes2Sheet('税区分マスタ');
}

シートに書き出した後は、任意で交互の背景色などを設定し、見た目を整えます。

税区分マスタのみ、使用しない税区分が多数あり、そこから実務で使用する区分のみにフィルタリングすると後々便利です。この場合もQUERY関数を使うこともできます。

APIで取得した税区分の全データ

QUERY関数のWHERE句を利用して別シートでフィルタリングします。

=QUERY('税区分マスタ'!A:E,"select * where E = TRUE")

WHERE句の使い方に関しては以下の記事が参考になります。

いつも隣にITのお仕事｜【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する
QUERY関数でDBから抽出する(その2)[QUERY関数][Googleスプレッドシート]

無事フィルタリングできました。

請求書一括登録用のシートのフォーマットを整える

いよいよ請求書の一括登録シートの編集に進みます。

まず、1行目に先述した最低限必要なヘッダ項目をコピーしておきます。そして、各項目の入力がスムーズにすすむようにセルの表示形式の設定とデータの入力規則の設定を行います。

日付が入力される列はyyyy-MM-dd形式にする

まず日付が入力される列に関しては、yyyy-MM-dd形式の日時を表示させます。

GASとスプレッドシートの関係なのですが。スプレッドシート側のデータ型（日時か文字列か等）の違いで、取得されるオブジェクトの種類が変わる場合があります。日時を入力する予定のセルは、日時に準拠した表示形式にしておくと良いでしょう。

データの入力規則を指定して表記や入力の揺れを防ぐ

  • 勘定科目
  • 取引先
  • 品目
  • 部門
  • メモタグ
  • 税区分

といった項目は、マスタを別シートに書き出してあるので、その名称を請求書作成用に入力する際に表記ゆれや入力ミスが無いように、データの入力規則の機能を利用して、入力値を制限します。

取引先の入力を取引先マスタの取引先名に限定するためには、まず入力予定のセルを選択します。

続いて上部メニューからデータ > データの入力規則とすすみます。


条件 > 範囲または数式を入力の右側の格子をクリック。

勘定科目などマスタが整備されているデータ種別に関しては、表記ゆれは即エラーとなります。無効なデータの場合 > 入力を拒否を拒否 を設定しておくと良いでしょう。

これで準備は整いました。入力規則を設定したセルには▼があらわれ、取引先を指定することができます。

また選択したい取引先の頭文字をいくつか入力することで絞り込まれたサジェッションも表示されますので、スムーズに取引先を選択できます。

このデータの入力規則は、リストを手入力で直接指定することもできます。敬称（御中、様、(空白)の3つから選択）の項目などは、この機能で指定しておくと表記ゆれ発生しません。

注意いただきたいのが、メモタグの指定をする列です。メモタグはカンマ区切りで入力することで、複数付与することができますが、データの入力規則を設定してしまうと、複数指定時にエラーもしくは警告が表示されます。

2つ目以降のメモタグの選択はサジェッションも出ないので、警告を無視しつつ手入力で対応することになります。

無効なデータの場合：入力を拒否

に設定しているとメモタグの複数指定ができなくなるので、警告を表示を選択しましょう。

これで請求書登録用のシートの事前準備が整いました。

『重要』請求書番号が明細行をまとめるキー

GAS x freeeAPIライブラリを使用してスプレッドシートから請求書を一括作成する場合で、特に複数明細行がある請求書を作成する場合、注意点が2点あります。

① 以下のヘッダ項目は、明細行分入力必要が必要です。

  • 請求書番号
  • 請求日 (yyyy-MM-dd)
  • 期日 (yyyy-MM-dd)
  • 売上計上日
  • 概要
  • 取引先
  • 請求書に表示する取引先名
  • 敬称（御中、様、(空白)の3つから選択）
  • メッセージ
  • 請求書備考
  • 請求書レイアウト
  • 請求書の消費税計算方法

② デフォルトでは請求書番号が、複数の明細行を1つの請求書として取りまとめるキーとなります。

4件の請求書が作成される

任意の請求書番号を都度指定してもよいのですが、すでに与えられている情報から自動採番するのであれば、取引先のシステムIDと売上計上日を組み合わせることで、ユニークな番号が作成できるはずです。

取引先ID + "_" + 売上計上日

これは、同一計上日・同一顧客宛の請求書を複数作るケースが少ないという前提での運用です。同日の日付で同一の取引先に請求書を分割して作成する必要がある場合は、この運用は使えません。

取引先IDはfreeeのシステムIDを使用します。先だって取得した取引先マスタのシートからVLOOKUP関数で呼び出します。

=VLOOKUP(F2,{'取引先マスタ'!D:D,'取引先マスタ'!A:A},2,0)&"_"&D2

ここであれ？っと思われた方は流石です。通常VLOOKUP関数は、左側列にあるキーからの距離で値を取得するので、取引先名が取引先IDの右側にある場合は使えない…となるのですが、スプレッドシートは検索時の列順を関数内で入れ替えることができます。

詳しくは以下の記事がオススメです（感動します）。

カワムラ｜スプレッドシートでVlookup関数(左というか逆というか)

GASで登録した請求書を区別できるように「メモタグ：GAS登録」を作成しておく

freee APIによる請求書のPOST（登録）時の必須事項ではないですが、あらかじめGAS登録のような名前のメモタグを作成しておき、スプレッドシートからGASを使って登録した請求書がその他の請求書と区別できるようにしておきましょう。

メモタグを付与しておくと、後でfreeeの操作画面で検索や絞り込みができるので大変便利です。

概要に「freee APIで登録」とメモを入れておく

請求書に関しては、概要の項目に「freee APIで登録」と便利です。請求書の一覧画面では、先程紹介したメモタグでの検索や絞り込みやができないためです。

スプレッドシートで概要に「freee APIで登録」と入力しておくと
請求書一覧画面で絞り込みが可能

GAS x freeeAPIライブラリによる請求書の一括登録

長らくお疲れ様でした！これでスプレッドシートから請求書を一括作成する準備が整いました。後は、以下のシンプルなスクリプトを実行するだけで請求書が作成されます。

function demoPostInvoicesFromSheet() {
  const accessToken = getService().getAccessToken();
  const company_id = PropertiesService.getScriptProperties().getProperty('COMPANY_ID');
  const invoice_freeeAPI = new freeeAPI.invoice(accessToken, company_id);
  invoice_freeeAPI.postInvoicesFromSheet('請求書登録');
}

postInvoicesFromSheet(sheetName, groupKey)メソッドは、シート名で指定したシートの請求データから一括して請求書を作成するメソッドです。

おわりに

「GAS x freeeAPIライブラリを使ってスプレッドシートから請求書を一括作成しよう」をご紹介しました。GASやfreee APIの操作といっても50%くらいはスプレッドシートの機能の紹介でした。

今回のスクリプトでエラーが出るケースは、入力項目の抜け漏れや表記のゆれがほとんどです。エラーが出る場合は、まずスプレッドシートに取得した過去の請求書のデータをよく確認して、参考にしましょう。

Amazon欲しい物リスト公開しています。

開発者のモチベーションアップのためにAmazon欲しい物リストを公開しております。役に立ったよ！という方の感謝の気持ちで何かいただけるのであれば嬉しいです笑

Amazon欲しい物リスト

タグ: , ,
Share on:
Previous Post
チャット
GAS活用法

Chatworkのメッセージをスプレッドシートに保存しよう その1 – 最新の100件のメッセージを書き出す

2022年9月14日
Next Post
入り口看板
GAS活用法

Gmailのスター付きメールからTodoistのタスクを作成＆Slackに通知する その4 – Incoming Webhooksを使ってSlackに通知

2022年9月4日 No Comments