Excelからスプレッドシートのテーブルデータを取得する【ExcelVBA × Google Apps Script】

ExcelVBAで指定したGoogleスプレッドシートのテーブルデータを取得していくマクロです。

GoogleAppsScriptでWebアプリを作成し、機能としてはdoGet関数でデータを文字列として返す。

ExcelVBAからはGetリクエストを送り、文字列を受け取り、それをテーブルと同じ形に戻してシートに入れる命令。

実際に使用したサンプルコード（GoogleAppsScript側）------------------------------------

function doGet() {

//Sheetの設定
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

//シートのデータ範囲から値を取得
let values = sheet.getDataRange().getValues();

//データの縦のサイズと横のサイズを取得する
let row = values.length-1;
let col = values[0].length-1;

//join() メソッドは、配列の要素を引数に指定した区切り文字で結合して1つの文字列にする
let response = values.join(",");

//完成した文字列の1番目と2番目に行と列のサイズを入れる
response = row + "," + col + "," + response;

//完成した文字列をテキスト文字列として返す
return ContentService.createTextOutput(response);

}

実際に使用したコード（GoogleAppsScript側）------------------------------------

実際に使用したコード（ExcelVBA側）------------------------------------

Sub Sample()

 'httpRequestを使用する為の準備設定
 Dim httpRequest As New WinHttp.WinHttpRequest

 ' HTTPリクエストを作成（設定部分）
 httpRequest.Open "GET", "〇〇〇", False
 httpRequest.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"

 '設定した内容でリクエストを送信
 httpRequest.Send

 ' レスポンスを表示
 MsgBox httpRequest.ResponseText

 'split関数を使用してカンマで分割して配列にする
 Dim myary As Variant
 myary = Split(httpRequest.ResponseText, ",")

'最終的に入れる配列を用意
Dim lastary() As Variant

 '1つ目に行数が入っている（縦のサイズ）

 '2つ目に列数が入っている（横のサイズ）
 ReDim lastary(myary(0), myary(1)) As Variant

 '繰り返し用の変数
 Dim cnt As Integer
 Dim i As Integer  '列用の変数
 Dim j As Integer  '行用の変数
 i = 0
 j = 0

 '配列を2次元配列に入れ直していく、最初の2つは行と列のサイズの為
 'スタートは+2をする
 For cnt = LBound(myary, 1) + 2 To UBound(myary, 1)

        '1つずつ順番に入れる
         lastary(j, i) = myary(cnt)
         i = i + 1
         '横のサイズを超えたら次の行に行くために
         '０に戻して行を＋1する
         If i > UBound(myary, 1) Then
             i = 0
             j = j + 1
         End If

 Next

 '配列の最大値は０からなのでプラス１をして縦・横を合わせて配列を入れる
 Sheet1.Range("A1").Resize(UBound(lastary, 1) + 1, UBound(lastary, 2) + 1).Value = lastary

End Sub

実際に使用したコード（ExcelVBA側）------------------------------------

正直そのままコピペで、WebアプリのURLだけ、〇〇のところを変えてあげれば使用可能です。

Webアプリがアクセスユーザーが全員になっていないとExcel側からはアクセスできません。

Sheet1の部分を指定したシート名に変更し、Range("A1")の部分を入れたい先頭セルにすれば汎用的に使用可能。

※セキュリティ関連は考慮していません（WebアプリのURLがわかればだれでもアクセスできてしまう）のでご注意。