【Excel VBA⇔Cloud】API×ExcelOnlineデータ検索&更新(テーブル使用)

Excel VBAでMicrosoft Graph REST APIを使用して、SharePoint Online上にあるExcelファイル(テーブル使用)のデータ検索・更新を行う方法を紹介します。

• 操作環境(*)：
  • OS:Windows 11
  • Webブラウザー:Edge
• 使用プラン：Microsoft 365 Business Premium
  *SharePoint Online上のExcelファイルおよびMicrosoft Graph REST APIにアクセスできる組織アカウントでサインインしていること

なお、この記事では、VBAの使用許可や開発メニュー表示といった初期設定や、VBAエディタの使い方の説明は割愛いたします。

ExcelOnline操作用APIについて

ExcelOnline(テーブル使用)のAPIによる操作は、Microsoft Graph REST APIで実施できます。この記事で参考にした公式記事を以下に列挙します。

• 前々回・前回の記事(テーブル未使用時)と同じ
• OAuth2.0認証(ユーザー委任によるアクセストークン取得)：
  ユーザーの代わりにアクセスを取得 - Microsoft Graph | Microsoft Learn
• セッション(永続セッション)：
  Excel の操作:セッション - Microsoft Graph v1.0 | Microsoft Learn
• エラー処理：
  Excel の操作:エラー処理 - Microsoft Graph v1.0 | Microsoft Learn
• 前々回・前回の記事(テーブル未使用時)と異なる
• データ検索(テーブル：タイトル行)：
  Table:HeaderRowRange - Microsoft Graph v1.0 | Microsoft Learn
• データ検索(テーブル：データ行)：
  

  行を一覧表示する - Microsoft Graph v1.0 | Microsoft Learn

• データ追加(テーブル)：
  

  TableRowCollection: add - Microsoft Graph v1.0 | Microsoft Learn

• データ変更(テーブル)：
  

  tablerow を更新する - Microsoft Graph v1.0 | Microsoft Learn

• データ削除(テーブル)：
  

  TableRow: delete - Microsoft Graph v1.0 | Microsoft Learn

*データ変更・削除は公式記事の仕様から変わっているようです。URLの最後の{index番号}は、ItemAt(index={index番号})と記述しないと機能しませんでした

Entra管理C API初期設定

前々回の記事の工程2(テーブル未使用時)と全く同じですので、そちらをご覧ください。
上記で設定済であれば、設定を追加せずに共通利用する形で問題ありません。

SharePoint Online上のExcelファイル用意

前々回の記事の工程3で用意した、SharePointサイト上のExcelファイルを流用します。
今回の記事用のデータシートを追加するため、ファイルをデスクトップアプリで開きます。

図表3-1 SharePointサイト上のExcelファイル

前々回の記事の工程3(テーブル未使用時)で用意したExcelファイルのdataシートをコピーし、使用範囲をテーブルにします。データ検索・更新のAPIではテーブル名を使用します。

• シート名：data_table
• テーブル名：tbl_data

図表3-2 Excelテーブルの作成

実行用Excelファイル用意

前々回の記事の工程4(テーブル未使用時)と全く同じですので、そちらをご覧ください。

*工程8,9で紹介するVBAプロジェクト内のサブプロシージャ・関数の内容は変更点があります

認可コード取得

前々回の記事の工程5(テーブル未使用時)と全く同じですので、そちらをご覧ください。
上記で設定済であれば、設定を追加せずに共通利用する形で問題ありません。

PostmanによるサイトID・ファイルID・トークン取得

前々回の記事の工程6(テーブル未使用時)と全く同じですので、そちらをご覧ください。
上記で設定済であれば、設定を追加せずに共通利用する形で問題ありません。

PostmanによるExcel操作確認(推奨)

対応必須ではありませんが、VBAでの実装イメージ確認のため、Postmanでデータ検索・更新を試してみます。なお、セッションID取得は前の記事の工程7-1(テーブル未使用時)と全く同じですので、この記事での紹介では割愛します。

