こんにちは！
デジタルを学び始めて、爆速で業務改善に挑んでいるおみえです。
今回、これまで１つ１つ処理していた書類作成から返信をまとめるという作業をこつこつと自動化させたお話です。
作業量がとにかく多い
私は、毎月定期的にお取引先様に契約書類を郵送し返信してもらい、各契約担当者へ連絡という作業をしています。平均、月に約１５契約、多い時には３０契約ある場合もあります。契約書類の為、最新の注意を払って作成しなければなりません。
送付リスト作成、書類作成、郵送、返信管理、PDF作成、リスト化、担当者へ連絡
と１つ１つ作業しておりこの作業にかなり時間を要していました。
デジタルを学び始めて書類作成をVBAにしてみたり、アプリへ反映させたりと少しづつ自動化を行っていましたが、ところどころ手作業は発生するものの、返信から連絡までを私は見守るだけという業務改善に挑みました。
自動化流れ
一連の処理の流れは以下になります。
各処理実装
１．スプレッドシートから今月処理が必要なリストを自動抽出（GAS）
契約の進捗確認はApp Sheetを使用して、各担当に管理させています。
App Sheetに表示する用のスプレッドシートから、Google Apps Script（GAS）を使用して今月処理が必要な分のみを抽出し別スプレッドシートリストとして保存。
ＧＡＳコードはこちら
function extractAndSaveForMonth() {
// スプレッドシートIDと保存フォルダID
var sourceSpreadsheetId = 'スプレッドシート名';
var folderId = 'フォルダID';
// 元のスプレッドシートを開く
var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
var sourceSheet = sourceSpreadsheet.getActiveSheet();
// メッセージボックスで月を聞く（キャンセルされた場合の処理を追加）
var inputMonth = Browser.inputBox('月を入力してください（形式: yyyy/MM）');
if (inputMonth === 'cancel') {
return; // キャンセル時は終了
}
// データ範囲を取得
var data = sourceSheet.getDataRange().getValues();
// 新しいスプレッドシートを作成
var newSpreadsheet = SpreadsheetApp.create(inputMonth);
var newSheet = newSpreadsheet.getActiveSheet();
// フィルタされたデータを格納する配列
var filteredData = [];
// ヘッダー行を追加 (A列からG列のみ)
filteredData.push(data[0].slice(0, 7));
// データをフィルタリングして抽出 (A列からG列まで)
for (var i = 1; i < data.length; i++) {
var contractType = data[i][2]; // C列
var dateValue = data[i][6]; // G列
// 日付が正しい形式かを確認
var date;
if (Object.prototype.toString.call(dateValue) === "[object Date]" && !isNaN(dateValue)) {
date = dateValue;
} else {
date = new Date(dateValue);
}
// 再契約かつG列が指定された月に一致するか確認
if (contractType === '再契約' && Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM') === inputMonth) {
// A列からG列までのデータを取得
var newRow = data[i].slice(0, 7);
filteredData.push(newRow);
}
}
// 抽出したデータを新しいシートに書き込む
if (filteredData.length > 1) {
newSheet.getRange(1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
} else {
Browser.msgBox('該当するデータがありませんでした。');
newSpreadsheet.delete(); // 該当データがない場合は新規スプレッドシートを削除
return;
}
// 保存先フォルダに移動
var folder = DriveApp.getFolderById(folderId);
var file = DriveApp.getFileById(newSpreadsheet.getId());
file.moveTo(folder);
Browser.msgBox('データが正常に保存されました。');
}
現在は手作業で契約月を入力し抽出していますが、月ベースのトリガー設定で作業日に合わせてリストを作成しておくに変更しようと思っています。
２．契約書類をWordデータ自動作成（VBA)
契約書類は社内規定の為、紙で発行しなければならないので一旦Excelファイルに変更します。
ExcelファイルからVBAを使用して、必要情報を別ファイルから抽出し、再度リスト化。
Wordの雛形データからリストにある全てのページを自動作成します。ついでに送付状も別Word雛形を作成しており自動作成します。合わせて、郵送先の宛名シールもリストの住所から自動作成されるようにVBAを実装しています。
転記する雛形のWordファイルには、転記したい項目ヘッダー部分を<<>>で指定しています。
詳細は過去に書いた記事に記載しています。
実はここもGoogleドキュメントにて書類を作成しようと思ったのですが、お取引先さまの情報が各種Excelにちらばっている為、現在はドキュメントで作成できないか検討中です。
ＶＢＡコードはこちら
Sub ExportDataToTemplate()
Dim ws As Worksheet
Dim wdApp As Object
Dim wdDoc As Object
Dim templatePath As String
Dim savePath As String
Dim lastRow As Long
Dim rowIndex As Long
Dim dateStr As String
Dim tempDocPath As String
Dim formattedDate As String
Dim formattedEndDate As String
' Set references to the Excel worksheet and Word application
Set ws = ThisWorkbook.Sheets("リスト")
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
' Define the path to the Word template
templatePath = ThisWorkbook.Path & "\template.docx"
' Verify that the template file exists
If Dir(templatePath) = "" Then
MsgBox "Template file not found: " & templatePath
Exit Sub
End If
' Find the last row with data in the worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
' Define the path for the temporary document
tempDocPath = ThisWorkbook.Path & "\temp.docx"
' Copy the template to a temporary file
FileCopy templatePath, tempDocPath
' Open the temporary file
Set wdDoc = wdApp.Documents.Open(tempDocPath)
' Copy the template content to be pasted later
wdDoc.Content.Copy
' Loop through each row of data
For rowIndex = 4 To lastRow
' Format the dates
formattedDate = Format(ws.Cells(rowIndex, 1).Value, "yyyy年m月d日")
formattedEndDate = Format(ws.Cells(rowIndex, 7).Value, "yyyy年m月d日")
' If not the first row, insert a page break and paste the template content
If rowIndex > 4 Then
Set rng = wdDoc.Content
rng.Collapse Direction:=0 ' wdCollapseEnd
rng.InsertBreak Type:=7 ' wdPageBreak
rng.Paste
End If
' Find the placeholders and replace them with the actual data
With wdDoc.Content
.Find.ClearFormatting
.Find.Replacement.ClearFormatting
.Find.Execute FindText:="<<日付>>", ReplaceWith:=formattedDate, Replace:=2
.Find.Execute FindText:="<<担当>>", ReplaceWith:=ws.Cells(rowIndex, 2).Value, Replace:=2
.Find.Execute FindText:="<<会社名>>", ReplaceWith:=ws.Cells(rowIndex, 5).Value, Replace:=2
.Find.Execute FindText:="<<物件名>>", ReplaceWith:=ws.Cells(rowIndex, 6).Value, Replace:=2
.Find.Execute FindText:="<<満了日>>", ReplaceWith:=formattedEndDate, Replace:=2
.Find.Execute FindText:="<<所在地>>", ReplaceWith:=ws.Cells(rowIndex, 8).Value, Replace:=2
End With
Next rowIndex
' Save the document with the name as "終了通知_日付"
dateStr = Format(ws.Cells(4, 1).Value, "yyyy-mm-dd")
savePath = ThisWorkbook.Path & "\終了通知_" & dateStr & ".docx"
wdDoc.SaveAs2 savePath
wdDoc.Close False
' Delete the temporary file
Kill tempDocPath
' Cleanup
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
Set ws = Nothing
' Display the completion message
MsgBox "終了しました"
End Sub
３．押印の上、郵送
ここで一旦紙を印刷し、社印を押したうえでお取引先様へ郵送します。
ここは残念ながら手作業です。
４．返信をGoogleフォームから投稿してもらう
これまではお取引先様へ郵送した書類に返信書類を同封し、郵送にて返信頂いていました。
郵便料金値上げにより郵送費を削減しようと、Googleフォーム投稿へ変更しました。
当初、Googleフォームが外部がアクセスできずに、情報システム部に聞いたりしたのですが、身近な先輩にきいたところ、設定でできますよとお答えを頂きました。ありがとうございます！先輩！
Googleフォームの設定画面で回答部分を信頼できる組織のユーザーに限定するのチェックを外すことで外部からの投稿ができるようになりました。
契約一覧のスプレッドシートはApp Sheetから情報登録しているので、各契約にランダムな個別IDがついています。そのIDをフォームに入力してもらうと、すでに登録された企業情報が表示されるようGASで設定したかったのですがうまく動かず、断念。
お取引先様にフォームにIDを入力してもらうように変更しました。
フォームが投稿された時点でこのIDで全てのデータにリンクがされるようになります。
５．フォームに投稿されたらPDFに保存（GAS)
フォームに投稿されたら、トリガーが発動され、お取引先様に入力頂いたID名でPDFを自動保存します。自動保存はフォーム送信時でトリガー設定しています。
保存するPDFは、Googleドキュメントで作成したフォーム回答雛形を使用して作成されます。フォームで設定した項目をドキュメント雛形で<<>>で指定すると入力された内容が自動で転記されPDFで保存されます。
各契約担当がいつの契約なのかすぐにわかるように、ドキュメントの雛形にはお取引先さまにて入力してもらう項目以外に、契約一覧のスプレッドシートから同じIDの行の契約満了日を抽出して転記するようにしています。お取引先さまに入力頂く内容は極力少なくするようにしています。
ＧＡＳコードはこちら
function createPDFforNewEntries() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); // シート名を適宜変更
const lastRow = sheet.getLastRow();
const lastColumn = sheet.getLastColumn();
// PDF作成状況を確認する列（K列、11列目をPDF作成確認列とする）
const pdfStatusColumn = 11; // K列は11列目
// 契約一覧シートのIDと満了日を取得
const contractSpreadsheet = SpreadsheetApp.openById('スプレッドシートID');
const contractSheet = contractSpreadsheet.getSheetByName('契約一覧');
const contractData = contractSheet.getRange(2, 1, contractSheet.getLastRow() - 1, 7).getValues(); // A列からG列までのデータを取得
// 2行目以降のデータを処理（1行目はヘッダー）
for (let row = 2; row <= lastRow; row++) {
const id = sheet.getRange(row, 2).getValue(); // 2列目がID
const pdfStatus = sheet.getRange(row, pdfStatusColumn).getValue(); // PDF作成状況
// まだPDFが作成されていない行を処理
if (pdfStatus !== 'PDF作成済み' && id) {
// 最終行のデータを取得
const responses = sheet.getRange(row, 1, 1, lastColumn).getValues()[0];
// タイムスタンプをフォーマット yyyy/MM/DD 時間
const timestamp = new Date(responses[0]);
const formattedTimestamp = Utilities.formatDate(timestamp, Session.getScriptTimeZone(), 'yyyy/MM/dd HH:mm');
// テンプレートのドキュメントIDとPDFの保存先フォルダID
const templateDocId = '雛形ドキュメントID';
const folderId = 'ドライブID';
// Googleドキュメントのテンプレートをコピーして新しいドキュメントを作成
const newDocId = DriveApp.getFileById(templateDocId).makeCopy().getId();
const newDoc = DocumentApp.openById(newDocId);
const body = newDoc.getBody();
// テンプレートのプレースホルダーをフォームの回答で置換
body.replaceText('<<タイムスタンプ>>', formattedTimestamp); // タイムスタンプをフォーマットしたものに置換
body.replaceText('<<ID>>', responses[1]); // 2列目が「ID」
body.replaceText('<<出店ＳＣ名>>', responses[2]); // 3列目が「出店ＳＣ名」
body.replaceText('<<会社名>>', responses[3]); // 4列目が「会社名」
body.replaceText('<<郵便番号>>', responses[4]); // 5列目が「郵便番号」
body.replaceText('<<会社住所>>', responses[5]); // 6列目が「会社住所」
body.replaceText('<<部署名>>', responses[6]); // 7列目が「部署名」
body.replaceText('<<ご担当者名>>', responses[7]); // 8列目が「ご担当者名」
body.replaceText('<<ご担当者様連絡先電話番号>>', responses[8]); // 9列目が「ご担当者様連絡先電話番号」
body.replaceText('<<Emailアドレス>>', responses[9]); // 10列目が「Emailアドレス」
// 契約一覧シートから同じIDの行を検索して満了日を取得
let expirationDate = '';
for (let i = 0; i < contractData.length; i++) {
if (contractData[i][0] === id) { // A列（ID）が一致する場合
expirationDate = contractData[i][6]; // G列（満了日）を取得
break;
}
}
// 満了日をPDFに追加
if (expirationDate) {
const formattedExpirationDate = Utilities.formatDate(new Date(expirationDate), Session.getScriptTimeZone(), 'yyyy/MM/dd');
body.replaceText('<<満了日>>', formattedExpirationDate); // 満了日を追加
} else {
body.replaceText('<<満了日>>', 'データなし'); // IDが見つからない場合の処理
}
// ドキュメントを保存して閉じる
newDoc.saveAndClose();
// ドキュメントをPDFに変換
const newFile = DriveApp.getFileById(newDocId);
const pdf = newFile.getAs('application/pdf');
// PDFを指定フォルダに保存（ファイル名を2列目の「ID」にする）
const folder = DriveApp.getFolderById(folderId);
folder.createFile(pdf).setName(responses[1] + '.pdf'); // 2列目の「ID」をファイル名に設定
// 作成したドキュメントを削除（PDFだけを残す場合）
DriveApp.getFileById(newDocId).setTrashed(true);
// PDF作成済みフラグをK列に設定
sheet.getRange(row, pdfStatusColumn).setValue('PDF作成済み');
}
}
}
６．スプレッドシートに”返送済”と変更し、PDFのリンクを追加（GAS）
契約一覧のIDと５で保存されたPDFのIDが同じものが保存されたら、スプレッドシートに返信済と記載するのと合わせて、PDFのURLを転記するを自動転記されます。
契約一覧スプレッドシートはApp Sheetと連携している為、PDFのURLを登録することでアプリ側からクリックでPDFを開くことができます。各担当はアプリを確認するだけで、契約管理とお取引先様契約担当者を確認することができます。
ＧＡＳコードはこちら
function extractAndSaveForMonth() {
// スプレッドシートIDと保存フォルダID
var sourceSpreadsheetId = 'スプレッドシートID'; // スプレッドシートID
var folderId = 'フォルダID'; // 保存フォルダID
// 元のスプレッドシートを開く
var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
var sourceSheet = sourceSpreadsheet.getActiveSheet();
// メッセージボックスで月を聞く（キャンセルされた場合の処理を追加）
var inputMonth = Browser.inputBox('月を入力してください（形式: yyyy/MM）');
if (inputMonth === 'cancel') {
return; // キャンセル時は終了
}
// データ範囲を取得
var data = sourceSheet.getDataRange().getValues();
// 新しいスプレッドシートを作成
var newSpreadsheet = SpreadsheetApp.create(inputMonth);
var newSheet = newSpreadsheet.getActiveSheet();
// フィルタされたデータを格納する配列
var filteredData = [];
// ヘッダー行を追加 (A列からG列のみ)
filteredData.push(data[0].slice(0, 7));
// データをフィルタリングして抽出 (A列からG列まで)
for (var i = 1; i < data.length; i++) {
var contractType = data[i][2]; // C列
var dateValue = data[i][6]; // G列
// 日付が正しい形式かを確認
var date;
if (Object.prototype.toString.call(dateValue) === "[object Date]" && !isNaN(dateValue)) {
date = dateValue;
} else {
date = new Date(dateValue);
}
// 再契約かつG列が指定された月に一致するか確認
if (contractType === '再契約' && Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM') === inputMonth) {
// A列からG列までのデータを取得
var newRow = data[i].slice(0, 7);
filteredData.push(newRow);
}
}
// 抽出したデータを新しいシートに書き込む
if (filteredData.length > 1) {
newSheet.getRange(1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
} else {
Browser.msgBox('該当するデータがありませんでした。');
newSpreadsheet.delete(); // 該当データがない場合は新規スプレッドシートを削除
return;
}
// 保存先フォルダに移動
var folder = DriveApp.getFolderById(folderId);
var file = DriveApp.getFileById(newSpreadsheet.getId());
file.moveTo(folder);
Browser.msgBox('データが正常に保存されました。');
}
７．”返送済”に変更されたら契約担当者にメールを自動送信（GAS）
６で実行したスプレッドシートが更新されたら、契約一覧リストからIDと一致する担当者を抽出し、各契約担当者に自動でメールを自動送信します。
契約一覧シートとは別のシートに担当者一覧シートを作成し、Ａ列に担当者名：Ｂ列に各担当者のメールアドレス：Ｃ列に自分のメールアドレス、別の任意のセルにメール本文のテンプレートを作成。それに加え、保存されたPDFのURLリンクをメール本文に作成し、メールを自動送信しています。
ＧＡＳコードはこちら
function matchPdfAndSetUrl() {
var folderId = 'フォルダID'; // PDFファイルが保存されているフォルダのID
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles(); // フォルダ内のすべてのファイルを取得
var fileMap = {}; // ファイル名とURLを保存するマップ
// フォルダ内のすべてのファイルをマップに格納
while (files.hasNext()) {
var file = files.next();
var fileName = file.getName().replace('.pdf', ''); // 拡張子を除外してファイル名を取得
fileMap[fileName] = file.getUrl(); // ファイル名をキー、URLを値としてマップに保存
}
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
var data = sheet.getRange(2, 1, lastRow - 1, 1).getValues(); // A列のデータを取得
// メール送信のためのデータを担当者一覧シートから取得
var staffSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('担当者一覧');
var staffData = staffSheet.getRange(2, 1, staffSheet.getLastRow() - 1, 3).getValues(); // 担当者一覧のA列、B列、C列
var emailSubject = staffSheet.getRange('A10').getValue(); // 件名
var emailBodyTemplate = staffSheet.getRange('A11').getValue(); // メール本文テンプレート
for (var row = 0; row < data.length; row++) {
var id = data[row][0].toString();
var currentPValue = sheet.getRange(row + 2, 16).getValue(); // P列の値を取得
var emailLogValue = sheet.getRange(row + 2, 17).getValue(); // Q列（メール送信済みかどうかのログ）を取得
if (id.length === 8 && fileMap[id] && !currentPValue && !emailLogValue) { // P列とQ列が空の場合のみ処理
// P列にPDFリンクを転記
var pdfUrl = fileMap[id];
sheet.getRange(row + 2, 16).setValue(pdfUrl); // P列にURLを転記
// PDFリンクが転記された行のI列に"返送済"を記入
sheet.getRange(row + 2, 9).setValue('返送済'); // I列に"返送済"を記入
// 担当者名を契約一覧シートのB列から取得
var personInCharge = sheet.getRange(row + 2, 2).getValue(); // B列の担当者名を取得
// B列の担当者名に基づいて担当者一覧のB列とC列のメールアドレスを取得
var toEmail = '';
var ccEmail = '';
var staffRow = -1;
for (var i = 0; i < staffData.length; i++) {
if (staffData[i][0] === personInCharge) { // A列で担当者名を一致させる
toEmail = staffData[i][1]; // B列のメールアドレスをTOに設定
ccEmail = staffData[i][2]; // C列のメールアドレスをCCに設定
staffRow = i + 2; // スタッフシートの行番号（2行目から開始）
break;
}
}
// メール本文に追加する情報を取得
var storeName = sheet.getRange(row + 2, 4).getValue(); // D列の店舗名
var companyName = sheet.getRange(row + 2, 5).getValue(); // E列の企業名
var expirationDate = sheet.getRange(row + 2, 6).getValue(); // F列の満了日
// 満了日を yyyy/MM/DD に変換
var formattedExpirationDate = Utilities.formatDate(new Date(expirationDate), Session.getScriptTimeZone(), 'yyyy/MM/dd');
// メール本文にPDFリンクと契約情報を追加
if (toEmail && staffRow !== -1) {
var emailBody = emailBodyTemplate + "\n\n" +
"店舗名: " + storeName + "\n" +
"企業名: " + companyName + "\n" +
"満了日: " + formattedExpirationDate + "\n\n" +
pdfUrl; // メール本文にPDFリンクを追加
MailApp.sendEmail({
to: toEmail,
subject: emailSubject,
body: emailBody,
cc: ccEmail // CCにC列のメールアドレスを設定
});
// メール送信が成功したら、Q列に「メール送信済」と記録
sheet.getRange(row + 2, 17).setValue('メール送信済'); // Q列に「メール送信済」と記録
// 担当者一覧シートのF列に送信日、G列にToメールアドレス、H列にCCメールアドレスを記録
var sendDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy/MM/dd');
staffSheet.getRange(staffRow, 6).setValue(sendDate); // F列に送信日を記録
staffSheet.getRange(staffRow, 7).setValue(toEmail); // G列にToメールアドレスを記録
staffSheet.getRange(staffRow, 8).setValue(ccEmail); // H列にCCメールアドレスを記録
}
}
}
}
フィードバックからの改善
部内で返信を紙からデータに変更しますと連携したところ、情報が見やすくなった👍メールで送信される文章が簡潔すぎるので迷惑メールと誤認しそう😰という意見もあり、メール本文雛形を修正しました。
また、今まで返信されきた紙を各担当者に渡していたので紙の削減にはなりましたが、紙でもらう方が忘れないという意見もあり、その方はPDFをわざわざ印刷して保管していました😅。なかなか一遍に全部変えるとはいかないようです。
アプリを都度確認するという習慣がない担当者もいる為、契約一覧をリスト化しメールで送信することも自動化させようと思っています。
以前、契約管理のオンラインサービス導入を検討したことがありましたが、なんと自分で作れちゃいそうです！
課題
今回の一連の作業で４～７は私の作業は一切発生しません。はずが、お取引先様の中にはパソコンがうまくできないという企業もいらっしゃることから、FAXでも返信可としました。FAXで返信されたものについては自分でPDFにし、ID名でドライブに保存する必要があります。ただし、PDFを保存するだけであとは自動転記される為、作業時間は大幅に削減されました。
社内ではまだ契約書類は紙という規定があります。
電子契約書を使用している企業もいる中、当社は紙しか許可されていないのでというDX推進を足止めしている課題があります。来年度に電子契約書を推進せよと部内では指令が出ていますが、社内説得へのハードルはまだまだ高いのではと感じています。
個人がDX化に挑んでいる中、社内ではまだまだセキュリティ面で許可されない部分が多いです。１つ１つ説明、説得が必要であり、もっとＤＸ推進に理解、興味を持つ人を増やすことが課題であると日々感じています。
まとめ
プログラミングコードを書けない私が初めてGoogle Apps Scriptに出会ってここまで作業効率を改善することができました。いまだにコードの中身は理解していませんし、コードが合っているのかもわかりません。すべてChatGPTもしくはClaudeでコード生成しています。
１つ１つあれをどうしようこれを動かそうと作成している為、もっと効率の良い方法があるかもしれません。これってどうなの？等、何か気づいた点がありましたらご教示ください！
何をどう動かしたいか、何がどうなったら楽になるか日々業務の自動化に向き合っています。