■ 背景
社内で既定のメーラーをBecky!からOutlookにしようということから始まり、
Outlookでアドレス帳にデータをインポートする方法を調べていると、元ファイルの形式がBecky!とOutlookで違うことを知りました。
(Becky!→テキストファイル,Outlook→CSVファイル)
今後アドレス帳を更新していくことを踏まえ、Googleスプレッドシートでアドレス帳を管理して更新の際に自動でファイル出力できるものをGoogle Apps Scriptで作ってしまおうということになりました。
■ ゴールイメージ
元のデータを自分で更新して、スクリプトを動かせばインポート用のファイルがGoogle Driveに出力されるという仕組み。
本記事ではOutlook用だけ紹介します。
※Becky!用にもシートとスクリプトを作りましたが、ほぼ変わらないのでOutlookと違うとこだけ注釈入れます。
■ 仕組みの考え方
シートの構成
| シート名 | 内容 | 用途 |
|---|---|---|
| 更新反映データ | データ更新用のシート | データ更新 |
| Outlook | Outlook用最新の全データ | ファイル出力 |
| Outlook差分 | Outlook用差分データ | ファイル出力 |
| ここでいう差分とは、登録アドレス追加分のことです。 |
「更新反映データ」でデータの追加・削除を行い、「Outloook」「Outlook差分」に反映させるという仕組み。
シートの更新は関数だけで作れるのでは？
「更新反映データ」から各シートへの反映は、VLOOKUP,INDEX,OFFSETでやればいけるだろうという勝手な思い込みで試みるも、今後社員が増えていくときに下へ下へと関数を追加しなくてはいけないと気づく。
とはいえ、予めセルに関数を入れてしまうとファイルに出力した際に関数まで拾ってしまいデータ範囲が広くなり、出力の処理を調整するのが面倒。
(セルに関数を挿入して何らかの拍子に誤って書き換えてしまったりすると、さらに面倒な気もする)
だったら全部GASで作る
少々荒業な気もしますが、関数とGASを組み合わせるよりはすべてをGASで作るほうが考え方が楽と考え、関数ではなくGASですべてを作ることに。
■ 仕組み作り
実際のシート(更新反映データ)
ルールとして、
今回更新した人に関してはF列に★を付ける。(差分のデータを出力する際に使う)
中身はさておき…
■スクリプト
シート更新
Outlook
sheet_Outlook.clear();で一度シート全体をクリアしてコピー(同期)することで、追加と削除に対応する。
「更新反映データ」でデータを追加、書き換えを行った上で「Outlook」へ更新反映させる。
function updateLatestSheet_Outlook() {
var sheet_src = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('更新反映データ');
var sheet_Outlook = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Outlook');
sheet_Outlook.clear();
for (var i = 1; i <= sheet_src.getDataRange().getValues().length ; i++) {
for (var j = 1; j <= 5 ; j++) {
var values = sheet_src.getRange(i, j).getValue();
sheet_Outlook.getRange(i, j).setValue(values);
}
}
}
Outlook差分
実際のシートにもある通り
追加した行に対して、F列に★を入力するルールという前提で、F列の★をトリガーにデータを差分シートに書き込む。
また、先頭のカラム行も追加する。
function updateDifferSheet_Outlook() {
var sheet_src = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('更新反映データ');
var sheet_differ_Outlook = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Outlook差分');
sheet_differ_Outlook.clear();
for (var i = 1; i <= sheet_src.getDataRange().getValues().length; i++ ) {
var flag = sheet_src.getRange(i,6).getValue();
var differName = sheet_src.getRange(i, 1).getValue();
var differAddress = sheet_src.getRange(i, 2).getValue();
var differAccount = sheet_src.getRange(i, 3).getValue();
var differLastName = sheet_src.getRange(i, 4).getValue();
var differFirstName = sheet_src.getRange(i, 5).getValue();
if (i === 1) {
sheet_differ_Outlook.appendRow([differName,differAddress,differAccount,differLastName,differFirstName]);
}
if (flag === "★") {
sheet_differ_Outlook.appendRow([differName,differAddress,differAccount,differLastName,differFirstName]);
}
}
}
ロードと書き込み
シート名を指定してロードと書き込みの関数を設定。
csvで出力なので,区切り
データに日本語を含む場合、charset= 'utf-8'だと文字化けするので、charset = 'Shift_JIS'で指定。
※Becky!の場合
元ファイルはテキストファイルでTab区切りにしなくてはいけないので、,の部分を\tにする。また、テキストファイルで出力するのでcharset= 'utf-8'でも可能。
function loadData_writeDrive_Outlook(sheetName) {
var drive = DriveApp.getFolderById('<出力したいGoogleDriveのフォルダID>');
var fileName;
var today = Utilities.formatDate( new Date(), 'Asia/Tokyo','yyyyMMdd');
var contentType = 'text/csv';
var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues();
var csv = '';
var charset = 'Shift_JIS';
for(var i = 0; i < data.length; i++) {
csv += data[i].join(',') + "\r\n";
}
if (sheetName === 'Outlook') {
fileName = '【最新版】Outlookアドレス帳_'+today+'.csv';
}
if (sheetName === 'Outlook差分') {
fileName = '【差分】Outlookアドレス帳_'+today+'.csv';
}
var blob = Utilities.newBlob('', contentType, fileName).setDataFromString(csv, charset);
drive.createFile(blob);
}
function createLatestCSV_Outlook() {
loadData_writeDrive_Outlook('Outlook');
}
function createDifferCSV_Outlook() {
loadData_writeDrive_Outlook('Outlook差分');
}
■実行
createLatestCSV_Outlook,createDifferCSV_Outlookを実行する
■CSVファイル出力
Google Driveに出力された！
中身を見てみる
テキストエディタでCSVファイルを開いてみる。
両方とも、上手く出力されました。
最新版
差分
おわりに
ほぼ誰得的なスクリプトですが、予想以上に試行錯誤を繰り返しました…
開発経験の1つになってよかったです。
今度GASで開発する機会があったら、API絡みの開発を経験してみたいです。