Chia sẻ: Kết hợp tương tác Excel và Google SpreadSheet ngay trong VBA (1 người xem)

[HeSanbi]

GSPREAD.NET một gói thư viện được viết bằng Microsoft Visual C# và Microsoft .Net Framework, nó cung cấp các Hàm API để kết nối tương tác với Google Spreadsheet từ các ngôn ngữ script như VBScript , VBA, ... mà nó hỗ trợ.

Để sử dụng được, các bạn cần tải và cài đặt GSPREAD.NET, sau đó thêm thư viện vào trong ứng dụng Office của bạn bằng cách:
Mở VBA > Tools > References : tìm đến gói chứa tên GSPREAD.NET và thêm.

Sau đó tạo một file Google Spreadsheet vào Trình biên tập tập lệnh tạo một File Script và copy mã script do trang chủ cung cấp vào Script và chấp nhận xác thực từ Google để Script có đủ quyền thao tác lên Google Spreadsheet. Có Script này thì các Hàm API trong thư viện GSPREAD.NET mới hoạt động.


GSPREAD.NET cung cấp cả mã nguồn nên các bạn có thể cải tiến theo cá nhân.

Hướng dẫn từ Trang chủ https://scand.com/products/gspread/: (Link tải ngay trên Website)

Bài viết được dịch bởi Google dịch.

Lấy ClientID và Secret được Google cung cấp thông qua tài khoản Google trong phần Nhà Phát Triển.

1 2 3 4

// Name - User name, any you like // ClientIdAndSecret - `client_id|client_secret` format // ScriptId - Google Apps script ID app.MailLogon(Name, ClientIdAndSecret, ScriptId);

Ví dụ: Tạo đơn giản một bảng:

Set app = CreateObject("GSpreadCOM.Application")
app.Workbooks.Item("Example2").Worksheets.Item("Sheet1").Activate()

Set cells = app.Cells

' --- Data'

cells(2, 2).Cells.Value = "Product"
For i = 1 To 4
cells(2, 2 + i).Cells.Value = "Quarter " & i
Next
cells(2, 7).Cells.Value = "Year"

cells(4, 2).Cells.Value = "Apples"
cells(5, 2).Cells.Value = "Pears"
cells(6, 2).Cells.Value = "Bananas"
cells(7, 2).Cells.Value = "Oranges"
cells(8, 2).Cells.Value = "Totals"

randomize
For row = 4 To 7
For col = 3 To 6
cells(row, col).Cells.Value = (int(rnd * 100) + 1) * 100
Next
Next

cells.Range("C8:G8").FormulaR1C1 = "=SUM(R[-4]C[0]:R[-1]C[0])"
cells.Range("G4:G7").Formula = "=SUM(C4:F4)"

' --- Styling'

Set tableRange = cells.Range("B2:G8")
tableRange.Borders.Color = "white"
tableRange.NumberFormat = "$0,000" ' currency number format'

cells.Range("B2:B3").Merge()
cells.Range("C2:C3").Merge()
cells.Range("D2:D3").Merge()
cells.Range("E2:E3").Merge()
cells.Range("F2:F3").Merge()
cells.Range("G2:G3").Merge()

Set headerRange = cells.Range("B2:G3")
Set footerRange = cells.Range("B8:G8")

' color #0066CC has index 23 in excel color palette'
headerRange.Interior.ColorIndex = 23
footerRange.Interior.Color = "#0066CC"

' white color has index 2 in excel color palette'
headerRange.Font.ColorIndex = 2
footerRange.Font.Color = "white"

headerRange.Font.Bold = True
footerRange.Font.Bold = True

footerRange.RowHeight = 30

oddRowColor = 14994616 ' RGB(184, 204, 228)'
evenRowColor = 15853019 ' RGB(219, 229, 241)'

cells.Range("B4:G4").Interior.Color = oddRowColor
cells.Range("B5:G5").Interior.Color = evenRowColor
cells.Range("B6:G6").Interior.Color = oddRowColor
cells.Range("B7:G7").Interior.Color = evenRowColor

WScript.Echo "Done"

CÁC CHỨC NĂNG VÀ THUỘC TÍNH ĐƯỢC HỖ TRỢ
Thư viện bị giới hạn và hỗ trợ một tập hợp con các hàm Excel nhất định:

