詳細は以下の記事を参照。
Last active
January 7, 2020 07:48
-
-
Save W-Yoshida/df2765c8438e79929ed223c5bf3af0a0 to your computer and use it in GitHub Desktop.
ガルーンからGoogleカレンダーに予定を移行するRubyプログラム
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
require 'net/http' | |
require 'uri' | |
require 'rexml/document' | |
require 'OpenSSL' | |
require 'csv' | |
require 'date' | |
FACILITY_FILE_NAME = 'FacilityList.csv' | |
USER_FILE_NAME = 'GaroonUsers.csv' | |
OUTPUT_FILE_NAME = 'GetEventByFacility.csv' | |
# 予定を取得する対象期間を指定(UTC+0000) | |
TARGET_DATE_FROM = '2018-06-08T15:00:00' | |
TARGET_DATE_TO = '2019-12-31T14:59:59' | |
# ガルーンのユーザー一覧、設備一覧CSVを読み込んでテーブルにしておく | |
# ※文字化け回避のため、SJISから変換せずにUTF-8のまま扱う | |
# ※CSVのヘッダは読み込み時にすべて小文字に変換されるので注意 | |
USER_TABLE = CSV.table(USER_FILE_NAME, encoding: 'UTF-8') | |
FACILITY_TABLE = CSV.table(FACILITY_FILE_NAME, encoding: 'UTF-8') | |
# アウトプットのヘッダを書き込み | |
csv = CSV.open(OUTPUT_FILE_NAME,'w') | |
csv << ['取得対象会議室','イベントID','イベントタイプ','公開タイプ','予定区分','予定件名','予定詳細','タイムゾーン(開始)','タイムゾーン(終了)','終日予定フラグ','開始時刻のみフラグ','開始時刻','終了時刻','参加者','参加者(メールアドレス)','公開先','公開先(メールアドレス)','設備','設備(メールアドレス)','繰り返し条件','繰り返し除外日時'] | |
csv.close | |
def get_event_by_facility(target_facility_name,target_facility_id,start_time,end_time) | |
uri = URI.parse('https://yourdomain.cybozu.com/g/cbpapi/schedule/api.csp?') | |
response = nil | |
request = Net::HTTP::Post.new(uri.request_uri) | |
request.body = <<EOS | |
<?xml version="1.0" encoding="UTF-8"?> | |
<soapenv:Envelope xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope"> | |
<soapenv:Header> | |
<Action> | |
ScheduleGetEventsByTarget | |
</Action> | |
<Security> | |
<UsernameToken> | |
<Username>ikou-user</Username> | |
<Password>password</Password> | |
</UsernameToken> | |
</Security> | |
<Timestamp> | |
<Created>2010-08-12T14:45:00Z</Created> | |
<Expires>2037-08-12T14:45:00Z</Expires> | |
</Timestamp> | |
<Locale>jp</Locale> | |
</soapenv:Header> | |
<soapenv:Body> | |
<ScheduleGetEventsByTarget> | |
<parameters start="#{start_time}" end="#{end_time}"> | |
<facility id="#{target_facility_id}"></facility> | |
</parameters> | |
</ScheduleGetEventsByTarget> | |
</soapenv:Body> | |
</soapenv:Envelope> | |
EOS | |
http = Net::HTTP.new(uri.host, uri.port) | |
http.use_ssl = true | |
http.verify_mode = OpenSSL::SSL::VERIFY_NONE | |
http.start do |h| | |
response = h.request(request) | |
end | |
File.open('GetEventByFacility.xml', 'w') do |f| | |
f.puts(response.body) | |
end | |
# responseをXML解析してCSV出力 | |
doc = REXML::Document.new(response.body) | |
csv = CSV.open(OUTPUT_FILE_NAME,'a') | |
doc.elements.each('soap:Envelope/soap:Body/schedule:ScheduleGetEventsByTargetResponse/returns/schedule_event'){ |event| | |
line = [] | |
line << target_facility_name.chomp.encode('UTF-8') | |
line << event.attributes['id'] | |
line << event.attributes['event_type'] | |
line << event.attributes['public_type'] | |
line << event.attributes['plan'] | |
line << event.attributes['detail'] | |
line << event.attributes['description'] | |
line << event.attributes['timezone'] | |
line << event.attributes['end_timezone'] | |
line << event.attributes['allday'] | |
line << event.attributes['start_only'] | |
# 時刻 | |
event.elements.each('when/datetime'){ |time| | |
line << time.attributes['start'] | |
line << time.attributes['end'] | |
} | |
# 参加者 | |
member_list = '' | |
member_email_list = '' | |
event.elements.each('members/member/user'){ |member| | |
display_name = member.attributes['name'] | |
member_list += display_name + "\n" | |
# ガルーンユーザーCSVから参加者のメールアドレスを取得 | |
user = USER_TABLE.find{|user_row| user_row[:displayname] == display_name} | |
if user.nil? | |
user_email = '不明ユーザー' | |
else | |
if user[:email].nil? | |
user_email = 'メールアドレスなし' | |
else | |
user_email = user[:email] | |
end | |
end | |
member_email_list += user_email + "\n" | |
} | |
line << member_list.chomp | |
line << member_email_list.chomp | |
# 公開先 | |
observer_list = '' | |
observer_email_list = '' | |
event.elements.each('observers/observer/user'){ |obs| | |
display_name = obs.attributes['name'] | |
observer_list += display_name + "\n" | |
# ガルーンユーザーCSVから公開先のメールアドレスを取得 | |
user = USER_TABLE.find{|user| user[:displayname] == display_name} | |
if user.nil? | |
user_email = '不明ユーザー' | |
else | |
if user[:email].nil? | |
user_email = 'メールアドレスなし' | |
else | |
user_email = user[:email] | |
end | |
end | |
observer_email_list += user_email + "\n" | |
} | |
line << observer_list.chomp | |
line << observer_email_list.chomp | |
# 設備 | |
facility_list = '' | |
facility_email_list = '' | |
event.elements.each('members/member/facility'){ |facility| | |
facility_name = facility.attributes['name'] | |
facility_list += facility_name + "\n" | |
# ガルーン設備CSVから設備カレンダーのメールアドレスを取得 | |
facility = FACILITY_TABLE.find{|facility_row| facility_row[:facility_name] == facility_name} | |
if facility.nil? | |
facility_email = '不明な設備' | |
else | |
if facility[:resource_id].nil? | |
facility_email = '不明な設備(メールアドレスなし)' | |
else | |
facility_email = facility[:resource_id] | |
end | |
end | |
facility_email_list += facility_email + "\n" | |
} | |
line << facility_list.chomp | |
line << facility_email_list.chomp | |
# 繰り返し条件 | |
repeat_condition = '' | |
event.elements.each('repeat_info/condition'){ |condition| | |
repeat_condition = 'type:' + condition.attributes['type'] + "\n" \ | |
+ 'day:' + condition.attributes['day'] + "\n" \ | |
+ 'week:' + condition.attributes['week'] + "\n" \ | |
+ 'start_date:' + condition.attributes['start_date'] + "\n" \ | |
+ 'end_date:' + condition.attributes['end_date'] + "\n" \ | |
+ 'start_time:' + condition.attributes['start_time'] + "\n" \ | |
+ 'end_time:' + condition.attributes['end_time'] | |
} | |
line << repeat_condition | |
# 繰り返し除外日時 | |
repeat_list = '' | |
event.elements.each('repeat_info/exclusive_datetimes/exclusive_datetime'){ |repeat_info| | |
# 除外日時は開始日時のみ取得すればOK | |
repeat_list += DateTime.parse(repeat_info.attributes['start']).strftime('%Y%m%d') + "\n" | |
} | |
line << repeat_list.chomp | |
csv << line | |
} | |
csv.close | |
end | |
#アウトプットのヘッダを書き込み | |
csv = CSV.open(OUTPUT_FILE_NAME,'w') | |
csv << ['取得対象会議室','イベントID','イベントタイプ','公開タイプ','予定区分','予定件名','予定詳細','タイムゾーン(開始)','タイムゾーン(終了)','終日予定フラグ','開始時刻のみフラグ','開始時刻','終了時刻','参加者','参加者(メールアドレス)','公開先','公開先(メールアドレス)','設備','設備(メールアドレス)','繰り返し条件','繰り返し除外日時'] | |
csv.close | |
FACILITY_TABLE.each do |row| | |
#予定の取得・出力を実行 | |
p row[:facility_name] | |
get_event_by_facility(row[:facility_name],row[:id],TARGET_DATE_FROM,TARGET_DATE_TO) | |
end | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
require 'google/apis/calendar_v3' | |
require 'googleauth' | |
require 'googleauth/stores/file_token_store' | |
require 'csv' | |
require 'date' | |
require 'time' | |
OOB_URI = 'urn:ietf:wg:oauth:2.0:oob' | |
dir_path = File.dirname(__FILE__) | |
# Google Calendar APIを実行する管理者権限Googleアカウント | |
user_id = 'admin@yourdomain.com' | |
# アプリケーション名 | |
app_name = 'ImportGoogleCalendar' | |
# トークンを保存するファイル | |
store_file = dir_path + 'google_api.yaml' | |
# Google API Console からダウンロードしたclient idファイル | |
client_id = Google::Auth::ClientId.from_file("#{dir_path}/client_id.json") | |
token_store = Google::Auth::Stores::FileTokenStore.new(file: store_file) | |
scope = 'https://www.googleapis.com/auth/calendar' | |
authorizer = Google::Auth::UserAuthorizer.new(client_id, scope, token_store) | |
credentials = authorizer.get_credentials(user_id) | |
if credentials.nil? | |
# トークンが設定されていない、または期限切れの場合 | |
url = authorizer.get_authorization_url(base_url: OOB_URI) | |
File.write 'url.txt', url | |
puts 'ブラウザで次のURLを開いてAPIの利用を許可してください' | |
puts url | |
puts 'URLは(url.txt)にも出力されています' | |
puts '応答ページに表示されるコードを入力してenterを押してください' | |
puts 'code:' | |
code = gets | |
credentials = authorizer.get_and_store_credentials_from_code(user_id: user_id, code: code, base_url: OOB_URI) | |
end | |
service = Google::Apis::CalendarV3::CalendarService.new | |
service.client_options.application_name = app_name | |
service.authorization = credentials | |
#インポートファイルを指定 | |
IMPORT_FILE_NAME = 'ImportEvent.csv' | |
#ログファイルを指定 | |
LOG_FILE_NAME = 'ImportEvent_log.csv' | |
# インポートリストを読み込み | |
# 文字化け回避のため、SJISから変換せずにUTF-8のまま扱う | |
import_csv = CSV.read(IMPORT_FILE_NAME, headers: true, encoding: 'UTF-8') | |
#ログファイルを開く | |
log_file = CSV.open(LOG_FILE_NAME, 'w') | |
log_file << ['レコードNo','イベントID','イベント名','実行結果','実行結果詳細'] | |
import_csv.each_with_index do |row,rownum| | |
# 参加者リストを配列に変換 | |
attendees_ary = [] | |
cal_id = '' | |
unless row["参加者(メールアドレス)"].nil? | |
guests = row["参加者(メールアドレス)"].each_line.map(&:chomp) | |
# 「不明ユーザー」と「メールアドレスなし」のユーザーは除外する | |
guests.delete("不明ユーザー") | |
guests.delete("メールアドレスなし") | |
# 参加者 | |
guests.each_with_index do |guest,index| | |
# ガルーンAPIからは「登録者」を取得できないので、参加者の一番上を登録対象カレンダーとする | |
cal_id = guest if index == 0 | |
attendees_ary << {email: guest , attendees_omitted: true} | |
end | |
end | |
if cal_id.empty? | |
log_file << [rownum + 1,row["イベントID"],row["予定件名"],'登録先カレンダーが存在しません'] | |
next | |
end | |
# 設備 | |
resources = row["設備(メールアドレス)"].each_line.map(&:chomp) | |
resources.each do |resource| | |
# resourceはReadOnlyプロパティなので、指定する必要はないかも | |
attendees_ary << {email: resource , resource: true} | |
end | |
# 繰り返し予定 | |
recurrence_ary = [] | |
if row["イベントタイプ"] == 'repeat' | |
# セル内改行で保持されていた繰り返し条件から、各値を取得 | |
condition_ary = row["繰り返し条件"].each_line.map(&:chomp) | |
repeat_type = condition_ary.grep(/type:/).join.gsub!(/type:/,'') | |
repeat_day = condition_ary.grep(/day:/).join.gsub!(/day:/,'') | |
repeat_week = condition_ary.grep(/week:/).join.gsub!(/week:/,'') | |
repeat_start_date = condition_ary.grep(/start_date:/).join.gsub!(/start_date:/,'').delete('-') | |
repeat_end_date = condition_ary.grep(/end_date:/).join.gsub!(/end_date:/,'').delete('-') | |
repeat_start_time = condition_ary.grep(/start_time:/).join.gsub!(/start_time:/,'').delete(':') | |
repeat_end_time = condition_ary.grep(/end_time:/).join.gsub!(/end_time:/,'').delete(':') | |
# 曜日指定を数値から文字列に変換 | |
case repeat_week | |
when '1' then repeat_week = 'MO' | |
when '2' then repeat_week = 'TU' | |
when '3' then repeat_week = 'WE' | |
when '4' then repeat_week = 'TH' | |
when '5' then repeat_week = 'FR' | |
when '6' then repeat_week = 'SA' | |
when '7' then repeat_week = 'SU' | |
else | |
log_file << [rownum + 1,row["イベントID"],row["予定件名"],'失敗:想定外の繰り返し条件です。(曜日エラー)'] | |
next | |
end | |
# ガルーンの繰り返し条件をRRULE形式に変換 | |
rrule = '' | |
# 繰り返し終了日は、今回はTimeZone変換がない前提で決め打ち | |
until_str = Time.parse(repeat_end_date).strftime("%Y%m%d") + 'T000000Z' | |
case repeat_type | |
when 'day' then # 毎日 | |
rrule = 'RRULE:FREQ=DAYLY;UNTIL=' + until_str | |
when 'weekday' then # 毎日(土日除く) | |
rrule = 'RRULE:FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR;UNTIL=' + until_str | |
when 'week' then #毎週X曜日 | |
rrule = 'RRULE:FREQ=WEEKLY;BYDAY=' + repeat_week + ';UNTIL=' + until_str | |
when '1stweek' then #毎月第1X曜日 | |
rrule = 'RRULE:FREQ=MONTHLY;BYDAY=1' + repeat_week + ';UNTIL=' + until_str | |
when '2ndweek' then #毎月第2X曜日 | |
rrule = 'RRULE:FREQ=MONTHLY;BYDAY=2' + repeat_week + ';UNTIL=' + until_str | |
when '3rdweek' then #毎月第3X曜日 | |
rrule = 'RRULE:FREQ=MONTHLY;BYDAY=3' + repeat_week + ';UNTIL=' + until_str | |
when '4thweek' then #毎月第4X曜日 | |
rrule = 'RRULE:FREQ=MONTHLY;BYDAY=4' + repeat_week + ';UNTIL=' + until_str | |
when 'lastweek' then #毎月最終X曜日 | |
rrule = 'RRULE:FREQ=MONTHLY;BYDAY=-1' + repeat_week + ';UNTIL=' + until_str | |
when 'month' then #毎月X日 | |
#ガルーンで毎月末は「day=0」となるので、「BYMONTHDAY-1」に変換 | |
if repeat_day == '0' | |
rrule = 'RRULE:FREQ=MONTHLY;BYMONTHDAY=-1' + ';UNTIL=' + until_str | |
else | |
rrule = 'RRULE:FREQ=MONTHLY;BYMONTHDAY=' + repeat_day + ';UNTIL=' + until_str | |
end | |
else | |
log_file << [rownum + 1,row["イベントID"],row["予定件名"],'失敗:想定外の繰り返し条件です。'] | |
next | |
end | |
recurrence_ary << rrule | |
#繰り返し除外日時 | |
unless row["繰り返し除外日時"].nil? | |
row["繰り返し除外日時"].each_line do |exdate| | |
recurrence_ary << 'EXDATE:' + exdate.chomp | |
end | |
end | |
end | |
# Googleカレンダーには「予定区分」がないので、予定件名のプレフィックスとして設定 | |
summary_str = '【' + row["予定区分"] + '】' + row["予定件名"] | |
# ガルーンの非公開予定・限定公開予定は、非公開予定とする | |
if row["公開タイプ"] == 'public' | |
visibility_str = 'default' | |
else | |
visibility_str = 'private' | |
end | |
# 開始・終了時刻のTimeZoneがブランクの場合は、JSTに固定 | |
start_time_zone = row["タイムゾーン(開始)"] | |
start_time_zone = 'Asia/Tokyo' if start_time_zone.nil? | |
end_time_zone = row["タイムゾーン(終了)"] | |
end_time_zone = 'Asia/Tokyo' if end_time_zone.nil? | |
# 各値をハッシュとして保存 | |
event = { | |
summary: summary_str, | |
description: row["予定詳細"], | |
guests_can_modify: true, | |
attendees_omitted: true, | |
visibility: visibility_str, | |
start: { | |
date_time: row["開始時刻"], | |
time_zone: start_time_zone | |
}, | |
end: { | |
date_time: row["終了時刻"], | |
time_zone: end_time_zone | |
}, | |
attendees: attendees_ary, | |
recurrence: recurrence_ary | |
} | |
unless row["参加者"].nil? | |
guests_name = row["参加者"].each_line.map(&:chomp) | |
cal_id = 'shared_calendar_id1@group.calendar.google.com' if guests_name.include?('面接予定') | |
cal_id = 'shared_calendar_id2@group.calendar.google.com' if guests_name.include?('セミナー予定') | |
end | |
begin | |
cal_event = Google::Apis::CalendarV3::Event.new(event) | |
result = service.insert_event(cal_id, cal_event) | |
log_file << [rownum + 1,row["イベントID"],row["予定件名"],'成功:',result.html_link] | |
rescue => e | |
log_file << [rownum + 1,row["イベントID"],row["予定件名"],'失敗:',e] | |
end | |
# 進捗表示 | |
p (rownum + 1).to_s + ' rows completed.' if (rownum + 1) % 10 == 0 | |
end | |
log_file.close | |
puts "インポートが完了しました。" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment