【老人ホームのDX】 インボイスに合わせて案件・入居管理→請求書発行の自動化をしてみた【GoogleSpreadsheet】
この記事は15,423文字（コード含む）あります。
経営管理は企業・組織の成長を左右する重大な業務ですが、システムを入れたら比較的簡単にDXできる勤怠管理、給与管理、精算管理、ワークフローなどと異なり、経営管理システム導入の優先度は低くなりがちです。
経営管理は「情報収集」→「集計・加工」「報告」という３つのプロセスで構成されますが、Excel等の手作業が多く介在するプロセスになりがちで、システム化されていてもその後の経営方針やKPIにフィットせず、レガシー化しているケースが散見されます。
インボイスで社内の請求書フォーマットを統一するタイミングを機に、既存の入居管理システムに請求書発行システムを組み合わせたところ業務効率が上がったので、そのナレッジを共有します。
コードに関する質問は回答しないと思います。
全体の構成図
全体のざっくりした構成はこんな感じです。
基幹システム
Google Workspace社内グループウェア
Google Chat
SFA兼CRM
ビジネスプロセスの観点から考えると、SFA（「営業支援システム」Sales Force Automation）の範囲はリードが案件化してから受注まで、CRM（「顧客関係管理」Customer Relationship Management）の範囲は受注後とそれぞれ別々に考えますが、入力業務を簡略化するためにスプレッドシート内の1つのシートの左半分をSFA、受注後（入居）は右半分をCRMとして運用しています。
各施設長が入居案件（入居確定後は入居者情報）を入力し、現在入居中の方の情報だけを別シートに抽出、ダッシュボードに集約しています。
見切れていますが、「集計」シートには以下の情報も毎月ごとに集計しており、施設の状況が一目でわかるようになっています。
入居者
入院分を差し引いた稼働率
入院中は介護保険報酬が発生しないので空室と同じ
居室数＝定員ではない（二人部屋がある）ためそれぞれを集計入退去
平均介護度
月4回集計した平均値
職員
常勤換算数
非正規雇用が多いため雇用人数ではなく当社の常勤規定でカウント職員稼働率
人員効率（介護サービス提供時間／勤務時間）離職率（常勤換算）
ダッシュボードはリアルタイムに更新されるため、GAS（GoogleApps Script）を使って毎日同時刻にGoogleドライブの指定フォルダへ自動保存しています。
function savePdf(){
//PDFの保存先
let folderId = "**************";//フォルダーID
//アクティブなスプレッドシートを取得する
let ss = SpreadsheetApp.openById("**************");//スプレッドシートID
//スプレッドシートIDを取得
let sheetName = '*************';//保存するシート名
let shId = ss.getSheetByName(sheetName).getSheetId();
let ssId = ss.getId();
//PDFのファイル名を「yyyy-MM-dd」として保存
let fileName = Utilities.formatDate(new Date(), "JST", "yyyy-MM-dd");
//関数createPdfを実行し、PDFを作成して保存
createPdf(folderId, ssId, shId, fileName);
}
//PDFを作成し指定したフォルダーに保存
function createPdf(folderId, ssId, shId, fileName){
//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=false" //用紙の向き 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" //グリッドラインの表示有無
+ '&range=A1%3AT26' //セル範囲を指定「%3A はコロン(:)を表す」
+ "&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(fileName + '.pdf');
//PDFの保存先フォルダー
//フォルダーIDは引数のfolderIdを使用
let folder = DriveApp.getFolderById(folderId);
//PDFを指定したフォルダに保存する
folder.createFile(blob);
}ダッシュボードはWEBページ化して、経営幹部がリアルタイムにモニタリングできるようにしています。
更にこれをLINEで営業に配信し、リード獲得に活用しています。
大まかにはLINE Developersで公開WEBページのLIFF（LINE Front-end Framework）アプリを作成し、公式LINEアカウンのリッチメニューからアクセスできるようにしています。
請求書
電帳法対応も見込んで、請求書の作成と自動保存が同時にできるようにしています。
請求書の入力と出力を別々のスプレッドシートとし、GASで出力シートの内容をPDF出力→GooleDrive指定フォルダに自動で保存、それを印刷する仕様にすることで、様式を改変されてしまうトラブルを未然に防いでいます。
居室番号から自動的に料金を計算できるように、各居室の料金データベースを別途作成しました。
請求書入力シートのメニューバーに請求書発行メニューを追加し、それぞれのメニューにGASを割り当てています。
また、請求書入力シートを開いたりリロードする際、入力するセル内の値を消去＆チェックボックスのチェックを外すことで毎回新規入力するので、入力ミスを未然に防いでいます。
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('請求書を発行')
.addItem('入居', 'saveAsPDFAndCopyValues')
.addItem('退去', 'saveAsPDFAndCopyValues1')
.addItem('標準', 'saveAsPDFAndCopyValues2')
.addToUi();
clearActiveSheetData(); // アクティブなシートをクリア
}
function clearActiveSheetData() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = spreadsheet.getSheetByName("入力@入居"); // 入居シートを取得
var sheet2 = spreadsheet.getSheetByName("入力@退去"); // 退去シートを取得
var sheet3 = spreadsheet.getSheetByName("入力@標準"); // 退去シートを取得
// 個別にセルをクリア（入居）
sheet1.getRange("C2:C3").clearContent();
sheet1.getRange("C5").clearContent();
sheet1.getRange("C36").clearContent();
sheet1.getRange("C38:C40").clearContent();
sheet1.getRange("B87:F96").clearContent();
sheet1.getRange("B100").clearContent();
sheet1.getRange("M87:M89").clearContent();
// チェックボックスのチェックを外す（入居）
var checkBoxRange1 = sheet1.getRange("D21:D22");
var checkBoxRange2 = sheet1.getRange("D24:D28");
var checkBoxRange3 = sheet1.getRange("D37");
var checkBoxRange4 = sheet1.getRange("E7");
var checkBoxRange5 = sheet1.getRange("E5");
var checkBoxValues1 = checkBoxRange1.getValues();
var checkBoxValues2 = checkBoxRange2.getValues();
var checkBoxValues3 = checkBoxRange3.getValues();
var checkBoxValues4 = checkBoxRange4.getValues();
var checkBoxValues5 = checkBoxRange5.getValues();
for (var i = 0; i < checkBoxValues1.length; i++) {
for (var j = 0; j < checkBoxValues1[i].length; j++) {
if (checkBoxValues1[i][j] === true) {
checkBoxRange1.getCell(i + 1, j + 1).setValue(false);
}
}
}
for (var i = 0; i < checkBoxValues2.length; i++) {
for (var j = 0; j < checkBoxValues2[i].length; j++) {
if (checkBoxValues2[i][j] === true) {
checkBoxRange2.getCell(i + 1, j + 1).setValue(false);
}
}
}
for (var i = 0; i < checkBoxValues3.length; i++) {
for (var j = 0; j < checkBoxValues3[i].length; j++) {
if (checkBoxValues3[i][j] === true) {
checkBoxRange3.getCell(i + 1, j + 1).setValue(false);
}
}
}
if (checkBoxValues4[0][0] === true) {
checkBoxRange4.setValue(false);
}
if (checkBoxValues5[0][0] === true) {
checkBoxRange5.setValue(false);
}
// 個別にセルをクリア（退去）
sheet2.getRange("C2:C3").clearContent();
sheet2.getRange("C5").clearContent();
sheet2.getRange("C36:C42").clearContent();
sheet2.getRange("B87:F96").clearContent();
sheet2.getRange("B100").clearContent();
sheet2.getRange("N86:N96").clearContent();
// チェックボックスのチェックを外す（退去）
var checkBoxRange1 = sheet2.getRange("E7");
var checkBoxRange2 = sheet2.getRange("D21:D28");
var checkBoxRange3 = sheet2.getRange("E22:E32");
var checkBoxValues1 = checkBoxRange1.getValues();
var checkBoxValues2 = checkBoxRange2.getValues();
var checkBoxValues3 = checkBoxRange3.getValues();
if (checkBoxValues1[0][0] === true) {
checkBoxRange1.setValue(false);
}
for (var i = 0; i < checkBoxValues2.length; i++) {
for (var j = 0; j < checkBoxValues2[i].length; j++) {
if (checkBoxValues2[i][j] === true) {
checkBoxRange2.getCell(i + 1, j + 1).setValue(false);
}
}
}
for (var i = 0; i < checkBoxValues3.length; i++) {
for (var j = 0; j < checkBoxValues3[i].length; j++) {
if (checkBoxValues3[i][j] === true) {
checkBoxRange3.getCell(i + 1, j + 1).setValue(false);
}
}
}
// 個別にセルをクリア（標準）
sheet3.getRange("C2:C5").clearContent();
sheet3.getRange("B9:F28").clearContent();
sheet3.getRange("B32").clearContent();
}請求書を発行時に複数の機能を追加しています。
GoogleDriveの指定フォルダに請求書のPDFを自動保存
保存が成功したらポップアップダイアログにPDFのリンクを表示→請求書を印刷
失敗したらエラーを通知→管理者が確認関係部署（経理など）のGoogleChatにBotで通知
発行した請求書の情報を一覧シートに追加
var file1, fileName1, file2, fileName2; // 変数を宣言しておく
function saveAsPDFAndCopyValues() {
var folderId = "**********"; // GoogleドライブのフォルダID
var spreadsheetId = "*********"; // スプレッドシートキー
var sheetName1 = "印刷@入居"; // 印刷@入居シート名
var sheetName2 = "入力@入居"; // 入力@入居シート名
var range1 = "A1:H51"; // 印刷@入居シートのセル範囲
var range2 = "B1:G100"; // 入力@入居シートのセル範囲
var timestamp = Utilities.formatDate(new Date(), "GMT+9", "yyyy/MM/dd_HH/mm/ss"); // 日時のフォーマットを変更
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
var sheet1 = spreadsheet.getSheetByName(sheetName1);
var sheet2 = spreadsheet.getSheetByName(sheetName2);
// 印刷@入居シートのPDF作成
var options1 = {
pageSize: 'A4',
landscape: false,
printTitleColumns: '',
printTitleRows: '',
sheetId: sheet1.getSheetId(),
range: range1
};
var url1 = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?exportFormat=pdf&format=pdf" +
"&gid=" + sheet1.getSheetId() +
"&size=A4" +
"&portrait=true" + // ページの向きを縦向きに設定
"&gridlines=false" +
"&printtitle=false" +
"&horizontal_alignment=CENTER" + //水平方向の位置
"&vertical_alignment=TOP" + //垂直方向の位置
"&fzr=false" + // 凍結行を非表示
"&top_margin=0.5" + // 上の余白
"&right_margin=0.5" + // 右の余白
"&bottom_margin=0.5" + // 下の余白
"&left_margin=0.5" + // 左の余白
"&scale=4" + // 1: 標準100% 2: 幅に合わせる 3: 高さに合わせる 4: ページに合わせる
"&range=" + encodeURIComponent(range1);
var pdfBlob1 = UrlFetchApp.fetch(url1, {headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}}).getBlob();
// 入力@入居シートのPDF作成
var options2 = {
pageSize: 'A4',
landscape: false,
printTitleColumns: '',
printTitleRows: '',
sheetId: sheet2.getSheetId(),
range: range2
};
var url2 = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?exportFormat=pdf&format=pdf" +
"&gid=" + sheet2.getSheetId() +
"&size=A4" +
"&portrait=true" + // ページの向きを縦向きに設定
"&gridlines=false" +
"&printtitle=false" +
"&horizontal_alignment=CENTER" + //水平方向の位置
"&vertical_alignment=TOP" + //垂直方向の位置
"&fzr=false" + // 凍結行を非表示
"&top_margin=0.5" + // 上の余白
"&right_margin=0.5" + // 右の余白
"&bottom_margin=0.5" + // 下の余白
"&left_margin=0.5" + // 左の余白
"&scale=4" + // 1: 標準100% 2: 幅に合わせる 3: 高さに合わせる 4: ページに合わせる
"&range=" + encodeURIComponent(range2);
var pdfBlob2 = UrlFetchApp.fetch(url2, {headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}}).getBlob();
// フォルダにPDFファイルを保存
var folder = DriveApp.getFolderById(folderId);
// 印刷@入居シートのPDFファイル名を設定
var values1 = sheet1.getRange(range1).getValues();
fileName1 = values1[3][0] + "_" + timestamp + "_" + values1[4][0] + "_" + sheet1.getRange("H47").getDisplayValue() + ".pdf"; // ファイル名を設定
file1 = folder.createFile(pdfBlob1.setName(fileName1));
// 入力@入居シートのPDFファイルを印刷@入居シートのPDFファイルに追加
var values2 = sheet2.getRange(range2).getValues();
fileName2 = values1[3][0] + "_" + timestamp + "_" + values1[4][0] + "_" + "入力シート" + ".pdf"; // ファイル名を設定
file2 = folder.createFile(pdfBlob2.setName(fileName2));
// 出力完了メッセージを表示
var dialogHtml = `
<p><a href="${file1.getUrl()}" target="_blank">${fileName1}</a></p>
<p><a href="${file2.getUrl()}" target="_blank">${fileName2}</a></p>
`;
var userInterface = HtmlService.createHtmlOutput(dialogHtml).setWidth(400).setHeight(200);
SpreadsheetApp.getUi().showModalDialog(userInterface, "請求書ファイルの出力が完了しました");
// 通知を送信
sendNotifications();
// 印刷データのコピーを実行
copyValuesBetweenSpreadsheets(timestamp);
}
function sendNotifications() {
var webhookUrls = [
'********************',
'********************'
]; // 通知するChatスペースのWebhookURL
var spreadsheetId = "***********"; // スプレッドシートキー
var sheetName = "印刷@入居"; // シート名
var range = "A1:H51"; // セル範囲
var timestamp = Utilities.formatDate(new Date(), "GMT+9", "yyyy/MM/dd_HH/mm/ss"); // 日時のフォーマットを変更
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
var sheet = spreadsheet.getSheetByName(sheetName);
var cellValue1 = sheet.getRange("A4").getValue(); // セルの値を取得
var cellValue2 = sheet.getRange("A5").getValue(); // セルの値を取得
var cellValue3 = sheet.getRange("H47").getDisplayValue(); // H47セルの表示されているテキストを取得
var chatMessage = '請求書@入居が発行されました。\n\n' +
cellValue1 + '\n' +
cellValue2 + '\n' +
cellValue3 + '\n\n' +
'*************'; // 保存フォルダのURL
for (var i = 0; i < webhookUrls.length; i++) {
sendNotificationToChat7(webhookUrls[i], chatMessage);
}
}
function sendNotificationToChat7(chatWebhookUrl, message) {
var options = {
'method': 'POST',
'headers': {
'Content-Type': 'application/json; charset=UTF-8'
},
'payload': JSON.stringify({
'text': message
})
};
UrlFetchApp.fetch(chatWebhookUrl, options);
}
function copyValuesBetweenSpreadsheets(timestamp) {
// 読み取るスプレッドシートのキー
var sourceSpreadsheetKey = "*************";
// 読み取るシート名
var sourceSheetName = "印刷@入居";
// 読み取るセル範囲
var sourceRangeA4 = "A4";
var sourceRangeA5 = "A5";
var sourceRangeH47 = "H47";
// 書き込むスプレッドシートのキー
var targetSpreadsheetKey = "**************";
// 書き込むシート名
var targetSheetName = "テスト";
// 読み取るスプレッドシートを開く
var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetKey);
// 書き込むスプレッドシートを開く
var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetKey);
// 読み取るシートを取得
var sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
// 書き込むシートを取得
var targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
// 読み取る値を取得
var valueA4 = sourceSheet.getRange(sourceRangeA4).getValue();
var valueA5 = sourceSheet.getRange(sourceRangeA5).getValue();
var valueH47 = sourceSheet.getRange(sourceRangeH47).getValue();
var valueG1 = timestamp; // タイムスタンプをセット
var valueG2 = 'ボナール'
// ユーザーの名前を取得
var userName = Session.getActiveUser().getEmail();
// 値を書き込むターゲットセルを検索
var targetRangeB2 = targetSheet.getRange("B2");
var targetRangeC2 = targetSheet.getRange("C2");
var targetRangeD2 = targetSheet.getRange("D2");
var targetRangeE2 = targetSheet.getRange("E2");
var targetRangeF2 = targetSheet.getRange("F2");
var targetRangeH2 = targetSheet.getRange("H2");
while (
targetRangeB2.getValue() !== "" ||
targetRangeC2.getValue() !== "" ||
targetRangeD2.getValue() !== "" ||
targetRangeE2.getValue() !== "" ||
targetRangeF2.getValue() !== "" ||
targetRangeH2.getValue() !== ""
) {
targetRangeB2 = targetRangeB2.offset(1, 0); // 次の行に移動
targetRangeC2 = targetRangeC2.offset(1, 0);
targetRangeD2 = targetRangeD2.offset(1, 0);
targetRangeE2 = targetRangeE2.offset(1, 0); // 次の行に移動
targetRangeF2 = targetRangeF2.offset(1, 0);
targetRangeH2 = targetRangeH2.offset(1, 0);
}
// 値を書き込む
targetRangeB2.setValue(valueA4);
targetRangeC2.setValue(valueA5);
targetRangeD2.setValue(valueH47);
targetRangeE2.setValue(file1.getUrl());
targetRangeF2.setValue(file2.getUrl());
targetRangeH2.setValue(userName);
var targetRangeA2 = targetSheet.getRange("A2");
while (targetRangeA2.getValue() !== "") {
targetRangeA2 = targetRangeA2.offset(1, 0); // 次の行に移動
}
targetRangeA2.setValue(valueG1);
var targetRangeG2 = targetSheet.getRange("G2");
while (targetRangeG2.getValue() !== "") {
targetRangeG2 = targetRangeG2.offset(1, 0); // 次の行に移動
}
targetRangeG2.setValue(valueG2)
}通知
こんな感じでかわいい秘書子さん（Bot）が担当者に請求書発行をChatで通知してくれます。
発行ログ
各施設や部署が発行した請求書発行ログを確認するスプレッドシートにも自動で反映します。
経理が入金確認したら一連の流れは完了です。
案件管理と入居者情報さえ入力しておけば、請求書に同じ宛名を入力したり、発行した請求書を他部署に共有したり、その他諸々の工数をカットできました。
入退去日と物件の居室番号に合わせて自動的に請求書額が表示されるので、誰でもミスなく簡単に使えるのがポイントです。
いいなと思ったら応援しよう！
