Option Explicit
' ==========================================================
' modGoogleSvc.bas - Goi GoogleSvc.dll qua RPC Bridge V4
'
' Duong di: VBA -> modBridgeAuth (BridgeVba.dll) -> mang (TCP)
' -> BridgeSvc/BridgeHost -> BridgeInvoker64.dll
' -> GoogleSvc.dll (GBridgeExecute) -> Google API
'
' YEU CAU: module nay phu thuoc modBridgeAuth.bas (phai co trong
' cung project) - dung OpenBridge()/RpcCallBuf/PtrToStr tu module do.
'
' TEN DLL TREN MAY CHU: GoogleSvc.dll phai nam dung thu muc BridgeSvc/
' BridgeHost quet DLL (cung thu muc voi BridgeCore.dll, hoac thu muc
' da cau hinh) - chi can goi bang TEN NGAN, khong can duong dan day du,
' giong cach goi TestDll64.dll truoc day.
'
' VE auth.login (GLogin_RPC):
' QUAN TRONG - chi goi duoc qua BridgeHost.exe (chay trong session
' nguoi dung co desktop), KHONG goi duoc qua BridgeSvc.exe (Windows
' Service, Session 0 Isolation - khong co desktop de mo browser).
' Quy trinh dung 1 lan cho moi account:
' 1. Dung BridgeSvc neu dang chay (BridgeSvc /stop)
' 2. Mo BridgeHost.exe tren may chu (co desktop)
' 3. Goi GLogin_RPC tu VBA - browser tu mo TREN MAY CHU
' 4. Dang nhap xong, token da luu (ma hoa DPAPI) - dong BridgeHost
' 5. Khoi dong lai BridgeSvc (BridgeSvc /start) - doc duoc token
' ngay vi DPAPI scope MACHINE
' Sau buoc nay, moi request khac (sheets/gmail/drive) goi qua
' BridgeSvc binh thuong, khong can dang nhap lai.
' ==========================================================
' ==========================================================
' PHAN 0 - CAU HINH
' ==========================================================
Private Const GOOGLESVC_DLL As String = "GoogleSvc.dll"
' Kich thuoc buffer mac dinh cho ket qua tra ve (so ky tu UTF-16).
' Sheets/Drive list co the can buffer lon hon mac dinh - dung
' GBridgeCallAdv neu can chi dinh rieng.
Private Const DEFAULT_BUF_CHARS As Long = 65536
' ==========================================================
' PHAN 1 - HAM GOI GOC (dung chung cho moi action)
' ==========================================================
' GBridgeCall: goi 1 action JSON qua GoogleSvc.dll::GBridgeExecute,
' tra ve JSON response dang String. Day la ham goc - moi wrapper
' Sheets/Gmail/Drive ben duoi deu xay tren ham nay.
'
' reqJson : JSON request day du, vd {"action":"ping"}
' bufChars : kich thuoc buffer ky tu UTF-16 cho ket qua (mac dinh
' DEFAULT_BUF_CHARS neu truyen 0)
'
' Luu y: GoogleSvc.dll dung out-buffer pattern - neu bufChars qua nho,
' KHONG tu cap lai duoc qua RPC (khac voi goi truc tiep in-process),
' vi moi lan goi RPC la 1 round-trip rieng. Neu nghi ket qua dai (vd
' doc nhieu dong Sheet, list nhieu file Drive), tang bufChars khi goi.
Public Function GBridgeCall(ByVal reqJson As String, _
Optional ByVal bufChars As Long = 0) As String
If bufChars <= 0 Then bufChars = DEFAULT_BUF_CHARS
Dim h As LongLong
h = OpenBridge()
If h = 0 Then
GBridgeCall = "{""ok"":false,""error"":""khong ket noi duoc Bridge (kiem tra BridgeSvc/BridgeHost dang chay)""}"
Exit Function
End If
Dim args As String
args = "wstr=" & reqJson & "|wbuf=" & CStr(bufChars) & "|i64=" & CStr(bufChars)
Dim respPtr As LongPtr
respPtr = RpcCallBuf(h, StrPtr(GOOGLESVC_DLL), StrPtr("GBridgeExecute"), StrPtr(args), 0)
If respPtr = 0 Then
GBridgeCall = "{""ok"":false,""error"":""RpcCallBuf tra ve NULL - kiem tra ket noi hoac log BridgeSvc""}"
Exit Function
End If
GBridgeCall = PtrToStr(respPtr)
If Len(GBridgeCall) = 0 Then
GBridgeCall = "{""ok"":false,""error"":""khong nhan duoc response (rong) - tang bufChars neu ket qua co the dai""}"
End If
End Function
' ==========================================================
' PHAN 2 - AUTH
' ==========================================================
' GLogin_RPC: dang nhap Google qua RPC. CHI goi duoc qua BridgeHost.exe
' (xem ghi chu o dau file). Block toi 300s ben may chu trong luc cho
' nguoi dung hoan tat OAuth tren browser may chu.
Public Function GLogin_RPC(Optional ByVal account As String = "default") As String
Dim req As String
req = "{""action"":""auth.login"",""account"":""" & account & """}"
' Dang nhap co the mat thoi gian - dung buffer nho la du (response
' chi la 1 JSON status ngan), khong can tang bufChars.
GLogin_RPC = GBridgeCall(req)
End Function
Public Function GPing_RPC() As String
GPing_RPC = GBridgeCall("{""action"":""ping""}")
End Function
' ==========================================================
' PHAN 3 - SHEETS
' ==========================================================
' GSheetRead_RPC: doc 1 vung trong Google Sheet.
' spreadsheetId : lay tu URL Sheet (giua /d/ va /edit)
' rangeA1 : vd "Sheet1!A1:C10"
Public Function GSheetRead_RPC(ByVal spreadsheetId As String, _
ByVal rangeA1 As String, _
Optional ByVal account As String = "default", _
Optional ByVal bufChars As Long = 0) As String
Dim req As String
req = "{""action"":""sheets.read"",""account"":""" & account & """," & _
"""spreadsheetId"":""" & spreadsheetId & """," & _
"""range"":""" & rangeA1 & """}"
GSheetRead_RPC = GBridgeCall(req, bufChars)
End Function
' GSheetWrite_RPC: ghi du lieu vao 1 vung, GHI DE (khong chen dong).
' valuesJSON: JSON mang 2 chieu, vd [["a","b"],[1,2]]
Public Function GSheetWrite_RPC(ByVal spreadsheetId As String, _
ByVal rangeA1 As String, ByVal valuesJSON As String, _
Optional ByVal account As String = "default") As String
Dim req As String
req = "{""action"":""sheets.write"",""account"":""" & account & """," & _
"""spreadsheetId"":""" & spreadsheetId & """," & _
"""range"":""" & rangeA1 & """," & _
"""values"":" & valuesJSON & "}"
GSheetWrite_RPC = GBridgeCall(req)
End Function
' GSheetAppend_RPC: them dong moi vao CUOI 1 vung (khong ghi de du
' lieu cu) - hop cho log/audit trail.
Public Function GSheetAppend_RPC(ByVal spreadsheetId As String, _
ByVal rangeA1 As String, ByVal valuesJSON As String, _
Optional ByVal account As String = "default") As String
Dim req As String
req = "{""action"":""sheets.append"",""account"":""" & account & """," & _
"""spreadsheetId"":""" & spreadsheetId & """," & _
"""range"":""" & rangeA1 & """," & _
"""values"":" & valuesJSON & "}"
GSheetAppend_RPC = GBridgeCall(req)
End Function
' ==========================================================
' PHAN 4 - GMAIL
' ==========================================================
Public Function GMailSend_RPC(ByVal toAddr As String, ByVal subject As String, _
ByVal body As String, Optional ByVal account As String = "default") As String
Dim req As String
req = "{""action"":""gmail.send"",""account"":""" & account & """," & _
"""to"":""" & toAddr & """," & _
"""subject"":""" & subject & """," & _
"""body"":""" & body & """}"
GMailSend_RPC = GBridgeCall(req)
End Function
' GMailList_RPC: liet ke email theo cu phap tim kiem Gmail
' (vd "from:abc@gmail.com is:unread").
Public Function GMailList_RPC(Optional ByVal query As String = "", _
Optional ByVal maxResults As Long = 20, _
Optional ByVal account As String = "default") As String
Dim req As String
req = "{""action"":""gmail.list"",""account"":""" & account & """," & _
"""query"":""" & query & """," & _
"""maxResults"":" & CStr(maxResults) & "}"
GMailList_RPC = GBridgeCall(req)
End Function
' ==========================================================
' PHAN 5 - DRIVE
' ==========================================================
' GDriveUpload_RPC: upload 1 file tu DIA CUA MAY CHU (khong phai may
' khach) len Drive. QUAN TRONG: localPath la duong dan tren may chay
' BridgeSvc/BridgeHost, vi GoogleSvc.dll thuc thi tren may chu, KHONG
' phai may dang chay VBA. Neu can upload file tu may khach, phai copy
' file do len may chu truoc (vd qua thu muc mang chia se) hoac dung
' co che khac de chuyen file qua mang.
Public Function GDriveUpload_RPC(ByVal localPath As String, _
Optional ByVal folderID As String = "", _
Optional ByVal mimeType As String = "", _
Optional ByVal account As String = "default") As String
Dim req As String
req = "{""action"":""drive.upload"",""account"":""" & account & """," & _
"""localPath"":""" & Replace(localPath, "\", "\\") & """," & _
"""folderId"":""" & folderID & """," & _
"""mimeType"":""" & mimeType & """}"
GDriveUpload_RPC = GBridgeCall(req)
End Function
' GDriveDownload_RPC: download 1 file tu Drive ve DIA MAY CHU (cung
' luu y nhu GDriveUpload_RPC - localPath la duong dan tren may chu).
Public Function GDriveDownload_RPC(ByVal fileID As String, ByVal localPath As String, _
Optional ByVal account As String = "default") As String
Dim req As String
req = "{""action"":""drive.download"",""account"":""" & account & """," & _
"""fileId"":""" & fileID & """," & _
"""localPath"":""" & Replace(localPath, "\", "\\") & """}"
GDriveDownload_RPC = GBridgeCall(req)
End Function
Public Function GDriveList_RPC(Optional ByVal folderID As String = "", _
Optional ByVal maxResults As Long = 50, _
Optional ByVal account As String = "default") As String
Dim req As String
req = "{""action"":""drive.list"",""account"":""" & account & """," & _
"""folderId"":""" & folderID & """," & _
"""maxResults"":" & CStr(maxResults) & "}"
GDriveList_RPC = GBridgeCall(req)
End Function
' ==========================================================
' PHAN 6 - TEST (chay tu Immediate Window, Ctrl+G)
' SUA spreadsheetId/email/file truoc khi chay cho dung du lieu thuc te.
' ==========================================================
Public Sub Test_GPing()
Debug.Print "=== Test_GPing ==="
Debug.Print GPing_RPC()
End Sub
Public Sub Test_GLogin()
Debug.Print "=== Test_GLogin (chi qua BridgeHost.exe) ==="
Debug.Print "Dang dang nhap, cho browser tren MAY CHU..."
Debug.Print GLogin_RPC()
End Sub
Public Sub Test_GSheetRead()
Debug.Print "=== Test_GSheetRead ==="
Debug.Print GSheetRead_RPC("1punDQ-pRaMsoULsUaNthri5pj4ZrhNLFkOD3qkoo", "Sheet1!A1:C10")
End Sub
Public Sub Test_GSheetWrite()
Debug.Print "=== Test_GSheetWrite ==="
Debug.Print GSheetWrite_RPC("1punDQ-pRaMsopULsUaNuHrpj4ZrhNLFkOD3qkoo", _
"Sheet1!A7:B8", "[[""Qua RPC""],[1]]")
End Sub
Public Sub Test_GMailSend()
Debug.Print "=== Test_GMailSend ==="
Debug.Print GMailSend_RPC("kieumanh366377@gmail.com", "Test qua RPC", "Gui tu modGoogleSvc.bas")
End Sub
Public Sub Test_GDriveList()
Debug.Print "=== Test_GDriveList ==="
Debug.Print GDriveList_RPC()
End Sub
Sub Test_Tenant_Login()
Dim req As String
req = "{""action"":""auth.login"",""tenant"":""test1"",""account"":""default""}"
Debug.Print GBridgeCall(req)
End Sub
devblogs.microsoft.com
Nhận xét tổng hợp
