2022年6月20日月曜日

無料で簡単に管理できる請求書等の受領システム（電子取引の保存要件に対応）

電子帳簿保存法の改正により、電子取引を電子データとして保存することが義務化されました。しかしながら、電子取引による請求書の発行も受領も月数件なので対応にコストをかけたくないというのが正直なところ……。

ということで、電子取引の保存要件に対応した請求書等の発行システムに続きまして、Google フォーム、スプレッドシート、GAS (Google Apps  Script) を利用して請求書等の受領システムを作成します。


受領システムについても、以下の3つの条件を満たす検索機能が必要となります。
  1. 取引年月日その他の日付、取引金額及び取引先を検索の条件として設定することができること。
  2. 日付又は金額に係る記録項目については、その範囲を指定して条件を設定することができること。
  3. 二以上の任意の記録項目を組み合わせて条件を設定することができること。
（国税庁「電子帳簿保存法一問一答【電子取引関係】」より）


主な機能は2つです。

① 請求書等受領機能

「請求書等（PDFファイル）を Google フォームからドライブへアップロード」（手動）
　　　↓
「ファイル名の形式を変更して統一」（自動）
　　　↓
「取引先履歴の保存」（自動）


② 検索機能

「取引履歴」を1～3の条件で検索できるようにします。


Google フォームをまるっと共有しようとしたところ、「閲覧者」としての共有はできない仕様みたいなので、以下いちからの作り方です。


▼ フォームの作成



まずは Google フォームを作成します。質問項目は4つです。

① 受領書類のアップロード（ファイルのアップロード）
「特定のファイル形式のみ許可」で PDF のみチェックしておきます。

② 金額（記述式テキスト（短文回答））
回答の検証を入れておきます。「正規表現」の「一致する」で ^(0|[1-9]\d*)$ とか、「数値」の「整数」でいいかと思います。

③ 取引先（プルダウン）
取引先の一覧を選択肢として作っておきます。数が多いようであればスプレッドシートから読み込むようにしてもいいかもしれません（こちら参照）。

④ 種別（ラジオボタン）
請求書、納品書、見積書、領収書等を選択肢として作っておきます。

全て必須項目です。

ファイルのアップロード先となるフォルダが自動的に作成されます。回答先となるスプレッドシートも作成しておきます。


▼ スプレッドシートの作成


フォームの回答先として作成されたスプレッドシートに手を加えます。

自動で作成される「フォームの回答 1」シートの他に「取引履歴」という名前でシートを追加します。

項目は「管理番号（A列）」「受領日（B列）」「金額（C列）」「取引先（D列）」「種別（E列）」「URL（F列）」となります。


▼ ファイルアップロード部分の GAS の説明


トリガーとして「フォーム送信時」を使うため、フォームからスクリプトを作成します。

まずはファイルのアップロード部分の GAS になります。

function getForm(e) {
  //回答を取得
  let itemResponses = e.response.getItemResponses();

  //ファイル名の生成
  let date = new Date();
  date = Utilities.formatDate(date, "JST", "yyyyMMdd");
  let amt = itemResponses[1].getResponse(); //金額
  let sup = itemResponses[2].getResponse(); //取引先
  let typ = itemResponses[3].getResponse(); //種別
  let fileName = date + '_' + sup + '_' + amt + '_' + typ;
  
  //アップロードファイルを取得
  let file = itemResponses[0];

  //ファイル名の変更
  let pdf = DriveApp.getFileById(file.getResponse());
  pdf.setName(fileName);

  //取引履歴の記録
  setTrHis(pdf);

  //マイドライブ下にアップロードされたファイルの削除
  dltMyFile();
}

//取引履歴の記録
function setTrHis(pdf) {
  Utilities.sleep(3 * 1000);

  const sp = SpreadsheetApp.openById("スプレッドシート ID");
  const formSh = sp.getSheetByName("フォームの回答 1");
  const hisSh = sp.getSheetByName("取引履歴");
  let formLastRow = formSh.getLastRow();
  let hisLastRow = hisSh.getLastRow();

  //ファームから回答の取得
  let formData = formSh.getRange(formLastRow, 1, 1, 5).getValues();
  formData = Array.prototype.concat.apply([], formData); //二次元配列を一次元配列へ

  //取引履歴の記録
  let hisData = [];
  let num = "";
  if(hisLastRow == 1) {
    num = 1;
  } else {
    num = hisSh.getRange(hisLastRow, 1).getValue() + 1;
  }
  hisData.push(num);
  hisData.push(Utilities.formatDate(formData[0], "JST", "yyyy/MM/dd"));
  hisData.push(formData[2]);
  hisData.push(formData[3]);
  hisData.push(formData[4]);
  hisData.push(pdf.getDownloadUrl());
  hisSh.getRange(hisLastRow + 1, 1, 1, 6).setValues([hisData]);
}

