Google Spreadsheet のデータを自動で Google Calendar に追加する

おはこんばんちは!! 尾藤 a.k.a. BTO です!!

スギ花粉の脅威に毎日怯えている皆様、ご機嫌いかがでしょうか。 最近は Google Spreadsheet や GAS(Google Apps Script) を使って社内業務の改善などをしております。 だって会社がどんどん大きくなってるから、業務の効率化を進めないと回らないんだもん!!

Google Spreadsheet のデータを Google Calendar に追加したい!!

弊社では営業管理ツールに Salesforce 使っていて、他部署とのデータ連携のために、Salesforce のデータを Google Spreadsheet に同期しています。 ここに動画の公開日の情報があるので、Google Calendar にイベントを追加したいのが今回の目的です。 以前は、全て人が手作業で入力していたため非常にコストのかかる作業だったのが、全て自動でできるようになってので、かなり効率化することができました。

Spreadsheet にスクリプトを追加する

Spreadsheet に GAS を追加するのは簡単です。 Tools -> Script editor でスクリプトエディタを開いたら、あとはガンガン書くだけ。 ES5(Javascript)で書けるので、Javascript を書いたことがある人なら簡単に書けます。

Spreadsheet から情報を読み出す

GAS では Spreadsheet にアクセスするようのクラス SpreadsheetApp が用意されているので、これで簡単に情報をとってくることができます。 具体的に何ができるかは、 SpreadsheetApp のマニュアルを見ればわかります。

例えばこんなプログラムでシートの情報を二次元配列で取得できます。

var ss = SpreadsheetApp.getActive();
var records = ss.getRange('Sheet1!A:Z').getValues();

Google Calendar にスケジュールを追加する

SpreadsheetApp と同じように CalendarApp というクラスが用意されているので、これを使えば簡単に Google Calendar にスケジュールを追加できます。

var calApp = CalendarApp.getCalendarById('ここにカレンダーのIDを入れる');
calApp.createAllDayEvent('タイトル', new Date('3/14/2017'));

ここまでで、Spreadsheet からデータを読み込んで、Google Calendar にスケジュールを追加することができるようになりました。 しかし、このままでは運用に大きな問題があります。

スケジュールの新規追加しかできない

のです。 このままではスクリプトを実行するたびにスケジュールが増殖してしまいますね。 なので、新規追加したスケジュールの場合はイベントIDを覚えておいて、イベントIDがある場合は新規追加ではなく編集するようにしないといけません。

そのためには、スケジュールのイベントIDとそれに対応する情報を何らかの形でどこかに保存する必要があります。

連想配列を読込・保存する HashSheet クラスを作成

今回のスクリプトは Spreadsheet 上で動作するので、スケジュールのイベントID を Spreadsheet に保存するようにしました。 しかし、Spreadsheet は基本的には二次元配列しか扱うことができないので、扱いたい情報(key)に対応するイベントID(value)をとってくるような KVS的な使い方をするのが難しいです。 そこで、Spreadsheet の二次元データと Javascript の連想配列の変換をするためのクラス HashSheet を作りました。

var HashSheet = function HashSheet(sheetName) {
  if (!sheetName) {
    throw new Error('sheetName must be specified');
  }
  this.sheetName = sheetName;

  this.sheet = SpreadsheetApp.getActive().getSheetByName(this.sheetName);
  if (!this.sheet) {
    throw new Error('sheet does not exist');
  }
};

HashSheet.prototype.destroy = function destroy() {
  this.sheet.getRange('A:B').clear();
};

HashSheet.prototype.read = function read() {
  var obj = {};
  var records = this.sheet.getRange('A:B').getValues();
  for (var i = 0; i < records.length; i++) {
    if (records[i][0]) {
      obj[records[i][0]] = records[i][1];
    }
  }
  return obj;
};

HashSheet.prototype.save = function save(obj) {
  var records = [];
  for (var key in obj) {
    if (!obj.hasOwnProperty(key)) {
      continue;
    }
    records.push([key, obj[key]]);
  }

  this.destroy();
  this.sheet.getRange(1, 1, records.length, 2).setValues(records);
};

使い方は簡単で、 read() メソッドで、シートの二次元データを Javascript の連想配列として取り出します。 そして save() メソッドで Javascript の連想配列を Spreadsheet の二次元データとして保存します。

var hashSheet = new HashSheet('sheet1');
var obj = hashSheet.read();
// 処理
hashSheet.save(obj);

イベントIDからカレンダーのイベント情報が取得できない