• データ検索：テーブルのタイトル行とデータ行を取得するAPIを使用します。
• データ更新：変更・削除時は指定したindex(0から始まるデータ行の通し番号)のデータを更新するAPIを使用します。indexを操作するAPIでは、SQLのような条件によるデータ抽出ができません。VBA実装時はデータ検索結果から更新すべきindexを特定する必要があります。
• 追加：データを追加
• 変更：データ検索結果から更新すべきindexを特定し、データを更新
• 削除：データ検索結果から更新すべきindexを特定し、データを削除(*)

*前々回・前回紹介したテーブル未使用時の記事において、行削除を行うと、行削除とデータ再検索が非同期で処理され、順序が逆転して後続のセル範囲特定でズレが生じました。そのため行削除は断念し削除フラグを立てる処理としました。
しかし、今回のテーブル使用時の記事においては、1,000件のデータソースに対し30件更新(追加・更新・削除)を行った限り再検索時のズレが発生しなかったため、データの物理削除を実装しました。
テーブル未使用時/使用時でVBAの処理フローはほぼ同じなので、ズレの発生有無が異なるのは不可解に感じました。テーブル使用時の方が処理が高速なためズレが発生しないのか、より大量のデータを扱う場合はズレが生じる可能性があるのかなど、動作確認時に注視した方が良さそうです

データ検索(タイトル行)

前々回の記事の工程6-2で追加したサイトID取得の設定で[・・・＞Duplicate]を選択し(画面イメージは割愛)、データ検索(タイトル行)の設定を追加します。

• メソッド：GET
• URL：https://graph.microsoft.com/v1.0/sites/{{site_id}}
  /drive/items/{{file_id}}/workbook/tables/tbl_data/headerRowRange
  *上記URLは表示の都合で改行が入っていますが、使用時は改行を削除します
  *{{xxx}}はxxxという変数として扱われます
• Headers＞Content-type：application/json
  *セッションの使用はここでは割愛しています

前々回の記事の工程6-2のサイトID取得時と同様に[Authorization]タブでアクセストークン取得を経て、[Send]を実行します。
画面下部に結果が表示されたら、"text":［［"xxx",・・・,"xxx"］］の各xxxがタイトル行の値になります。

図表7-1 Postman データ検索(タイトル行)

データ検索(データ行)

工程7-1で追加したデータ検索(タイトル行)の設定で[・・・＞Duplicate]を選択し(画面イメージは割愛)、データ検索(データ行)の設定を追加します。

• メソッド：GET
• URL：https://graph.microsoft.com/v1.0/sites/{{site_id}}
  /drive/items/{{file_id}}/workbook/tables/tbl_data/rows
  *上記URLは表示の都合で改行が入っていますが、使用時は改行を削除します
  *{{xxx}}はxxxという変数として扱われます
• Headers＞Content-type：application/json
  *セッションの使用はここでは割愛しています

データ検索(タイトル行)と同様に[Authorization]タブでアクセストークン取得を経て、[Send]を実行します。
画面下部に結果が表示されたら、各"values"要素内の配列が各データ行の値になります。

{"value":[
  {"@odata.context":"・・・", "index":0,"values":[[IDの値,"カテゴリの値",・・・]]},
  {"@odata.context":"・・・", "index":1,"values":[[IDの値,"カテゴリの値",・・・]]},
  ・・・
]}

図表7-2 Postman データ検索(データ行)

データ追加

工程7-1で追加したデータ検索(タイトル行)の設定で[・・・＞Duplicate]を選択し(画面イメージは割愛)、データ追加の設定を追加します。

• メソッド：POST
• URL：https://graph.microsoft.com/v1.0/sites/{{site_id}}
  /drive/items/{{file_id}}/workbook/tables/tbl_data/rows/add
  *上記URLは表示の都合で改行が入っていますが、使用時は改行を削除します
  *{{xxx}}はxxxという変数として扱われます
• Headers＞Content-type：application/json
  *セッションの使用はここでは割愛しています
• ・Body：{"index":null,"values":［［(追加する全列の値をカンマ区切りで設定)］］}
  *上記記述内の大カッコは表示の都合で全角としていますが、使用時は半角にしてください

データ検索(タイトル行)と同様に[Authorization]タブでアクセストークン取得を経て、[Send]を実行します。

図表7-3 Postman データ追加

データ変更

工程7-1で追加したデータ検索(タイトル行)の設定で[・・・＞Duplicate]を選択し(画面イメージは割愛)、データ変更の設定を追加します。

• メソッド：PATCH
• URL：https://graph.microsoft.com/v1.0/sites/{{site_id}}
  /drive/items/{{file_id}}/workbook/tables/tbl_data/rows/ItemAt(index=(インデックス番号))
  *上記URLは表示の都合で改行が入っていますが、使用時は改行を削除します
  *{{xxx}}はxxxという変数として扱われます
• Headers＞Content-type：application/json
  *セッションの使用はここでは割愛しています
• ・Body：{"index":(インデックス番号),"values":［［(追加する全列の値をカンマ区切りで設定)］］}
  *上記記述内の大カッコは表示の都合で全角としていますが、使用時は半角にしてください

データ検索(タイトル行)と同様に[Authorization]タブでアクセストークン取得を経て、[Send]を実行します。

図表7-4 Postman データ変更

データ削除

工程7-1で追加したデータ検索(タイトル行)の設定で[・・・＞Duplicate]を選択し(画面イメージは割愛)、データ削除の設定を追加します。

• メソッド：DELETE
• URL：https://graph.microsoft.com/v1.0/sites/{{site_id}}
  /drive/items/{{file_id}}/workbook/tables/tbl_data/rows/ItemAt(index=(インデックス番号))
  *上記URLは表示の都合で改行が入っていますが、使用時は改行を削除します
  *{{xxx}}はxxxという変数として扱われます
• Headers＞Content-type：application/json
  *セッションの使用はここでは割愛しています

データ検索(タイトル行)と同様に[Authorization]タブでアクセストークン取得を経て、[Send]を実行します。

図表7-5 Postman データ削除

Excel VBAによるデータ検索実装

データ検索のメインとなるプロシージャ(searchSPExcelBook)のソースです。ポイント事項は以下の通りです。

• トークン更新後、セッションID取得・タイトル行とデータ行のデータ検索を行い、検索結果をSearchシートに書き出している
• 検索結果はJson型の変数apiJsonで取得し、エラーがあれば詳細情報をダイアログ表示用の変数に格納する
• 検索が正常終了した時のデータの取り出し方法は、Postmanでデータ検索を実行した時の書式に基づいている
• タイトル行："text"要素内の二次元配列の一行目の値を出力
• データ行："value"の要素数がデータの行数。その中の各"values"内の二次元配列の一行目の値を出力

Private Sub searchSPExcelBook(message, title)

    '変数宣言(Excel関連)
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws_param As Worksheet
    
    '変数宣言(API関連)
    Dim apiJson As Object
    
    '変数宣言(処理変数)
    Dim cnt_row As Long
    Dim cnt_col As Long
    
    'ワークシートの設定&初期化
    Set wb = ThisWorkbook
    Set ws_param = wb.Worksheets("param")
    Set ws = wb.Worksheets("Search")
    ws.Rows("1:" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Delete
    
    'トークン更新、セッション取得
    Call refreshTokens(ws_param)
    Call setSession(ws_param)

    'データ検索(タイトル行)
    Set apiJson = getSearchData(ws_param, "headerRowRange")
    
    'データ検索(タイトル行)でエラーが発生した場合、引数に結果を格納し終了
    If apiJson.Exists("error") Then
      message = "データ検索(タイトル行)でエラーが発生しました。:" & _
        apiJson("error")("code") & ":" & apiJson("error")("message")
      title = "データ検索エラー"
      Exit Sub
    End If
    
    'データ検索(タイトル行)結果をSearchシートに書き出す
    For cnt_col = 0 To apiJson("text")(1).Count - 1
      ws.Cells(1, cnt_col + 1) = "'" & apiJson("text")(1)(cnt_col + 1)
    Next cnt_col
    
    'データ検索(データ行)
    Set apiJson = getSearchData(ws_param, "rows")
    
    'データ検索(データ行)でエラーが発生した場合、引数に結果を格納し終了
    If apiJson.Exists("error") Then
      message = "データ検索(データ行)でエラーが発生しました。:" & _
        apiJson("error")("code") & ":" & apiJson("error")("message")
      title = "データ検索エラー"
      Exit Sub
    End If
    
    'データ検索(データ行)結果をSearchシートに書き出す
    For cnt_row = 0 To apiJson("value").Count - 1
      For cnt_col = 0 To apiJson("value")(1)("values")(1).Count - 1
        If cnt_col = 0 Then 'ID列
          ws.Cells(cnt_row + 2, cnt_col + 1) = _
            apiJson("value")(cnt_row + 1)("values")(1)(cnt_col + 1)
        Else 'ID列以外のデータ行
          ws.Cells(cnt_row + 2, cnt_col + 1) = "'" & _
            apiJson("value")(cnt_row + 1)("values")(1)(cnt_col + 1)
        End If
      Next cnt_col
    Next cnt_row
  
End Sub

データ検索のメインプロシージャから呼び出しているサブ関数・サブプロシージャのソースです。ポイント事項は以下の通りです。

• refreshTokens(トークン更新)：前の記事の工程8と全く同じソース
• setSession(セッションID取得)：前の記事の工程8と全く同じソース
• getSearchData(データ検索)：タイトル行検索とデータ行検索で共通利用するため、URLの一番最後の値を引数で渡している
•  callRestApi(API実行)：前の記事の工程8と全く同じソース

'*****************************************
' refreshTokens：GraphAPIのトークンを更新する
'*****************************************
Private Sub refreshTokens(ws_param As Worksheet)

    '変数宣言(API関連)
    Dim apiMethod As String
    Dim apiUrl As String
    Dim apiBody As String
    Dim apiParams As String
    Dim apiHeaders As New Dictionary
    Dim apiJson As Object

    'アクセストークン取得用の設定
    apiMethod = "POST"
    apiUrl = "https://login.microsoftonline.com/" & _
      ws_param.Cells(8, 2).Value & "/oauth2/v2.0/token"
    apiBody = "grant_type=refresh_token&scope=Files.ReadWrite"
    apiBody = apiBody & "&client_id=" & ws_param.Cells(3, 2).Value
    apiBody = apiBody & "&client_secret=" & ws_param.Cells(4, 2).Value
    apiBody = apiBody & "&refresh_token=" & ws_param.Cells(2, 2).Value
    apiParams = ""
    apiHeaders.RemoveAll
    apiHeaders.Add "Content-Type", "application/x-www-form-urlencoded"
  
    'アクセストークン取得・出力
    Set apiJson = callRestApi(apiMethod, apiUrl, apiBody, apiParams, apiHeaders)
    ws_param.Cells(1, 2).Value = apiJson("access_token")
    ws_param.Cells(2, 2).Value = apiJson("refresh_token")

End Sub

'*****************************************
' setSession：GraphAPIのセッションIDを格納する
'*****************************************
Private Sub setSession(ws_param As Worksheet)

    '変数宣言(API関連)
    Dim apiMethod As String
    Dim apiUrl As String
    Dim apiBody As String
    Dim apiParams As String
    Dim apiHeaders As New Dictionary
    Dim apiJson As Object

    'セッション取得用の設定
    apiMethod = "POST"
    apiUrl = "https://graph.microsoft.com/v1.0/sites/" & _
      ws_param.Cells(5, 2).Value & "/drive/items/" & _
      ws_param.Cells(6, 2).Value & "/workbook/createSession"
    apiBody = "{" & """" & "persistChanges" & """" & ":true}"
    apiParams = ""
    apiHeaders.RemoveAll
    apiHeaders.Add "Content-type", "application/json"
    apiHeaders.Add "Authorization", "Bearer " & ws_param.Cells(1, 2).Value
  
    'セッション取得・出力
    Set apiJson = callRestApi(apiMethod, apiUrl, apiBody, apiParams, apiHeaders)
    ws_param.Cells(7, 2).Value = apiJson("id")

End Sub

'*****************************************
' getSearchData：データ検索をして結果を返す
' ※data_option:headerRowRange=タイトル行,rows=データ行
'*****************************************
Private Function getSearchData(ws_param As Worksheet, data_option As String) As Object

    '変数宣言(API関連)
    Dim apiMethod As String
    Dim apiUrl As String
    Dim apiBody As String
    Dim apiParams As String
    Dim apiHeaders As New Dictionary

    'データ検索
    apiMethod = "GET"
    apiUrl = "https://graph.microsoft.com/v1.0/sites/" & _
      ws_param.Cells(5, 2).Value & "/drive/items/" & _
      ws_param.Cells(6, 2).Value & "/workbook/tables/tbl_data/" & data_option
    apiBody = ""
    apiParams = ""
    apiHeaders.RemoveAll
    apiHeaders.Add "Content-type", "application/json"
    apiHeaders.Add "Authorization", "Bearer " & ws_param.Cells(1, 2).Value
    apiHeaders.Add "workbook-session-id", ws_param.Cells(7, 2).Value
    
    'API実行
    Set getSearchData = callRestApi(apiMethod, apiUrl, apiBody, apiParams, apiHeaders)

End Function

'*****************************************
' callRestApi：API実行(アクセストークン取得時を含む)
'*****************************************
Private Function callRestApi(method As String, url As String, body As String, params As String, headers As Dictionary) As Object

    Dim http As Object
    Dim cnt As Long
  
    'データ削除の正常終了時、戻り値がNothingのためParseJsonでエラー扱いになる。これをスルーする
    On Error Resume Next
  
    'httpリクエストオブジェクトの設定(照設定でMicrosoft Scripting Runtimeが必要)
    'Set http = CreateObject("msxml2.xmlhttp")
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1") 'TLS1.2対応版でないと動作しない
    http.option(6) = True
  
    http.Open method, url & params, False
  
    'httpリクエストヘッダーの設定
    For cnt = 0 To headers.Count - 1
      http.setRequestHeader headers.keys(cnt), headers.Items(cnt)
    Next cnt
  
    'リクエスト送信
    If body = "" Then
      http.send
    Else
      http.send (body)
    End If
  
    'レスポンスの文字列をJson型に変換して返す
    'Debug.Print http.responseText
    Set callRestApi = JsonConverter.ParseJson(http.responseText)

End Function

Excel VBAによるデータ更新実装

データ更新のメインとなるプロシージャ(renewSPExcelBook)のソースです。ポイント事項は以下の通りです。

• 処理の流れは前の記事の工程9とほぼ同じである。ただし、データ削除については削除フラグを立てる変更処理ではなく行削除としている
• 工程7で確認したAPIの仕様に合わせ、前の記事の工程9から変数apiMethodやapiUrl等の値を変更している