//マイドライブ下にアップロードされたファイルの削除
function dltMyFile() {
  const myDrive = DriveApp.getRootFolder();
  let file = myDrive.getFiles().next();
  file.setTrashed(true);
}
※ アップロード当日に検索されない不具合を修正しました。（2022-7-14）

31行目の「スプレッドシート ID」を書き換えてください。

アップロードされたファイルの名前は「日付_取引先_金額_種別.pdf」という形式になります。

指定したフォルダ以外にマイドライブ直下にもファイルがアップロードされるようなのでそちらは dltMyFile 関数で削除（ゴミ箱に移動）しています。マイドライブ直下に他のファイルがある場合はあらかじめ調整してください。

トリガーとして「フォーム送信時」を設定します。


これで、請求書等を受領した際にフォーム経由でドライブにアップロードすることでファイル名が統一フォーマットになり、取引履歴が記録されます。


▼ 検索部分の GAS の説明


次に検索部分の GAS です。取引履歴から検索します。

const sp = SpreadsheetApp.openById("スプレッドシート ID");
const hisSh = sp.getSheetByName("取引履歴");

let result = [];

function doGet() {
  let htmlIndex = HtmlService.createTemplateFromFile("index");

  htmlIndex.dataNum = dataNum();
  htmlIndex.dateList = dateList().join("");
  htmlIndex.csmList = csmList().join("");
  htmlIndex.result = result;

  return htmlIndex.evaluate().setTitle("受領済請求書等検索システム");
}

function doPost(e) {
  let y_from = e.parameter.y_from;
  let m_from = e.parameter.m_from;
  let d_from = e.parameter.d_from;
  let y_to = e.parameter.y_to;
  let m_to = e.parameter.m_to;
  let d_to = e.parameter.d_to;
  let amt_low = e.parameters.amt_low;
  let amt_up = e.parameters.amt_up;
  let csm = e.parameter.csm;
  let typ = e.parameter.typ;

  let htmlResult = HtmlService.createTemplateFromFile("index");

  let rowNum = []; //検索結果行

  //全ての行を取得
  for(let i = 2; i <= hisSh.getLastRow(); i++) {
    rowNum.push(i);
  }
  
  //種別→検索順1
  if(typ != "全選択") {
    let typFinder = hisSh.getRange("E2:E").createTextFinder(typ).findAll();
    for(let i in typFinder) {
      rowNum.push(typFinder[i].getRow());
    }
    //重複要素のみ残す
    rowNum = rowNum.filter(function (value, index, self) {
      return self.indexOf(value) === index && index !== self.lastIndexOf(value);
    });
  }

  //取引先→検索順2
  if(csm != "全選択") {
    let csmFinder = hisSh.getRange("D2:D").createTextFinder(csm).findAll();
    for(let i in csmFinder) {
      rowNum.push(csmFinder[i].getRow());
    }
    //重複要素のみ残す
    rowNum = rowNum.filter(function (value, index, self) {
      return self.indexOf(value) === index && index !== self.lastIndexOf(value);
    });
  }

  //金額→検索順3
  let amt = "";
  //下限
  if(amt_low != "") {
    for(let i in rowNum) {
      amt = hisSh.getRange(rowNum[i], 3).getValue();  
      //下限以下の行を削除
      if(amt >= amt_low) {
        rowNum.push(rowNum[i]);
      }
    }
    //重複要素のみ残す
    rowNum = rowNum.filter(function (value, index, self) {
      return self.indexOf(value) === index && index !== self.lastIndexOf(value);
    });
  }
  //上限
  if(amt_up != "") {
    for(let i in rowNum) {
      amt = hisSh.getRange(rowNum[i], 3).getValue();  
      //上限以上の行を削除    
      if(amt <= amt_up) {
        rowNum.push(rowNum[i]);
      }
    }
    //重複要素のみ残す
    rowNum = rowNum.filter(function (value, index, self) {
      return self.indexOf(value) === index && index !== self.lastIndexOf(value);
    });
  }

  //期間→検索順4
  let dtDate = "";
  let date = "";
  //期間・始まり
  date = new Date(y_from, m_from - 1, d_from);
  for(let i in rowNum) {
    dtDate = hisSh.getRange(rowNum[i], 2).getValue();  
    //開始日以前の行を削除    
    if(dtDate.getTime() >= date.getTime()) {
      rowNum.push(rowNum[i]);
    }
  }
  //重複要素のみ残す
  rowNum = rowNum.filter(function (value, index, self) {
    return self.indexOf(value) === index && index !== self.lastIndexOf(value);
  });
  //期間・終わり
  date = new Date(y_to, m_to - 1, d_to);
  for(let i in rowNum) {
    dtDate = hisSh.getRange(rowNum[i], 2).getValue(); 
    //終了日以降の行を削除    
    if(dtDate.getTime() <= date.getTime()) {
      rowNum.push(rowNum[i]);
    }
  }
  //重複要素のみ残す
  rowNum = rowNum.filter(function (value, index, self) {
    return self.indexOf(value) === index && index !== self.lastIndexOf(value);
  });

  rowNum.sort((a, b) => {return a - b;});

  result.push("<p><b>期間： </b>"+ Utilities.formatDate(new Date(y_from, m_from - 1, d_from), "JST", "yyyy/MM/dd") + " から " + Utilities.formatDate(new Date(y_to, m_to - 1, d_to), "JST", "yyyy/MM/dd") + " まで<br><b>金額： </b>" + amt_low + "円 以上 " + amt_up + "円 以下<br><b>取引先： </b>" + csm + "<br><b>種別： </b>" + typ + "</p><br>"); //検索条件
  result.push("<table><tr><th>管理番号</th><th>受領日</th><th>金額</th><th>取引先</th><th>種別</th><th>ファイル</th></tr>");
  for (let i = 0; i < rowNum.length; i++) {
    result.push("<tr><td>" + hisSh.getRange(rowNum[i], 1).getValue() + "</td>");
    result.push("<td>" + Utilities.formatDate(new Date(hisSh.getRange(rowNum[i], 2).getValue()), "JST", "yyyy/MM/dd") + "</td><td>");
    result.push(Array.prototype.concat.apply([], hisSh.getRange(rowNum[i], 3, 1, 3).getValues()).join("</td><td>"));
    result.push("<td><a href='" + hisSh.getRange(rowNum[i], 6).getValue() + "'>ダウンロード</a></td><td>");
    result.push("</td></tr>");
  }
  result.push("</table>");

  htmlResult.dataNum = dataNum();
  htmlResult.dateList = dateList().join("");
  htmlResult.csmList = csmList().join("");
  htmlResult.result = result.join("");

  return htmlResult.evaluate().setTitle("検索結果 - 受領済請求書等検索システム");
}

//登録数の作成
function dataNum() {
  let dataNum = 0;
  let lastRow = hisSh.getLastRow();
  for(let i = 2; i <= lastRow; i++){
    dataNum++;
  }
  return dataNum;
}

