経緯
教育機関内で、職員の業務の為にカレンダー形式で日々収集している就職に関する情報を、学生にも共有できないかというのが始まり。
折しも学生及び職員全員にGoogleアカウントが配布されることになり、全校でGoogleサービスを利用できるようになったことから、これを用いて学生に情報を共有しようというのがねらい。
Excelの運用を大きく変えず、Googleカレンダーを導入したいという人のもとに届いたら幸いです。
添付資料として、使用しているExcelファイルをGoogleスプレッドシートに変換したものがこちら
https://drive.google.com/open?id=1wUAwGGl3-MFQy5OQbqZE1REMfawk-odsrinYAP6T9Eo
Excelへの入力はこのように行われている(Googleスプレッドシートでの画面)
要件
現状、情報の入力先がExcelファイルであるが、これは他の業務でも使用されるため、この形式には変更を及ぼさないこと。
方法
Googleカレンダーは、CSVファイルによる予定のインポートに対応しているためこれを活用していくものとする。
よって、メインはExcelファイルのCSVによる書き出しとなる。
注意点
- CSVファイルの書き出しにあたって、GoogleカレンダーへインポートするにはGoogleカレンダー側の形式に沿ってCSVを出力しないといけない。そこで、現在Excelへ入力している内容をGoogleカレンダー側の形式に変換する必要が出てくる。
- Googleカレンダーは予定の重複を検知しないため、過去にインポートした予定は再びインポートしないようにしないといけない。すなわち、増分によるインポートを実行しないといけない。
手順1 インポート用データの抽出
増分インポート実現のため、CSV出力対象のデータだけを用意するシートを新たに作成する。表の構造は日々の入力を実施しているシートと変更せず、コピペで入力できるようにする。
また、入力する情報に入力日の属性(添付のファイルでいう"更新日"フィールド)を持たせることで、増分を管理しやすくなる。
対象データだけを用意するシート
普段入力を実施しているシートと構造を変えないことで、コピペで対象のデータを用意できる。
手順2 Googleカレンダーの形式への変換
出力対象のデータを用意したら、これをGoogleカレンダー側の形式に沿うように変換しないといけない。 Google カレンダーへの予定の読み込み に従ってヘッダーを設定したシートを新たに用意する。
ヘッダーを設定し、Googleカレンダーの形式に沿ったシート
ここで、SubjectやDescriptionといった予定の内容となる部分は、出力対象のデータを用意したシートから関数で引っ張ってくる。画像では、Subjectに企業名+予定のジャンル。Descriptionには、企業に関する情報と予定の詳細が自動で入力されるように関数を設定した。
手順3 CSVファイルの作成
いよいよGoogleカレンダーへインポートするためのCSVファイルを出力する。ここで注意しないといけないのが、Googleは文字コードがUTF-8にしか対応していないため、これに対応しないといけない。
もっとも簡単にできる手順でいえば、Excel上で名前を付けて保存を選択して、ファイル形式をCSV(カンマ区切り)に指定して保存し、保存したファイルをメモ帳で開いてそこでも名前を付けて保存を実行してその際に文字コードをANSIからUTF-8を指定する...というのがあるが、せっかくなのでマクロを組むことにする。
で、そのマクロの内容がこちら
Sub カレンダー用CSV出力()
'変数wsにCSV変換を実施するワークシートを保存
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(5)
'変数csvFileにCSVファイルの出力先を保存
Dim csvFile As String
csvFile = "C:\Desktop\" & Worksheets(5).Name & ".csv"
'ADODB.Streamオブジェクトを生成
Dim adoSt As Object
Set adoSt = CreateObject("ADODB.Stream")
Dim strLine As String
Dim i As Long, j As Long
i = 1
With adoSt
.Charset = "UTF-8"
.Open
'シート上のi行,1列目のセルが""でない間繰り返す
Do While ws.Cells(i, 1).Value <> ""
strLine = ""
'subjectからprivateまでを繰り返す
For j = 1 To 9
strLine = strLine & ws.Cells(i, j).Value & ","
Next j
strLine = strLine & ws.Cells(i, j).Value
.WriteText strLine & vbCrLf
i = i + 1
Loop
'CSVファイルの保存。同名のファイルがあれば上書き保存し、なければ作成して保存
.SaveToFile csvFile, 2: adSaveCreateOverWrite = 2
.Close
End With
'メッセージボックスを出力する
MsgBox csvFile & "に書き出しました"
End Sub
先に述べたように、GoogleがUTF-8しか受け付けていないため、それに対応するようADODB.Streamオブジェクトを使用する。
めんどくさければ一度Shift-JISで吐いて後から手動で変換しても変わらないが、せっかくマクロ組むのだから全てやってくれるように組んだ。
###手順4 CSVインポート
ここまでで全ての準備が整ったので、GoogleカレンダーへCSVをインポートする。CSVの出力先を弄ってGASを組んで自動でインポートさせることもできるが、今回は手動でGUIに従ってインポートする。(GASで自動アップデートは別のところで実装したので、そちらもいつか記事にする予定)
Googleにログインし、Googleカレンダーを開き、右上の歯車をクリックして設定を選択し、左側のメニューから"インポート/エクスポート"をクリックし、"パソコンからファイルを選択"に先程用意したCSVファイルを選び、下の"インポート"ボタンをクリックすることでGoogleカレンダーへCSVファイルから予定がインポートされる。
この画面でこのようにファイルを選択する
インポート後に、Googleカレンダーに予定が追加されていたら成功！
#おわりに
今回は情報を共有する手段としてGoogleカレンダーを採用し、かつ既存の業務の形をなるべく変えないようにするという点に重きを置いた内容でした。資料としてリンクを掲載したGoogleスプレッドシートの"就職カレンダー"シートがこれまで業務で使用していたシートで、残りの2シートが今回の記事の中で追加したシートです。データの処理・加工用に2シート追加するだけでGoogleカレンダーでの共有が可能になると考えれば実用性が高いんじゃないかと思います。
##参考にしたサイト
エクセルVBAで文字コードUTF-8のCSVファイルを書き出す方法
https://tonari-it.com/excel-vba-utf8-write/