この投稿は Facebook Office 365 コミュニティ Advent Calender 2014 の 12月13日分として投稿するものです。
みなさん、こんにちは。クリエ・イルミネートでノンテク担当の沼口です。
Office 365 の SharePoint Online や SharePoint Server のカスタム リストへのアイテムの追加で悩んだことありませんか。
特に、すでに Excel で管理していたものを SharePoint 上で作成したカスタム リストに移したい場合です。
10件、20件なら手入力すればいいですが、100件、500件、1000件となると、時間もさることながら誤入力も想定されるため、自動化、バッチ化したいところ。（バッチって古いなぁ） 「SharePoint リスト にアイテム追加」で検索すると、C# で SPListItem を使ったり、REST API を使ってのアイテム追加の記事を見つけることができますが、これを、プログラミングなしでしてみよう、というのが今回ご紹介するやり方です。
SharePoint ～ Access 連携の既出ネタといえば、既出ネタですが最後までお付きあいください。
なお、サンプルとして使用したデータは「なんちゃって個人情報」サイト様からお借りしました。ありがとうございます。
さて、プログラミングしないとなると製品やサービスの「機能」を使うしかないわけですが、SharePoint リストの機能として「エクスポート」はあっても、インポートはありません。しかし、その昔、SharePoint が Microsoft Office SharePoint Server (MOSS) と呼ばれていたように、Office 製品側に SharePoint への「エクスポート」機能があったりします。例えば Excel にはテーブル デザイン タブのエクスポートに「テーブルを SharePoint リストにエクスポート」という機能があります。しかし、この機能は Excel のテーブルごと、そっくりそのまま SharePoint リストとして新規作成してしまいます。既存もしくは自分が作成したカスタム リストにアイテムを追加することはできません。
すでに作成済みの SharePoint リストに Excel のデータをリスト アイテムとして追加する、それも VBA を含む、プログラミングなしでやろうとした場合は Excel だけではできませんが、Office 製品群のひとつである Access の機能を使うとプログラミングせずに既存のリストへ Excel のデータをリスト アイテムとして追加できます。では、その手順を追っていきましょう。
1. カスタム リストを作成する
今回はすでに SharePoint 側でカスタム リストが作成されている、という前提で、そのリストに Excel で管理されているデータをリスト アイテムとして追加することが最終目的です。そのため、事前にカスタム リストを作成します。
例として、以下のようなカスタム リストを作成します。
1行テキスト、選択肢、数値、日付と時刻などが使われています。
このリストの入力画面は以下のようなものです。
すでにこのリストには SharePoint のリスト入力画面からデータ（アイテム）が数件入力されているものとします。ここに今まで Excel で管理していた、すでに手元にあるデータを追加したい、それもノンプログラミングで追加したい、というシナリオです。
これまで Excel で管理していたデータは、基本的には今回作成したリストと同じような項目があります。
先に重要な「できないポイント」を提示しておきます。
SharePoint リストの列の作成で「選択肢」を選び、そのオプションで「チェック ボックス（複数選択可能）」を選んだ場合、今回の主旨のプログラミングしないで Excel から Office 製品の機能だけを使って SharePoint リストにアイテムを追加することはできません。（2014/12/13現在、、、）
これは SharePoint リストとのデータ連携に限らず、Access ～ Excel の間でも存在する課題で、Access のテーブル デザインビューのフィールド プロパティで [ルックアップ] タブの [複数の値の許可] を [はい] にしたフィールド（つまり、複数選択可能）への Excel からのデータのインポートは単純な機能だけではできないようです。
この複数選択可能もしくは複数の値の許可が [はい] として設定されたフィールドは、フィールドの中に子のレコードセットが存在していることになり、単純なテキスト、たとえば、カンマ( , ) で区切る、セミコロン( ; )で区切るといったテキストから子のレコードセットを Access のリンク/データインポートの機能で生成することが現状はできません。このインスタンスを作るためには Access VBA でプログラムする必要があります。
[参考] Access 2013|office 2013 DAO で複数値を持つフィールドを操作する方法
http://msdn.microsoft.com/ja-jp/library/ff821054.aspx
一方で、SharePoint リストと Access の間だけでみると、SharePoint リストの「選択肢 チェックボックス（複数選択可能）」の列は Access の「データ型：短いテキスト ルックアップ：コンボ ボックス 複数の値の許可」にマッピングされ、Access から SharePoint リストのアイテムの操作はデータシート ビューから可能です。
今後も Excel からのリスト アイテム追加の作業が発生することが予見されるようであれば、「カレーの食べ方」列での「選択肢 － チェック ボックス（複数選択可能）」をやめ、別の手段をとるほうが得策です。今回は、「カレーのルーの位置」列と「カレーとごはんの関係」列に分け、それぞれの列を「選択肢 ― ラジオ ボタン」（択一）に設定しなおします。
すでに存在している SharePoint リストが複数選択可能な列を利用している場合は、残念ながら上記理由でプログラミングするしかありません。
2. Access で SharePoint リストに接続する
SharePoint リストにアイテムを追加するために、どのようなフォーマット（アイテム構造）を用意すべきかを調べるために、まず、Access を使います。 Excel は SharePoint から Excel への一方通行のデータ接続を提供していますが、Access は SharePoint リストと双方向のデータ接続が可能です。いえ、双方向というよりは、SharePoint リストの入り口、窓口、フロントエンドとなる、と考えた方がいいかもしれません。
SharePoint リストと Access のデータ接続は Excel のそれと同じです。SharePoint リストのリスト タブの [接続とエクスポート] から [Access で開く] をクリックします。
ダイアログが表示され [OK] をおすと、Access が開きます。CustmerList に関連する List （参照先リスト）もリンクされます。
左サイドにあるテーブル名をダブルクリックすれば、データシート ビューが開きます。
この Access の SharePoint リンク テーブルから一旦 Excel にテーブルをエクスポートして、Excel 側でどんなデータを用意すればいいかを確認します。
また、このリンク テーブルは最終的にリンクされている SharePoint リストへのアイテム追加にも使います。
外部データ タブの [エクスポート] の [Excel] をクリックします。
書式設定とレイアウトの保持のチェックはしません。ここ重要です。[OK] をクリックします。
確認のダイアログが開いていますので閉じてください。
このエクスポートされた Excel のワークシートを参考にしてどのようなデータを用意すべきか確認します。
3. Excel で SharePoint リスト用のフォーマットを修正する
以下がエクスポートされたワークシートです。列数が多いので2つの図に分けています。
ピンクの列は SharePoint が付けた列です。別の言い方をすれば、ユーザーによって作成・追加された列ではありません。
オレンジ色の列はユーザーが見えているものとは違う列です。これは他のリストを参照先とする参照列です。[都道府県] と [キャリア] が該当し、ドロップダウン リストから選択した文字列ではなく参照先リストのアイテム ID が割り振られています。
リスト アイテムの入力画面では、白い列とオレンジの列だけ入力すればよく、ピンクの列は SharePoint が用意してくれます。このことから、[タイトル]、[名前]、[フリガナ]、[メールアドレス]、[性別]、[誕生日]、[婚姻]、[都道府県]、[携帯番号]、[キャリア] のユーザー追加の列を用意し、[都道府県] と [キャリア] は該当するアイテム ID を入力すればいいことになります。
[都道府県] と [キャリア] については、SharePoint リストから Access へエクスポートした時に「関連するリスト」として一緒に Access にエクスポートされています。ですから、これらも Excel にエクスポートしてしまいます。
もうひとつこのデータの場合は注意点があります。それは冒頭に説明した「カレーの食べ方」です。（複数選択可能な選択肢の列）
SharePoint リストの列の複数選択可能な選択肢問題で、「カレーの食べ方」列は「カレーのルーの位置」と「カレーとごはんの関係」の2つの列に分けられました。Excel のブックにあるデータは「右ルー・せき止め派」のように「・」で区切られているデータです。これを Excel 側で2つの列に対応する変換をする必要があります。
これで材料はすべて揃いました。Access から Excel にエクスポートした最初のブックにある列名を使って、SharePoint エクスポート用のデータを用意します。 VBA でももちろん処理できますが、プログラミングしない縛りなので、関数でやってみましょう。
元データがあるブックに追加ワークシートとして「エクスポート用」、「都道府県」、「キャリア」ワークシートを追加します。「都道府県」と「キャリア」は Access からエクスポートしたデータをコピーします。エクスポート用は Access からエクスポートしたワークシートから必要な列名（白とオレンジの列）だけをコピーします。
Excelで管理していたカスタマーリスト
エクスポート用の空のワークシート
インポートした都道府県リスト
インポートしたキャリア リスト
[カスタマーリスト]、[都道府県]、[キャリア] の各シートの情報から [エクスポート用] ワークシートにデータを数式を使って準備していくわけです。なお、それぞれの表は「テーブル」形式に変換しています。エクスポート用の表をテーブルにすることは必須ではありませんが、Excel のテーブルで利用できる構造化参照による数式を使ったほうがわかりやすいためテーブルにしています。
以下がポイントになります。このあたりは完全に Excel のテクニックになります。
- 変更なしの列は単純に数式で参照させる
- 都道府県は、カスタマーリストから参照した「都道府県名」を使って、都道府県ワークシートから MATCH/INDEX 関数を使って ID を抜き出す
- キャリアについても同様にキャリアワークシートから MATCH/INDEX 関数を使って ID を抜き出す
- カレーの食べ方では「・」より前をカレーの位置、後をごはんとの関係にいれる
- タイトルは固定で「顧客情報」をすべてのレコードに入力する
- ふりがなが SharePoint リストではフリガナになっているので PHONETIC 関数でカタカナに変換する
数式を参考として以下に示します。
都道府県
=INDEX(PrefTbl[ID],MATCH(CustTbl[@都道府県],PrefTbl[都道府県名],0))
キャリア
=INDEX(CarrierTb[ID],MATCH(CustTbl[@キャリア],CarrierTb[キャリア],0))
ふりがな
=PHONETIC(CustTbl[@ふりがな])
カレーの位置
=MID(CustTbl[@カレーの食べ方],1,FIND(“・”,CustTbl[@カレーの食べ方])-1)
ごはんとの関係
=RIGHT(CustTbl[@カレーの食べ方],LEN(CustTbl[@カレーの食べ方])-FIND(“・”,CustTbl[@カレーの食べ方]))
タイトル
=”顧客情報” 式で文字列を入れれば、テーブルの行追加の新規行でも同じ文字列が入力される
この方法でテスト用30件のエクスポート用のデータをオートフィルで生成しました。
ちなみに、テーブルの右下のフィルハンドルをドラッグしたオートフィルを使って 5,000件以上のデータを再計算させながら準備しましたが、、、、Excel 無茶苦茶速いです。気持ち悪くなるほどに軽いですね。この程度の処理ならば。
実際は、生成された列をチェックするチェックシートなどといった2重、3重チェックを業務では組み込むことになるでしょう。
特に Excel のデータは手入力されているものが多いケースもあり、数字、アルファベット、カタカナの全角・半角や、無駄な空白の削除、選択肢であれば存在チェックなどをします。
4. Access のインポート機能を使って、Excel のデータを SharePoint リストに追加する
SharePoint リストのタブから生成した Access へのデータ接続をそのまま使って、上記で準備した Excel のエクスポート用テーブルを SharePoint リストに追加します。
外部データ タブの [インポートとリンク] の [Excel] をクリックし、[外部データの取り込み – Excel スプレッドシート] ダイアログで、エクスポートするデータのあるブックを指定し、[レコードのコピーを次のテーブルに追加する] で対象となるリストを選択する。この例の場合は [CustmerList] を選択。[OK] をクリックします。
Access の追加先のテーブルが開いているダイアログがでたら [はい] をクリック。
インポートウィザードが開始されます。ワークシートが複数あるので「エクスポート用」を選びます。
[先頭行をフィールド名として使う] がすでにチェックされているので、そのまま [次へ] をクリック。
インポート先のテーブルに間違いがないことを確認して [完了] をクリック。
エラーなく完了すると、インポートした旨のダイアログになります。[閉じる] をクリック。
SharePoint 側で確認します。
もともとあった3件のリスト アイテムの下に30件のアイテムが追加されました。
このインポートは「列名」を比較しています。列名が同じものでなければいけないことに注意してください。そのため今回は一旦 Access から Excel に SharePoint リンク テーブルをエクスポートして、エクスポートしたデータから列名をコピーして使いました。
5. [参考] パフォーマンス
Office 365 SharePoint Online と Excel + Access によるリスト アイテム追加のパフォーマンスを計測してみました。もちろん、ネットワークの実行速度や PC の CPU/メモリーで差が出るところですが、意外に速いと思いました。
件数は 4,996 件を新たに追加して、計 4,999 件にしてみます。
サンプル追加データ（4,996件）
追加された SharePoint アイテム（4,999件）
1回目： 2分52秒 （1秒あたり 29 アイテム）
2回目： 2分53秒
3回目： これ以上やると MS から怒られそうなので中止。。。
終わりに
SharePoint の活用となると、どうしても C#/VB による .NET 開発もしくは HTML/CSS/JavaScript に行きがちですが、Office 製品との組み合わせでもかなりのことができると思います。今回ご紹介したケースなどは、SharePoint リストの特徴（というか、Office 連携すると厳しいところ = 複数選択可能列）を理解した上で SharePoint リストを用意しましたが、結局重要なのはインポート用のデータを用意する Excel のスキルだと思います。Excel 側ではそれほど難しいことをやっているわけではありませんが、実務でよく使われる関数の組み合わせを知っていると知っていないとでは全くやれることが変わるでしょう。
リスト操作で Access をリストとの双方向フロントエンドとして、リスト アイテムの集計、操作を Excel でやる、さらに Excel Services を使ってワークシートや指定範囲、グラフ、ピボットテーブルをページで表示する、アイテム数の多いものは SharePoint Access アプリを使うなど、SharePoint と Office の組みあわせを工夫するなどでかなりのことができると感じています。
最後までお読みいただきありがとうございました。クリエ・イルミネートのノンテク担当 沼口でした。
次の Advent Calender は CLRH.jp の naoki さんです。
イルミネート・ジャパン ブログ 