//期間の作成
function dateList() {
  let list = [];
  let date = "";
  //始まり（年）
  list.push("<select name='y_from'>");
  for(let i = 0; i < 8; i++) {
    date = new Date();
    date = Utilities.formatDate(new Date(date.setFullYear(date.getFullYear() - i)), "JST", "yyyy");
    if(i == 1) {
      list.push("<option value='" + date + "' selected>" + date + "</option>");
    } else {
      list.push("<option value='" + date + "'>" + date + "</option>");
    }
  }
  list.push("</select>" + "年 ");
  //始まり（月）
  list.push("<select name='m_from'>");
  for(let i = 0; i < 12; i++) {
    date = new Date();
    date = Utilities.formatDate(new Date(date.setFullYear(date.getFullYear() - i)), "JST", "MM");
    if((i + 1) == date) {
      list.push("<option value='" + (i + 1) + "' selected>" + (i + 1) + "</option>");
    } else {
      list.push("<option value='" + (i + 1) + "'>" + (i + 1) + "</option>");
    }
  }
  list.push("</select>" + "月 ");
  //始まり（日）
  list.push("<select name='d_from'>");
  for(let i = 0; i < 31; i++) {
    date = new Date();
    date = Utilities.formatDate(new Date(date.setFullYear(date.getFullYear() - i)), "JST", "dd");
    if((i + 1) == date) {
      list.push("<option value='" + (i + 1) + "' selected>" + (i + 1) + "</option>");
    } else {
      list.push("<option value='" + (i + 1) + "'>" + (i + 1) + "</option>");
    }
  }
  list.push("</select>" + "日 から ");

  //終わり（年）
  list.push("<select name='y_to'>");
  for(let i = 0; i < 8; i++) {
    date = new Date();
    date = Utilities.formatDate(new Date(date.setFullYear(date.getFullYear() - i)), "JST", "yyyy");
    if(i == 0) {
      list.push("<option value='" + date + "' selected>" + date + "</option>");
    } else {
      list.push("<option value='" + date + "'>" + date + "</option>");
    }
  }
  list.push("</select>" + "年 ");
  //終わり（月）
  list.push("<select name='m_to'>");
  for(let i = 0; i < 12; i++) {
    date = new Date();
    date = Utilities.formatDate(new Date(date.setFullYear(date.getFullYear() - i)), "JST", "MM");
    if((i + 1) == date) {
      list.push("<option value='" + (i + 1) + "' selected>" + (i + 1) + "</option>");
    } else {
      list.push("<option value='" + (i + 1) + "'>" + (i + 1) + "</option>");
    }
  }
  list.push("</select>" + "月 ");
  //終わり（日）
  list.push("<select name='d_to'>");
  for(let i = 0; i < 31; i++) {
    date = new Date();
    date = Utilities.formatDate(new Date(date.setFullYear(date.getFullYear() - i)), "JST", "dd");
    if((i + 1) == date) {
      list.push("<option value='" + (i + 1) + "' selected>" + (i + 1) + "</option>");
    } else {
      list.push("<option value='" + (i + 1) + "'>" + (i + 1) + "</option>");
    }
  }
  list.push("</select>" + "日 まで");
  return list;
}

//取引先の作成
function csmList() {
  let list = [];
  let lastRow = hisSh.getLastRow();
  if(lastRow > 1) {
    let csm = hisSh.getRange(2, 4, lastRow -1, 1).getValues(); //全取引先を取得
    csm = Array.prototype.concat.apply([], csm); //二次元配列を一次元配列へ
    //重複要素を削除
    csm = csm.filter(function (value, index, self) {
      return self.indexOf(value) === index;
    });
    for(let i = 0; i < csm.length; i++){
      list.push("<option value='" + csm[i].replace("'", "&#039;") + "'>" + csm[i].replace("'", "&#039;") + "</option>");　//シングルクオーテーションがあった場合はHTML特殊文字に変換
    }
  }
  return list;
}

1行目の「スプレッドシート ID」を書き換えてください。


HTML も作ります。ファイル名は index.html とします。

<!DOCTYPE html>
<html>
  <head>
    <base target="_top"></base>
  </head>
  <body>
    <center><h1><a href="ウェブアプリ URL">受領済請求書等検索システム</a></h1></center>
    <p>
      登録数： 件
    </p>
    <form action="ウェブアプリ URL" method="post">
      <p><b>＜期間＞</b><br />
        
      </p>
      <p><b>＜金額＞</b><br />
        <input name="amt_low" size="15" type="text" />円 以上 <input name="amt_up" size="15" type="text" />円 以下
      </p>
      <p><b>＜取引先＞</b><br />
        <select name="csm">
          <option value="全選択">全選択</option>
          
        </select>
      </p>
      <p><b>＜種別＞</b><br />
　      <input checked="checked" name="typ" type="radio" value="全選択" />全選択
        <input name="typ" type="radio" value="請求書" />請求書
        <input name="typ" type="radio" value="納品書" />納品書
        <input name="typ" type="radio" value="見積書" />見積書
        <input name="typ" type="radio" value="領収書" />領収書
      </p>
      <p>
        <input type="submit" value="検索" />
      </p>
    </form>
    <p>
      
    </p>
  </body>
</html>

7行目 <a> タグ、11行目 <form> タグの「ウェブアプリ URL」を書き換えてください。

ウェブアプリ URL は GAS をウェブアプリとしてデプロイすると得られます。公開はしないのでアクセスできるユーザーは「自分のみ」としておきます。


検索画面はこんな感じになります。



＊＊＊

必要があればお使いください。カスタマイズもご自由にどうぞ。


・関連投稿
無料かつ時短になる請求書等の発行システム（電子取引の保存要件に対応）


詳解！ Google Apps Script完全入門 [第3版]
詳解！ Google Apps Script完全入門 [第3版]
by SimpleImageLink


Twitter（@nkkmd）日々更新中です。
By 時刻:
ラベル: , ,