Private Sub renewSPExcelBook(message, title)
    
    '変数宣言(Excel関連)
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws_param As Worksheet
    
    '変数宣言(API関連)
    Dim apiJson_db As Object 'データ検索結果
    Dim apiMethod As String
    Dim apiUrl As String
    Dim apiBody As String
    Dim apiParams As String
    Dim apiHeaders As New Dictionary
    Dim apiJson As Object
    
    '変数宣言(処理変数)
    Dim cnt_row_db As Long 'データ検索結果の行カウンタ
    Dim cnt_row As Long 'データ更新対象の行カウンタ
    Dim max_row As Long 'データ更新対象の最大行数
    Dim last_row As Long '最終行(データ追加用)
    Dim new_id As Long 'ID最大値(データ追加用)
    Dim data_index As String '更新インデックス(データ変更・削除用)
    
    '出力先ワークシートの設定
    Set wb = ThisWorkbook
    Set ws_param = wb.Worksheets("param")
    Set ws = wb.Worksheets("Renew")
    
    'Renewシートの最大行数を求める(タイトル行は除く)
    max_row = ws.Range("A" & ws.Rows.Count).End(xlUp).Row - 1
    
    'トークン更新、セッション取得
    Call refreshTokens(ws_param)
    Call setSession(ws_param)

    'データ検索
    Set apiJson_db = getSearchData(ws_param, "rows")
    
    'データ検索でエラーが発生した場合、引数に結果を格納し終了
    If apiJson_db.Exists("error") Then
      message = "データ検索でエラーが発生しました。:" & _
        apiJson_db("error")("code") & ":" & apiJson_db("error")("message")
      title = "データ検索エラー"
      Exit Sub
    End If
    
    '検索結果から最終行とID列の最大値を求める
    last_row = apiJson_db("value").Count
    new_id = getNewId(apiJson_db)
    
    'API変数設定(共通)
    apiHeaders.RemoveAll
    apiHeaders.Add "Content-type", "application/json"
    apiHeaders.Add "Authorization", "Bearer " & ws_param.Cells(1, 2).Value
    apiHeaders.Add "workbook-session-id", ws_param.Cells(7, 2).Value

    For cnt_row = 0 To max_row - 1 '更新対象ループ
      
      'データ追加:API変数の設定(個別)
      If ws.Cells(cnt_row + 2, 1).Value = "ins" Then
        apiMethod = "POST"
        apiUrl = "https://graph.microsoft.com/v1.0/sites/" & _
          ws_param.Cells(5, 2).Value & "/drive/items/" & _
          ws_param.Cells(6, 2).Value & "/workbook/tables/tbl_data/rows/add"
        apiBody = getBody(ws, cnt_row, new_id + 1, "null")
        
      'データ変更,削除:更新セル範囲の特定(ID列の値が一致する行)
      Else
        For cnt_row_db = 0 To apiJson_db("value").Count - 1 '検索結果ループ
          If apiJson_db("value")(cnt_row_db + 1)("values")(1)(1) = ws.Cells(cnt_row + 2, 2).Value Then
            data_index = apiJson_db("value")(cnt_row_db + 1)("index")
            Exit For
          End If
        Next cnt_row_db
        
        'データ削除
        If ws.Cells(cnt_row + 2, 1).Value = "del" Then
          '行削除
          apiMethod = "DELETE"
          apiUrl = "https://graph.microsoft.com/v1.0/sites/" & _
            ws_param.Cells(5, 2).Value & "/drive/items/" & ws_param.Cells(6, 2).Value & _
            "/workbook/tables/tbl_data/rows/ItemAt(index=" & data_index & ")"
          apiBody = ""
        
        'データ変更
        ElseIf ws.Cells(cnt_row + 2, 1).Value = "upd" Then
          apiMethod = "PATCH"
          apiUrl = "https://graph.microsoft.com/v1.0/sites/" & _
            ws_param.Cells(5, 2).Value & "/drive/items/" & ws_param.Cells(6, 2).Value & _
            "/workbook/tables/tbl_data/rows/ItemAt(index=" & data_index & ")"
          apiBody = getBody(ws, cnt_row, ws.Cells(cnt_row + 2, 2).Value, CStr(data_index))
        End If
        
      End If
      
      'データ反映実行(API実行)
      Set apiJson = callRestApi(apiMethod, apiUrl, apiBody, apiParams, apiHeaders)
      
      'データ反映でエラーが発生した場合、ダイアログを表示する
      If Not apiJson Is Nothing Then
        If apiJson.Exists("error") Then
          message = CStr(cnt_row + 2) & "番目のデータ反映でエラーが発生しました。:" & _
            apiJson("error")("code") & ":" & apiJson("error")("message")
          title = "データ反映エラー"
          GoTo NextLoop
        End If
      End If
      
      'データ追加:最終行とID列の最大値をカウントアップ
      If ws.Cells(cnt_row + 2, 1).Value = "ins" Then
        last_row = last_row + 1
        new_id = new_id + 1
      
      'データ削除:行削除後の再検索,最終行とID列の最大値の再算出
      ElseIf ws.Cells(cnt_row + 2, 1).Value = "del" Then
        Set apiJson_db = getSearchData(ws_param, "rows")
        last_row = apiJson_db("value").Count
        new_id = getNewId(apiJson_db)
      End If
      