実は他にも問題があって、 CalendarApp にはイベントIDからイベントを取得する機能がありません。 そのため、そのカレンダーに登録されているイベントの情報をとってきて、同じイベントIDを持つイベントを探すしかありません。 この辺の処理を補うために、 CalUtil というクラスを作りました。 CalUtil には他にも全イベントを削除する機能も追加しています。

var CalUtil = function CalUtil(calId) {
  this.calApp = CalendarApp.getCalendarById(calId);
  if (!this.calApp) {
    throw new Error('Calendar does not exist');
  }
};

CalUtil.prototype.deleteEvents = function deleteEvents() {
  var events = this.calApp.getEvents(new Date('1/1/1970'), new Date('1/1/2100'));
  for (var i = 0; i < events.length; i++) {
    events[i].deleteEvent();
  }
};

CalUtil.prototype.getEvents = function getEvents() {
  var obj = {};
  var events = this.calApp.getEvents(new Date('1/1/1970'), new Date('1/1/2100'));
  for (var i = 0; i < events.length; i++) {
    obj[events[i].getId()] = events[i];
  }
  return obj;
};

カレンダーイベントの作成・編集の状態を Spreadsheet 上で管理する CalSheet クラスを作成

HashSheet と CalUtil クラスを実装したので、カレンダーイベントの状態を管理する準備が整いました。 それで、この2つのクラスを使って CalSheet というカレンダーイベントの状態を Spreadsheet で管理するクラスを作成しました。

var CalSheet = function CalSheet(calId, sheetName) {
  this.calUtil = new CalUtil(calId);
  this.hashSheet = new HashSheet(sheetName);

  this.eventIds = null;
  this.events = null;

  this.sheetName = sheetName;
};

CalSheet.prototype.deleteAll = function deleteAll() {
  this.calUtil.deleteEvents();
  this.hashSheet.destroy();
};

CalSheet.prototype.getEventById = function getEventById(eventId) {
  var events = this.getEvents();
  return events[eventId];
};

CalSheet.prototype.getEventByKey = function getEventByKey(key) {
  return this.getEventById(this.getEventIdByKey(key));
};

CalSheet.prototype.getEventIdByKey = function getEventIdByKey(key) {
  return this.getEventIds()[key];
};

CalSheet.prototype.getEventIds = function getEventIds() {
  if (this.eventIds) {
    return this.eventIds;
  }

  this.eventIds = this.hashSheet.read();
  return this.eventIds;
};

CalSheet.prototype.getEvents = function getEvents() {
  if (this.events) {
    return this.events;
  }

  this.events = this.calUtil.getEvents();
  return this.events;
};

CalSheet.prototype.saveEventId = function saveEventId(key, eventId) {
  var eventIds = this.getEventIds();
  eventIds[key] = eventId;
  this.hashSheet.save(eventIds);
};

いろいろメソッドはありますが、基本的には getEventByKey() でカレンダーイベントを取り出して、 新規追加の場合はsaveEventId でイベントIDを保存するのが主な使い方になります。 プログラムは下記のようなコードになります。

var calSheet = new CalSheet('カレンダーID', 'Calendar');
var calApp = calSheet.cal;
var calEvent = calSheet.getEventByKey('キー');
if (!calEvent) {
  calEvent = calApp.createAllDayEvent('タイトル', new Date('3/14/2017'));
  calSheet.saveEventId('キー', calEvent.getId());
}

Spreadsheet 上のデータを自動で Google Calendar に追加する

ここまでで、事前準備は全て整いました。 実際に動かすプログラムは、だいたい次のようなコードになります。

// カレンダーイベントを Spreadsheet で管理する CalSheet のインスタンス生成
var calSheet = new CalSheet('カレンダーID', 'Calendar');

// CalendarApp のインスタンス
var calApp = calSheet.calUtil.calApp;

// Spreadsheet から情報を取得
var ss = SpreadsheetApp.getActive();
var records = ss.getRange('Sheet1!A:Z').getValues();

// それぞれの情報に対してカレンダーイベントの処理を行う
for (var i = 1; i < records.length; i++) {
  var record = records[i];
  var key = record[?];
  var title = record[?];
  var date = record[?];

  // キーとなる情報を元にカレンダーイベントを取得
  var calEvent = calSheet.getEventByKey(key);

  // カレンダーイベントが無い場合は新規作成
  if (!calEvent) {
    calEvent = calApp.createAllDayEvent(title, date);
    // イベント作成後すぐに保存。スクリプトが途中で止まっても大丈夫なように
    calSheet.saveEventId(key, calEvent.getId());
  }

  //
  // カレンダーイベントの中身を更新
  //
}

まとめ

UUUM攻殻機動隊に入隊すると、決起会で4000円のうな重が食べられます。

---

www.wantedly.com