Object	Methods	Properties
Excel.Application	MailLogon Quit Save SaveWorkspace	ActiveCell ActiveSheet ActiveWorkbook Cells Columns DecimalSeparator Range Rows Sheets ThousandsSeparator Workbooks Worksheets
Excel.Workbooks	Add – templates will not be supported Open – limited options set will be supported to open only google spreadsheets by name Close	Count Item
Excel.Workbook	Activate Close ExportAsFixedFormat – only PDF format will be supported (may be excluded from 1st version) Save SaveAs – only google spreadsheet format is supported)	ActiveSheet Application FullName Name Sheets Worksheets
Excel.Worksheets	Add – only Worksheet can be added	Count Item
Excel.Worksheet	Activate Delete	Application Cells Columns Name Range Rows
Excel.Range	BorderAround Clear ClearContents ClearFormats Insert Merge UnMerge	Address Borders Cells Column Columns ColumnWidth – implemented in pixels Count EntireColumn EntireRow Font Formula FormulaR1C1 HorizontalAlignment Interior Item NumberFormat Offset Range Row Rows RowHeight Style Text Value Value2 VerticalAlignment Worksheet WrapText
Excel.Style		Borders Font HorizontalAlignment Interior NumberFormat VerticalAlignment WrapText
Excel.Font		Bold Color ColorIndex Italic Size Strikethrough Underline
Excel.Interior		Color ColorIndex
Excel.Borders		Color ColorIndex Count Item LineStyle Weight
Excel.Border		Color ColorIndex LineStyle Weight

CÀI ĐẶT TÀI KHOẢN GOOGLE

Theo mặc định, GSpread.NET sử dụng các định danh mẫu của nó. Các bạn nên thay đổi nó thành của riêng bạn. Để thực hiện việc này, vui lòng thực hiện các bước sau: Truy cập Google Drive của bạn và tạo một tập lệnh mới (Chọn: MỚI > KHÁC > Tệp Tập lệnh):

Nếu không có tùy chọn trình đơn như vậy, bạn có thể chọn ứng dụng Kết nối nhiều ứng dụng khác, hãy tìm kiếm tập lệnh ứng dụng của tập tin, trong hộp thoại đã mở và sau đó kết nối với Tập lệnh Google Apps.

Sao chép mã tập lệnh từ thư mục <Thư mục dự án> / src/GSpreadCOM/Resource/GoogleAppsScript.gs vào Code.gs bạn vừa tạo. Ngoài ra, bạn có thể tìm thấy tập lệnh tại đây: https://scand.com/doad/products/GSpread.NET/GoogleAppsScript.gs

Lưu dự án tập lệnh với tên GSpreadCOM_Script hoặc tên phù hợp.

Hơn, mở menu Xuất bản> Triển khai dưới dạng API thực thi API

Đặt bất kỳ phiên bản nào, ví dụ 1 và nhấn vào Dep Depdio:

Bây giờ chúng ta cần cấu hình dự án GSiverseCOM_Script.
Truy cập Bảng điều khiển API của Google https://console.developers.google.com và chọn một dự án:

Truy cập Tổng quan về> Các API của Google, tìm kiếm và kích hoạt Google Drive API:

và API thực thi tập lệnh Google Apps:

Bây giờ chúng ta cần tạo thông tin đăng nhập:

Chọn ID khách hàng OAuth IDD:

Loại ứng dụng - Giảm giá khác và nhấn vào Tạo Tạo:

Sao chép ID khách hàng của IDD

1 2 3 4 5 6 7

<?xml version="1.0" encoding="utf-8"?> <config> <user name="any random name"/> <client id="52444250292-02d5nuj5osdf268h605djiukijd5i4m5.apps.googleusercontent.com" secret="q2IOcXHThZ3LPc5nfFCcA_r"/> <script id="Mvi58b-XTLQOC7mbLFOvENmbpWLEH-geZ"/> </config>

Bạn có thể sử dụng các giá trị này làm tham số thứ hai của phương thức Application.MailLogon trong định dạng của client client_id | client_secret.

1 2 3

app.MailLogon("any random name", "52444250292- 02d5nuj5osdf268h605djiukijd5i4m5.apps.googleusercontent.com|q2IOcXH- ThZ3LPc5nfFCcA_r", "Mx1qw1DNjz3vrzoYbLfc5H0sBksCqgDyL");