こんにちは。ごすけです。
最近暖かくなりましたね。お昼はもう上着なしで外出できそうです。
今回はエクセルVBAを使ってスプレッドシートの特定の範囲のみを取得するのにとても苦労したので、その備忘録もこめて調べたことや考えたことを記載しました。
ちなみにですが、私の実力はネットで調べまくってVBAやGASを使える程度の初心者です…。
コードをゴリゴリかける人を本当に尊敬します…。
エクセルの「Power Query」を使用
こちらのサイトを参考に「Power Query」なるものを使用して取り込みました。
【参考サイト】GoogleスプレッドシートをExcelにインポートする／エクセルの神髄様
この機能はExcel2016より標準搭載されているようです。
この機能を使用してこちらのデータを取得していきます。
スプレッドシートのリンクを取得
スプレッドシートのリンクを取得します。
リンクの取得を「リンクを知っている全員」に変更
URLを変更
取得したURLを
https://docs.google.com/spreadsheets/d/1_xxxxxxxxxxxxxxxxxxxx/edit?usp=sharingから
https://docs.google.com/spreadsheets/d/1_xxxxxxxxxxxxxxxxxxxx/export?format=xlsx
に変更
エクセル「Power Query」を使用
エクセルを開いて「データ」タブ→「Webから」を取得
無事に出来ました！
所定のセルへの挿入はクエリと接続から
「データ」タブの「クエリと接続」をクリック
読み込みたいクエリにマウスを移動して「右クリック」
そして「読み込み先」をクリックします。
「データのインポート」のタブから
「テーブル」 → 「既存のワークシート」を選択
「インポートするセル」を選択 → 「OK」を押します。
無事にデータを反映できました。
シートの編集は「Power Query エディターの起動」から
「データ」→「Power Query エディターの起動」
もしくは「クエリと接続」で表示されている「シート」をクリックするとエディターか起動します。
試しに購入先の列データを削除すると
エクセルのシートからも購入先が削除されました。
問題点｜スプレッドシートをリンクを知っている全員が閲覧可にする必要がある
ここで問題になったのでスプレッドシートを「リンクを知っている全員が閲覧可」にする必要があることです。
リンクを知られるリスクは低いかもしれませんが、外に出すとまずい情報も書いている場合この方法で読み込むのには抵抗がありました。
せめてGoogle Drive内で何とか処理できないかなと…。
ここで色々調べた結果、すごくつまずきました。
公開しても問題ない範囲を違うスプレッドシートに書き込む（GAS使用）
今回はGASを使って以下のように実施しました。
- スプレッドシートで公開しても問題ない範囲を別のスプレッドシートに転記する（GAS）
- 転記したスプレッドシートを「リンクを知っている全員が閲覧可」にする
- Exelの「Power Query」で転記先のスプレッドシートを読み込む
- VBAを使ってExel起動時にデータを更新する
なんともまどろっこしいですが、いろいろ考えた挙句この方法にたどり着きました。
別のスプレッドシートへ転記する（Google Apps Script使用）
こちらのサイトを参考にスプレッドシートの値を転記するGASを仕込みます。
まずはスプレッドシートを2つ準備
1つは限定公開、もうひとつを「リンクを知っている全員が閲覧可」に設定
Google Apps Scriptを使って転記するコードを記載。※範囲選択をコピーするシンプルなGASにしています。
function UploadSpreadSheet(){
//現在のスプレットシートを取得する
var ss_copyFrom = SpreadsheetApp.getActiveSheet();
//コピー先のスプレットシートのid
var ss_copyTo = SpreadsheetApp.openById(‘1KIbX78HtDj3XZeONzTSSZTJ5KYCxoXZ68kZBk2Rwklg’);
//コピー先のスプレットシートの中のシート名
var sheet_copyTo = ss_copyTo.getSheetByName(‘シート１’);
//コピー元のシートの中のセルを指定
var copyValue = ss_copyFrom.getRange(‘A1:C10’).getValues();
//コピー先のシートの中のセルを指定して、コピー実行
ss_copyTo.getRange(‘A1:C10’).setValues(copyValue);
}
コードの実行は変更時になるように設定。こちらは状況に合わせて変更してください。
転記したスプレッドシートを公開する
こちらは最初の手順と一緒です。
「限定公開」から「リンクを知っている全員」に変更
Excelの「Power Query」で転記先のスプレッドシートを読み込む
こちらも最初の手順と一緒です
URLを変更して、エクセルのPower Queryで読み込みます。
VBAを使ってExel起動時にデータを更新する
こちらのVBAコードを記載して準備完了。
エクセル起動時にデータが更新されます。
| Private Sub Workbook_Open() ActiveWorkbook.RefreshAll End Sub |
自分が考えてダメだったこと
①スプレッドシートをCSVファイルで出力。その後、エクセルVBAで取り込む。
まず最初に考えたのがGoogleスプレッドシートをCSVで出力、エクセルVBAでCSVを読み込む方法です。
そして課題が発生…。
Google Drive内では同じファイル名で存在可能なのでcreateFileだと同名のCSVファイルが出来る
【参考サイト】グーグルドライブで上書き保存したい hoge.csvが沢山できる
こちらのサイトをコピペして使おうとするもエラー発生…。
【参考サイト】kojのとりあえず日記2｜Google SpreadSheetからCSVファイルを出力するスクリプト
Google Drive APIを使えば行けるかも…というところまではいったがそこからチンプンカンプン…
【参考サイト】teratail｜GASでdrive上のcsvデータに文字コードを指定して書き込む際、ファイルを作成した場合と上書きした場合で文字コードが変わる
②setContent(content)で上書き出来るようだが、エラーが出た。
setContentも試してみたがエラー発生。
テキストファイルなら使えるみたいですが、理解できず断念…。
【参考サイト】Google Apps Script試行錯誤 Blog｜Googleドライブ内のテキストファイルを更新する
まとめ
今回はGoogleスプレッドシートからエクセルに値を読み込む方法を記事にしました。
Power Queryを初めて使用しましたが、今後使える機会はもっとありそうな気がします。
ですが、自分の中では完全消化ではありません。
リンクを公開するのではなく、セキュリティの入ったドライブ内で処理したかったです…。
ネットでそれなりに調べましたが、なかなか難しいです。
エクセルVBAはネット先生がたくさんいるけど、GASはまだ先生が少ない。
APIとかGASとかもっと使いこなせるようになりたい…。
今後の自分とネット先生に期待です。
以上ごすけでした。
【小言】
こんなコード初心者な自分でも、少しでも自動化などを進めると社内でも一目置かれるような存在になっています。
他の人は触ったことがないって人も。
少し学ぶだけで他の人との差をつけられるのでぜひチャレンジしてみてはどうでしょうか？
私もまだまだ頑張ります！
それでは…。
