【GAS】Google Apps Script 活用事例 差し込み文書の作成を自動化する
「・・・・コレ、1枚1枚作ってたんじゃ、仕事終わらなくね？」
実務で、海外のスタッフが一時帰国する度に、必ず申請しなきゃいけない書類作成を自動化しました。その振り返りを備忘録として残しておきたいと思います。俗に言う差し込み印刷.....ですね。
Google Apps Scriptは、巷では、省略して「GAS」と呼ばれています。今回は、このGoogle Apps Scriptを用いた自動化と、実際のスクリプトを紹介したいと思います。
自動化の手順
1.書類を発行したいスタッフの名前、住所などシート内容の読み取り
2.テンプレートを元にドキュメントを複製
3.スタッフごとに内容の差し込み
4.管理しやすいようにファイル名のリネーム
5.指定のgoogleドライブに格納
6.ドキュメントのURLをシートに記述
今回、スクリプトを作成するにあたって、こちらの記事のアイデアを参考にしました。URL書いてくれるのってナイスアイデア！！これは、Google Driveならでは.....ですね！！下記の記事では担当者が入力されたら、トリガーで自動で作成されるようですが、今回、僕のスクリプトでは、対象者が3人であれば3通を、一括作成をするためにfor文を使っています。
参考にさせて頂いたスクリプト
サンプルファイルを公開しています。
完成形のソースコード
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('追加メニュー')
.addItem('差し込み文書の作成', 'createNewDocument')
.addToUi();
}
function createNewDocument() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('DB');
const values = spreadsheet.getDataRange().getDisplayValues();
//見出し行から列を特定する
const header = values[0];
const column = {
id: header.indexOf('ID'),
japaneseName: header.indexOf('スタッフ名'),
englishName: header.indexOf('スタッフ名・英語'),
address: header.indexOf('現住所'),
birthDate: header.indexOf('生年月日'),
employmentDate: header.indexOf('入社日'),
issuedDate: header.indexOf('書類発効日'),
status: header.indexOf('作成ステータス'),
url: header.indexOf('URL'),
online: header.indexOf('次回以降のオンライン申請'),
}
console.log(column);
let count = 0;
for(let i = 1; i < values.length; i++){
//A列のIDが記載あり、10列目の作成ステータスが空白だった時
if(values[i][column.id] !=='' && values[i][column.status] == ''){
const row = i + 1;
const info = {
id: values[i][column.id], //ID
japaneseName: values[i][column.japaneseName], //スタッフ名の日本語表記
englishName: values[i][column.englishName], //スタッフ名の英語表記
address: values[i][column.address], //住所
birthDate: values[i][column.birthDate], //生年月日
employmentDate: values[i][column.employmentDate], //契約日・入社年月日
issuedDate: values[i][column.issuedDate], //この書類が発行された日
}
console.log(info);
//別の関数を呼び出す。新しく作成したドキュメントのURLが取得できる。
const fileName = `${info.id} ${info.japaneseName} 発効日 ${info.issuedDate}`;
const documentUrl = replaceDocument(fileName, info);
//転記予定の範囲
const range = sheet.getRange(row, 10, 1, 3);
const newValues = [['作成済', documentUrl, '可']];
range.setValues(newValues);
console.log(`転記予定範囲： ${range.getA1Notation()}`);
count += 1;
}//if
}//for_i
console.log(`書類発行の対象者： ${count} 名`);
}//end
/**
* @param {string} ファイル名
* @param {object} 日本語名、英語名、住所等の差し込みに使う情報
* @return {string} 新規作成したドキュメントのURL
*/
function replaceDocument(fileName, info) {
//テンプレートファイルのID
const sourceDocument = DriveApp.getFileById('*******************');
//コピーしたファイルの保存先（フォルダ）の指定
const folder = DriveApp.getFolderById('***************');
////makeCopy(ファイル名、保存場所)
const duplicateDocument = sourceDocument.makeCopy(fileName, folder);
const duplicateDocumentId = duplicateDocument.getId();
//コピーしたドキュメントのURLを生成
const createUrl = 'https://docs.google.com/document/d/' + duplicateDocumentId + '/edit';
console.log(createUrl);
//コピーしたドキュメントをIDで開く
const targetDocument = DocumentApp.openById(duplicateDocumentId);
console.log(`作成したファイル名： ${targetDocument.getName()}`);
//ドキュメントの内容(placeholder1や、placeholder２)を差し替え
targetDocument.getBody()
.replaceText('placeholder01',info.englishName)//英語名
.replaceText('placeholder02',info.japaneseName)//日本語名
.replaceText('placeholder03',info.address)//住所
.replaceText('placeholder04',info.birthDate)//生年月日
.replaceText('placeholder05',info.employmentDate)//入社日
return createUrl
}ログ
2022/03/25 リライト
//見出し行から列を特定する
const header = values[0];
const column = {
id: header.indexOf('ID'),
japaneseName: header.indexOf('スタッフ名'),
englishName: header.indexOf('スタッフ名・英語'),
address: header.indexOf('現住所'),
birthDate: header.indexOf('生年月日'),
employmentDate: header.indexOf('入社日'),
issuedDate: header.indexOf('書類発効日'),
status: header.indexOf('作成ステータス'),
url: header.indexOf('URL'),
online: header.indexOf('次回以降のオンライン申請'),
}
console.log(column);
見出し行（1行目）から列を特定するスクリプトです。
values[i][4]などのように書いていると、列が追加されたりした場合に不具合が起こることがあります。
そのため、1次元配列の中から、特定の単語がどこにあるかを探す事のできるindexOfを使用しています。
難しいと感じる場合は、ライブラリの併用を検討してみるのも良いかも
GASを書く際に役立つショートカットキー
fn + ctrl + space メソッドの入力候補表示
option + / 変数名の入力補完
option + ↑キー 1行まるごと上に移動させる
option + shift + ↓キー 1行上の内容をコピペする
command + shift + k 1行まるごと削除
command + return ログの表示
command + R 実行
対象のシートがどれかを決める
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('DB');
const values = spreadsheet.getDataRange().getDisplayValues();
values.shift();シートが複数ある場合、どのシートが対象なのかを決める必要があります。今回は、DBというシート名からスタッフ名などの情報を読み取ります。
ざっくりとした解説ですが、マトリョーシカっぽいイメージです。
住所でも東京都港区......みたく大きな括りから小さく範囲を限定していきますよね？それと同じです。
・spreadsheetオブジェクト （大）
・sheetオブジェクト （中）
・rangeオブジェクト （小）
シートから情報を読み取る方法
spreadsheet.getRange('A2').getValue(); //単体のセルから情報を読み取る場合
spreadsheet.getRange('A2:D').getValues(); //特定の範囲から読み取る場合例えば、A2:D40の時もあれば、A2:D23の時のある。こうした変更をいちいちスクリプトに反映させるのは面倒です。GASには、データが存在する範囲をまるごと読み取るメソッドがあります。
getDataRange という便利なメソッド
sheet.getDataRange().getValues();getValues()で、シート上のデータを取得する事が出来ます。その際、データは、2次元配列で取得されます。2次元配列に関しては下記のブログが、一番分かりやすかったです。
日付の操作・処理は、getDisplayValues()を使った方が楽
var birthDate = Utilities.formatDate(new Date(values[i][10]),'Asia/Tokyo','yyyy/MM/dd');//生年月日
var employmentDate = Utilities.formatDate(new Date(values[i][11]),'Asia/Tokyo','yyyy/MM/dd');//入社年月日（契約開始日）
var issuedDate = Utilities.formatDate(new Date(values[i][12]),'Asia/Tokyo','yyyy/MM/dd');//書類の発行日2020.04.08 V8に対応させました。過去記事では、上記のように書いていたのですが、getDisplayValues()を使えば、記述が不要になります。詳しくは下記のエントリーでまとめています。
灰色で、グレーアウトした部分が、今回のスクリプトでは使わない部分になります。IDと名前だけだと、情報不足で分からない事が多いので、部署名を入れています。グループも今回のスクリプトに限っては使いません。
Google ドキュメントの操作方法について
tonari-it.com は、Google Apps Scriptでは一番詳しいブログで、初心者にも分かりやすく説明されています。最初は僕も、こちらを読んで勉強しました。
GASによるドキュメントの自動化は記事が少なめ
Googleドキュメントは、躓いた時に参考になるページが少ない.....。
躓いたのはreplaceメソッドとreplaceTextメソッドの使い方です。文書のテンプレートを複製して（フォントやスタイルを生かして）作成したい場合には、replaceTextメソッドが基本的にオススメです。
サンプルはこんな感じです。placeholder1とかplaceholder2とかにシートの値が挿入されます！！すごくシンプル。
Twitterで頂いたご指摘
1点目
Forの中で、作成済のステータスのものは処理から飛ばすと思いますが、GoogleドキュメントのURLを記載する処理は取得したレコードの上から順番に実施されてしまうので、ズレてしまいます。
例えば1行目が作成済になっており、2行目から処理を実施した際に
1行目のURLの行に2行目のURLが入ってしまうようです。
sheet.getRange(2, 10, status.length, status[0].length).setValues(status);上記のように、2次元配列を貼り付けているのが原因でした。修正しました。修正後のスクリプトでは、for文内でsetValue()を記載しています。
2点目
placeholder1をplaceholder01にした方が良いかと思います。placeholder10が出てきた時にplaceholder1と認識されてしまうからです。
差し込みしたい内容が10以上ある場合は、placeholder01とした方がいいよとのことでした。鍵付きアカウントでしたので、お名前は伏せさせていただきますが、ご指摘ありがとうございます。
エラーが出たパターン
const targetBody = targetDocument.getBody();
const targetText = targetBody.getText(); //ドキュメントの内容を取得
const replaceText = targetText.replace(/{部署・名前}/,section).replace(/{マニュアルタイトル}/,manualTitle).replace(/{yyyy年MM月dd日}/,createDate);
targetBody.setText(replaceText);replaceTextとsetTextは一緒に使っちゃいけない（笑）
下記もダメなパターン。真似しないでください。文書が白紙で作成される事が連発しました。replaceTextを使う場合は、setTextは要りません。
targetDocument.getBody()
.replaceText('placeholder1',englishName)
.replaceText('placeholder2',japaneseName)
.replaceText('placeholder3',residenceAddress)
.replaceText('placeholder4',birthDate)
.replaceText('placeholder5',employmentDate)
.replaceText('placeholder6',issuedDate);
targetBody.setText(replaceText);スニペット作りました！！
毎回、ゼロベースでスクリプトを書くのは大変なので、過去に書いたスクリプトのカンペ集のようなものを作りました！！良かったら見ていってください。