NextLoop:
    Next cnt_row
    
End Sub

データ更新のメインプロシージャから呼び出しているサブ関数(データ検索と重複する関数を除く)のソースです。ポイント事項は以下の通りです。

• getNewId(ID列の最大値取得)：データ検索結果の"value"内にある各"values"内の一行目のID列(配列の1列目)の値を比較し、最大値を取得している
• getBody(データ更新用apiBody設定)：データ追加・変更APIの仕様に合わせ、{ "index":(追加時はnull/変更時はインデックス番号),"values": ［［(追加・変更データのカンマ区切りの値)］］ }の形になるよう整形している。ID列のみ数値のため、値を"で囲んでいない

'*****************************************
' getNewId：データ検索結果からID列の最大値を返す
'*****************************************
Private Function getNewId(apiJson As Object) As Long

    Dim cnt_row As Long
    Dim new_id As Long
    
    new_id = 0
    For cnt_row = 0 To apiJson("value").Count - 1 '検索結果ループ
      If CLng(apiJson("value")(cnt_row + 1)("values")(1)(1)) > new_id Then
        new_id = CLng(apiJson("value")(cnt_row + 1)("values")(1)(1))
      End If
    Next cnt_row
    
    getNewId = new_id
    
End Function

'*****************************************
' getBody：ボディ格納(データ追加・変更用)
' { "values": [[value(num), "value2(string)", "value3(string)"]] }
'*****************************************
Private Function getBody(ws As Worksheet, cnt_row As Long, new_id As Long, data_index As String) As String

    Dim max_col As Long
    Dim cnt_col As Long
    Dim apiBody As String
  
    'Renewシートの最大行列数を求める(Action列は除く)
    max_col = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column - 1
  
    apiBody = "{" & """" & "index" & """" & ":" & data_index & _
      "," & """" & "values" & """" & ":[["
    For cnt_col = 0 To max_col - 1
      If cnt_col > 0 Then
          apiBody = apiBody & ","
      End If
      If cnt_col = 0 Then '数値:ID列
        apiBody = apiBody & CStr(new_id)
      Else '文字列:ID列以外
        apiBody = apiBody & """" & "'" & ws.Cells(cnt_row + 2, cnt_col + 2).Value & """"
      End If
    Next
    apiBody = apiBody & "]]}"
    
    'Bodyの値を返す
    getBody = apiBody

End Function

おわりに

APIでSharePoint Online上にあるExcelファイルのデータを検索・更新する方法について、前々回・前回の記事ではテーブル未使用時、今回はテーブル使用時の実装例を紹介しました。
今回のようにテーブルを使用した方が処理が若干速いのと、私が確認した限りでは行削除を行っても問題なく動作したため、お奨めします。
ただし、データ変更・削除におけるURLの指定方法について、2025年9月中旬時点で公式ドキュメントの記載が実際と異なっていたため、注意が必要です。

当ブログ内の関連記事

elmgrn.hatenablog.com

elmgrn.hatenablog.com

elmgrn.hatenablog.com

elmgrn.hatenablog.com

elmgrn.hatenablog.com