Một ý tưởng viết macro VBA tương tác với Google Drive từ Excel

[nguyendang95]

Xin chào mọi người.
Sau một thời gian nhận thấy có nhiều người dùng đặt vấn đề về khả năng macro VBA trong Excel tương tác với Google Drive nói chung và các dịch vụ của Google nói riêng và sau khi nghiên cứu cách viết macro VBA cũng như ngâm cứu tài liệu của Google.
Mình xin chia sẻ một ý tưởng thông qua một tệp ví dụ đính kèm theo bài viết này với những chức năng như sau:

• Quản lý tập tin, thư mục trong một thư mục nhất định trên Google Drive với những chức năng cơ bản như tải lên, tải xuống tập tin, đổi tên, xóa vĩnh viễn tập tin/thư mục.
• Tạo email, tải tệp đính kèm lên thư mục trong Google Drive và lấy shareable link đưa vào email mới tạo.
• Tiến trình tải lên/tải xuống tập tin được ghi nhận ở thanh Status Bar của Excel (góc trái dưới cùng)

Macro VBA trong tệp Excel trên có sử dụng:

1. Thư viện VBA-JSON của tác giả timhall: VBA-JSON
2. Hai hàm URLEncode và URLDecode (ExcelVBA.ru): URL Encode and Decode VBA functions for Excel on Mac or Windows including UTF-8 support. Source: ExcelVBA.ru

Mời mọi người xem tệp bên dưới.

 

[Kiều Mạnh]

ý tưởng hay đó ... nhưng cảm giác thấy hơi cà chua

Thử nghiên cứu thêm Web Server xem nó cũng hay đấy .... Hoặc cái ISS phía sau cái ISS là 1 Web Server đấy có điều muốn sử dụng nó phải cấu hình = tay ... còn cấu hình = code cũng rất cà chua

còn nghe tây trên google nó tù đồn ( 9 xác hay ko 9 xác thì = No biết ) là phía sau cái Ms Server cũng là 1 cái Web Server chạy ngầm

 

[nguyendang95]

ý tưởng hay đó ... nhưng cảm giác thấy hơi cà chua

Thử nghiên cứu thêm Web Server xem nó cũng hay đấy .... Hoặc cái ISS phía sau cái ISS là 1 Web Server đấy có điều muốn sử dụng nó phải cấu hình = tay ... còn cấu hình = code cũng rất cà chua

còn nghe tây trên google nó tù đồn ( 9 xác hay ko 9 xác thì = No biết ) là phía sau cái Ms Server cũng là 1 cái Web Server chạy ngầm

Nói ra thì xấu hổ, nhưng thật ra mình không biết gì về lập trình web, với lại mình đã nghĩ nát cả óc rồi mà không còn cách nào khác mà nên chỉ đành dùng một Userform như một trình duyệt web (Internet Explorer) để xin quyền truy cập vào Drive của người dùng (Microsoft Edge không cung cấp COM DLL để chạy được trên VBA mà phải động đến SeleniumBasic thì rất phiền phức). Rất may là trình duyệt Internet Explorer tuy đã rất cũ nhưng vẫn còn hiển thị tốt trang web của Google lẫn Microsoft, tuy nhiên một số trang (như Notion API chẳng hạn) thì Internet Explorer lại không hiển thị được. Ước gì Microsoft chịu cập nhật cho cái WebView2 như bên .NET thì tốt quá.
Nếu sau năm 2029 Microsoft loại bỏ hoàn toàn Internet Explorer khỏi Windows thì ý tưởng trên sẽ phải xếp xó mất.

 

[nguyendang95]

Trong tệp Excel trên có chứa một Class Module tên là GoogleOAuth2, đây là thành phần chính chịu trách nhiệm xử lý các vấn đề liên quan đến xác thực OAuth2 như:

1. Lấy Authorization Code khi người dùng cho phép
2. Lấy Access Token sau khi đã có Authorization Code
3. Lưu trữ Access Token cùng với Refresh Token vào một nơi thích hợp để tiện sử dụng mà không cần xin quyền truy cập từ người dùng về sau
4. Kiểm tra thời hạn của Access Token, nếu hết hạn thì dùng Refresh Token để lấy Access Token mới (theo Google, Access Token chỉ có tác dụng trong vòng một tiếng
5. Thu hồi Access Token cùng với Refresh Token khi cần thiết

Để trình bày một ví dụ khác về chủ đề bài viết nói trên, chúng ta thử viết một macro chèn, xóa và lấy dữ liệu từ một vùng (Range) bất kỳ trong một Spreadsheet (Google Sheets) trên Drive.
Đầu tiên, tạo một số Class Module như ClearValuesRequest, GetValuesRequest, UpdateValuesRequest và ValueRange (chi tiết vui lòng xem tệp đính kèm theo bài viết này)
Tiếp theo, viết một số thủ tục để thực hiện chức năng chèn, xóa và lấy dữ liệu từ một Range trong Spreadsheet:

Option Explicit

Public Function GetRangeValues(SpreadsheetId As String, A1Range As String) As Variant
    Dim objGoogleOAuth2 As GoogleOAuth2
    Dim objWinHttpRequest As WinHttp.WinHttpRequest
    Dim objDict As Scripting.Dictionary
    Dim objGetValuesRequest As GetValuesRequest
    Set objGetValuesRequest = New GetValuesRequest
    With objGetValuesRequest
        .SpreadsheetId = SpreadsheetId
        .Range = A1Range
    End With
    Set objGoogleOAuth2 = GetCurrentSession
    Set objWinHttpRequest = New WinHttp.WinHttpRequest
    With objWinHttpRequest
        .Open "GET", "https://sheets.googleapis.com/v4/spreadsheets/" & objGetValuesRequest.ConstructQueryParameters, True
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Accept", "application/json"
        .SetRequestHeader "Authorization", "Bearer " & objGoogleOAuth2.AccessToken
        .Send
        .WaitForResponse
        If .Status = 200 Then
            Set objDict = JsonConverter.ParseJson(.ResponseText)
            If Not IsEmpty(objDict.Item("values")) Then
                GetRangeValues = ConvertJsonToArray(objDict)
            End If
        Else
            MsgBox objDict.Item("message"), vbInformation, "Error"
        End If
    End With
End Function

Public Sub SetRangeValues(SpreadsheetId As String, R1Range As String, Values As Variant)
    Dim objGoogleOAuth2 As GoogleOAuth2
    Dim objWinHttpRequest As WinHttp.WinHttpRequest
    Dim objDict As Scripting.Dictionary
    Dim objUpdateValuesRequest As UpdateValuesRequest
    Dim objValueRange As ValueRange
    Set objValueRange = New ValueRange
    With objValueRange
        .Range = R1Range
        .Values = Values
    End With
    Set objUpdateValuesRequest = New UpdateValuesRequest
    With objUpdateValuesRequest
        .SpreadsheetId = SpreadsheetId
        .Range = R1Range
        .ValueInputOption = USER_ENTERED
        .IncludeValuesInResponse = "false"
    End With
    Set objGoogleOAuth2 = GetCurrentSession
    Set objWinHttpRequest = New WinHttp.WinHttpRequest
    With objWinHttpRequest
        .Open "PUT", "https://sheets.googleapis.com/v4/spreadsheets/" & objUpdateValuesRequest.ConstructQueryParameters, True
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Accept", "application/json"
        .SetRequestHeader "Authorization", "Bearer " & objGoogleOAuth2.AccessToken
        .Send JsonConverter.ConvertToJson(objValueRange.ToJson)
        .WaitForResponse
        If .Status <> 200 Then
            MsgBox "An error occurred", vbExclamation, "Error"
        End If
    End With
End Sub

Public Sub ClearRangeValues(SpreadsheetId As String, A1OrR1Range As String)
    Dim objGoogleOAuth2 As GoogleOAuth2
    Dim objWinHttpRequest As WinHttp.WinHttpRequest
    Dim objDict As Scripting.Dictionary
    Dim objClearValuesRequest As ClearValuesRequest
    Set objClearValuesRequest = New ClearValuesRequest
    With objClearValuesRequest
        .SpreadsheetId = SpreadsheetId
        .Range = A1OrR1Range
    End With
    Set objGoogleOAuth2 = GetCurrentSession
    Set objWinHttpRequest = New WinHttp.WinHttpRequest
    With objWinHttpRequest
        .Open "POST", "https://sheets.googleapis.com/v4/spreadsheets/" & objClearValuesRequest.ConstructQueryParameters, True
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Accept", "application/json"
        .SetRequestHeader "Authorization", "Bearer " & objGoogleOAuth2.AccessToken
        .Send
        .WaitForResponse
        If .Status <> 200 Then
            MsgBox "An error occurred", vbExclamation, "Error"
        End If
    End With
End Sub

Public Function GetCurrentSession() As GoogleOAuth2
    Dim objGoogleOAuth2 As GoogleOAuth2
    Set objGoogleOAuth2 = New GoogleOAuth2
    With objGoogleOAuth2
        .ApplicationName = "UploadFileToGoogleDrive"
        .ClientID = "364263102841-sqrgk7k3dv0tnt1eu2mdg1a5o4283110.apps.googleusercontent.com"
        .ClientSecret = "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"
        .Scope = Array("https://www.googleapis.com/auth/drive")
        .AuthorizeOAuth2
    End With
    Set GetCurrentSession = objGoogleOAuth2
End Function

Private Function Quote(Text As String) As String
    Quote = Chr(34) & Text & Chr(34)
End Function

Private Function ConvertJsonToArray(Json As Scripting.Dictionary) As Variant
    Dim arrValues() As Variant
    Dim i As Long, j As Long
    If Json.Item("values").Count > 1 And Json.Item("values")(1).Count > 1 Then
        ReDim arrValues(1 To Json.Item("values").Count, 1 To Json.Item("values")(1).Count)
        For i = 1 To Json.Item("values").Count
            For j = 1 To Json.Item("values")(i).Count
                arrValues(i, j) = Json.Item("values")(i)(j)
                Debug.Print arrValues(i, j)
            Next
        Next
    ElseIf Json.Item("values").Count = 1 And Json.Item("values")(1).Count > 1 Then
        ReDim arrValues(1 To 1, 1 To Json.Item("values")(1).Count)
        For i = 1 To Json.Item("values")(1).Count
            arrValues(1, i) = Json.Item("values")(1)(i)
        Next
    ElseIf Json.Item("values").Count = 1 And Json.Item("values")(1).Count = 1 Then
        ReDim arrValues(1 To 1)
        arrValues(1) = Json.Item("values")(1)(1)
    End If
    ConvertJsonToArray = arrValues
End Function

Chạy thử macro (trong ví dụ này chỉ trình bày chức năng chèn dữ liệu vào Range trên Google Sheets):

Private Sub SetValues()
    Dim objGoogleOAuth2 As GoogleOAuth2
    Set objGoogleOAuth2 = GetCurrentSession
    SetRangeValues "1t4J0vU57rAv8fPpGWc1J53mZC4A-m0UMfKNcEyVAvmE", "Sheet1!" & Range("A1:J20").Address(True, True, xlR1C1), Range("A1:J20").Value
End Sub

Thủ tục SetRangeValues có ba tham số, tham số thứ nhất là mã Id của Spreadsheet, tệp Spreadsheet này có thể nằm trên Drive của người dùng hoặc được chia sẻ với người dùng với quyền chỉnh sửa, tham số thứ hai là địa chỉ dạng A1 của Range trong tệp Spreadsheet cần chèn (vd: Sheet1!A1:A2), tham số cuối cùng là một giá trị đơn lẻ hoặc một mảng hai chiều để chèn vào Range trong tệp Spreadsheet.



So sánh kết quả:



Tệp Spreadsheet dùng làm mẫu cho bài viết này:

LuyenTapGoogleAppsScript

TongKetBanHang Mã MH,Mặt hàng,Đơn giá,Số lượng,Phí chuyên chở,Thành tiền,Tổng cộng HD1,Đĩa cứng,49,60,0.49,2940.49 FD1,Đĩa mềm,2.5,70,0.025,175.025 Hello,Mouse,3,30,0.03,90.03 SD1,SD Ram,13,120,0.13,1560.13 DD1,DD Ram,27,100,0.27,2700.27 HD2,Đĩa cứng,50,50,2.5,2502.5 MS2,Mouse,3.5,65,0.175,227.675

docs.google.com

Tham khảo thêm:
Method: spreadsheets.values.update

 

[haipvg]

chào bác @nguyendang95
Mình tải file của bác ở #1 về nhưng nó không hoạt động nữa. Báo lỗi tại Public Sub AuthorizeOAuth2()
Không biết là nếu chỉ cần lấy danh sách đường link file thôi thì có được không bác?

 

[haipvg]

chào bác @nguyendang95
Mình tải file của bác ở #1 về nhưng nó không hoạt động nữa. Báo lỗi tại Public Sub AuthorizeOAuth2()
Không biết là nếu chỉ cần lấy danh sách đường link file thôi thì có được không bác?

Sau khi mày mò thay đổi Client ID và Client secret thì gặp thông báo lỗi:

Đăng nhập - Tài khoản Google
Bạn không thể đăng nhập vào ứng dụng này vì ứng dụng không tuân thủ chính sách OAuth 2.0 của Google.

Nếu bạn là nhà phát triển của ứng dụng này, hãy đăng ký URI chuyển hướng trong Google Cloud Console.

 

[luongkiengxuong]

Xin chào mọi người.
Sau một thời gian nhận thấy có nhiều người dùng đặt vấn đề về khả năng macro VBA trong Excel tương tác với Google Drive nói chung và các dịch vụ của Google nói riêng và sau khi nghiên cứu cách viết macro VBA cũng như ngâm cứu tài liệu của Google.
Mình xin chia sẻ một ý tưởng thông qua một tệp ví dụ đính kèm theo bài viết này với những chức năng như sau:

• Quản lý tập tin, thư mục trong một thư mục nhất định trên Google Drive với những chức năng cơ bản như tải lên, tải xuống tập tin, đổi tên, xóa vĩnh viễn tập tin/thư mục.
• Tạo email, tải tệp đính kèm lên thư mục trong Google Drive và lấy shareable link đưa vào email mới tạo.
• Tiến trình tải lên/tải xuống tập tin được ghi nhận ở thanh Status Bar của Excel (góc trái dưới cùng)

Macro VBA trong tệp Excel trên có sử dụng:

1. Thư viện VBA-JSON của tác giả timhall: VBA-JSON
2. Hai hàm URLEncode và URLDecode (ExcelVBA.ru): URL Encode and Decode VBA functions for Excel on Mac or Windows including UTF-8 support. Source: ExcelVBA.ru

Mời mọi người xem tệp bên dưới.

Chào bạn, cho mình hỏi tí sao phần tải file lên nó không hoạt động mình chọn file rồi nhưng vào drive kiểm tra ko thấy tải lên

 

[phuongnam366377]

Xin chào mọi người.
Sau một thời gian nhận thấy có nhiều người dùng đặt vấn đề về khả năng macro VBA trong Excel tương tác với Google Drive nói chung và các dịch vụ của Google nói riêng và sau khi nghiên cứu cách viết macro VBA cũng như ngâm cứu tài liệu của Google.
Mình xin chia sẻ một ý tưởng thông qua một tệp ví dụ đính kèm theo bài viết này với những chức năng như sau:

• Quản lý tập tin, thư mục trong một thư mục nhất định trên Google Drive với những chức năng cơ bản như tải lên, tải xuống tập tin, đổi tên, xóa vĩnh viễn tập tin/thư mục.
• Tạo email, tải tệp đính kèm lên thư mục trong Google Drive và lấy shareable link đưa vào email mới tạo.
• Tiến trình tải lên/tải xuống tập tin được ghi nhận ở thanh Status Bar của Excel (góc trái dưới cùng)

Macro VBA trong tệp Excel trên có sử dụng:

1. Thư viện VBA-JSON của tác giả timhall: VBA-JSON
2. Hai hàm URLEncode và URLDecode (ExcelVBA.ru): URL Encode and Decode VBA functions for Excel on Mac or Windows including UTF-8 support. Source: ExcelVBA.ru

Mời mọi người xem tệp bên dưới.

Nói thật lòng ... rất ngưỡng mộ bài này .. lúc trước ngó qua không xem kỹ nay làm ggogle Sheets mới xem lại

Bạn đã đi đầu GPE này viết thuần VBA đọc và ghi dữ liệu lên Google Sheets =====> Tuyệt vời

có sẳn hết rồi chỉ việc tải về tái chế chút là ra cái của riêng Mình thôi

Tôi nghĩ với Tôi có lẻ không cần Module ( trên 1000 dòng code ) cho đọc ghi và xử lý các dữ liệu liên quan Tới Google Sheets ...

Tôi có viết chỉ trên 100 dòng code thuần VBA có điều trả úp làm gì ... Viết bài này gợi ý cho thiên hạ dò code đẳng cấp của bạn thôi

quá hay cho bài này mà ai đó vô tình lướt qua hay khả năng chưa nhận ra thôi


Lấy AccessToken có lẻ do bạn viết lúc đó Google chưa chặn nhúng trình duyệt nên sử dụng tốt .. còn bây giờ chặn rồi viết lại sử dụng

authCode = InputBox("Nhap mã xác thuc ban dã sao chép:") vậy là xong

vậy thôi để đó cho họ dò là ra Viết mã VBA thuần chủng đọc và ghi dữ liệu lên Google Sheets

Thoát thôi ...

 

[nguyendang95]

Nói thật lòng ... rất ngưỡng mộ bài này .. lúc trước ngó qua không xem kỹ nay làm ggogle Sheets mới xem lại

Bạn đã đi đầu GPE này viết thuần VBA đọc và ghi dữ liệu lên Google Sheets =====> Tuyệt vời

có sẳn hết rồi chỉ việc tải về tái chế chút là ra cái của riêng Mình thôi

Tôi nghĩ với Tôi có lẻ không cần Module ( trên 1000 dòng code ) cho đọc ghi và xử lý các dữ liệu liên quan Tới Google Sheets ...

Tôi có viết chỉ trên 100 dòng code thuần VBA có điều trả úp làm gì ... Viết bài này gợi ý cho thiên hạ dò code đẳng cấp của bạn thôi

quá hay cho bài này mà ai đó vô tình lướt qua hay khả năng chưa nhận ra thôi


Lấy AccessToken có lẻ do bạn viết lúc đó Google chưa chặn nhúng trình duyệt nên sử dụng tốt .. còn bây giờ chặn rồi viết lại sử dụng

authCode = InputBox("Nhap mã xác thuc ban dã sao chép:") vậy là xong

vậy thôi để đó cho họ dò là ra Viết mã VBA thuần chủng đọc và ghi dữ liệu lên Google Sheets

Thoát thôi ...

Code này bây giờ đã lỗi thời rồi bạn, viết sơ sài, thậm chí còn không tạo mã code verifier, state code, để như vầy bọn hacker làm giả yêu cầu ủy quyền thì toi. Chủ yếu là tôi muốn chia sẻ ý tưởng với mọi người thôi.
authCode = InputBox("Nhap mã xác thuc ban dã sao chép:")
Vấn đề này ngày xưa tôi đã từng làm nhưng đã bỏ từ lâu, căn bản thao tác này không an toàn, với lại nhiều người dùng chẳng thích nhiêu khê như vậy. Google gọi thao tác này là OOB (Out of Band).

 

[phuongnam366377]

Code này bây giờ đã lỗi thời rồi bạn, viết sơ sài, thậm chí còn không tạo mã code verifier, state code, để như vầy bọn hacker làm giả yêu cầu ủy quyền thì toi. Chủ yếu là tôi muốn chia sẻ ý tưởng với mọi người thôi.
authCode = InputBox("Nhap mã xác thuc ban dã sao chép:")
Vấn đề này ngày xưa tôi đã từng làm nhưng đã bỏ từ lâu, căn bản thao tác này không an toàn, với lại nhiều người dùng chẳng thích nhiêu khê như vậy. Google gọi thao tác này là OOB (Out of Band).

chính xác là vậy nhưng viết thuần VBA thế thôi cho các bạn mới tập tành code két học với

còn trên Delphi Tôi loại bỏ nó rồi mà giả lập máy chủ xong nhấn nút nó tự động lấy ... xem hình



chủ đề thớt này quá tuyệt vời cho các bạn tập viết đọc và ghi dữ liệu lên Google Sheets Thuần VBA ...

Có điều code thuộc hàng cao cấp rồi nên nhiều bạn đọc khó hiểu ... có gì cứ mạnh dạn hỏi chủ thớt sẻ

hổ trợ cho ... nếu chủ thớt kẹt , bận chưa trả lời hay từ chối ....

Tôi xem xét nếu khả năng có thể Tôi sẻ trợ giúp