Cách kết nối Google Sheets với Mysql Data bằng Apps Script

Kết nối Google Sheets với Mysql Data là một cách rất hay để backup và quản lý dữ liệu database một cách dễ dàng. Trong bài viết này, tôi chia sẻ đến bạn một số cách để lấy dữ liệu từ cơ sở dữ liệu SQL của bạn vào Google Sheets. Bài viết này sẽ hướng dẫn chi tiết cho bạn bởi nó là từ kinh nghiệm thực tế của tôi.

Kết nối cơ sở dữ liệu Google Trang tính với Apps Script

Google Sheets đi kèm với một nền tảng phát triển ứng dụng tích hợp có tên là “Apps Script”.

Dựa trên JavaScript, nó bao gồm rất nhiều tác vụ bạn sẽ sử dụng VBA trong Excel.

App Script đi kèm với JDBC Service cho phép bạn kết nối với cơ sở dữ liệu MySQL, Microsoft SQL Server và Oracl.

Các bước kết nối Google Sheets với Mysql Data

1. Tạo một tập lệnh trống.
Để tạo tập lệnh cho Google Trang tính của bạn, hãy nhấp vào Công cụ > Trình chỉnh sửa tập lệnh từ menu Google Trang tính:

2. Khai báo các biến thông tin kết nối Data Mysql
Thay đổi các dấu đỏ bên dưới bằng thông tin kết nối database của bạn.

//replace the variables in this block with real values as needed
var address = 'yourhostnameORip:port';
var user = 'YOUR_USER';
var userPwd = 'YOUR_PASS';
var db = 'YOUR_DATABASE';

var dbUrl = 'jdbc:mysql://' + address + '/' + db + '?useSSL=false';

3. Thêm tùy chọn menu
Chức năng này thêm một tùy chọn menu vào Google Trang tính của bạn, cho phép bạn sử dụng giao diện người dùng để gọi hàm của mình.

//Add shortcut Write data
function onOpen() {
	var spreadsheet = SpreadsheetApp.getActive();
	var menuItems = [{
		name: 'Write table data to a sheet',
		functionName: 'connectToMySQLData'
	}];
	spreadsheet.addMenu('MySQL Data', menuItems);
}

4. Viết một hàm để ghi dữ liệu MySQL vào một bảng tính
Hàm bên dưới đọc dữ liệu MySQL, sử dụng chức năng JDBC của Google Apps Script để kết nối với cơ sở dữ liệu MySQL, Microsoft SQL Server và Oracl.

Lưu ý, bạn có thể sửa code và tối ưu theo mục đích của mình.

/*
 * Reads data from a specified 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToMySQLData() {
	var thisWorkbook = SpreadsheetApp.getActive();
	var resultSheet = thisWorkbook.getActiveSheet();
	var rowNum = 3;
	var conn = Jdbc.getConnection(dbUrl, {
		user: user,
		password: userPwd
	});
    
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = ss.getSheets()[0];
	var lastRow = sheet.getLastRow();
	var stmt = conn.createStatement();
	var results = stmt.executeQuery('Select * from table_test where id<1000');
	stmt.setQueryTimeout(30);
	var rsmd = results.getMetaData();
	var numCols = rsmd.getColumnCount();

	// clear old data
	resultSheet.getRange(rowNum, 1, lastRow, numCols).clearContent();

	//write rows of MySQL data to the sheet
	var values = new Array(new Array(numCols));
	while (results.next()) {
		for (var col = 0; col < numCols; col++) {
			values[0][col] = results.getString(col + 1);
		}
		resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
		rowNum++;
	}

	results.close();
	stmt.close();
}

Khi hoàn thành chức năng, bạn sẽ có một bảng tính chứa dữ liệu MySQL của mình và bây giờ bạn có thể tận dụng tất cả chức năng tính toán, vẽ đồ thị và biểu đồ của Google Trang tính ở bất kỳ đâu có Internet.

Code hoàn chỉnh sẽ như thế này:

//replace the variables in this block with real values as needed
var address = 'yourhostnameORip:port';
var user = 'YOUR_USER';
var userPwd = 'YOUR_PASS';
var db = 'YOUR_DATABASE';

var dbUrl = 'jdbc:mysql://' + address + '/' + db + '?useSSL=false';

//Add shortcut Write data
function onOpen() {
	var spreadsheet = SpreadsheetApp.getActive();
	var menuItems = [{
		name: 'Write table data to a sheet',
		functionName: 'connectToMySQLData'
	}];
	spreadsheet.addMenu('MySQL Data', menuItems);
}

/*
 * Reads data from a specified 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToMySQLData() {
	var thisWorkbook = SpreadsheetApp.getActive();
	var resultSheet = thisWorkbook.getActiveSheet();
	var rowNum = 3;
	var conn = Jdbc.getConnection(dbUrl, {
		user: user,
		password: userPwd
	});
    
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = ss.getSheets()[0];
	var lastRow = sheet.getLastRow();
	var stmt = conn.createStatement();
	var results = stmt.executeQuery('Select * from table_test where id<1000');
	stmt.setQueryTimeout(30);
	var rsmd = results.getMetaData();
	var numCols = rsmd.getColumnCount();

	// clear old data
	resultSheet.getRange(rowNum, 1, lastRow, numCols).clearContent();

	//write rows of MySQL data to the sheet
	var values = new Array(new Array(numCols));
	while (results.next()) {
		for (var col = 0; col < numCols; col++) {
			values[0][col] = results.getString(col + 1);
		}
		resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
		rowNum++;
	}

	results.close();
	stmt.close();
}

5. Chạy Code.gs và cấp quyền truy cập
Sau khi hoàn thành Code.gs bạn cần chạy code để kiểm tra kết nối và xem kết quả.

Một số lỗi thường gặp

Nếu bạn gặp lỗi như bên dưới thì hãy kiểm tra lại thông tin kết nối. Nếu bạn đã điền đúng thì hãy xem tiếp bên dưới.

Nhiều khi vấn đề kết nối là do tường lửa cơ sở dữ liệu của bạn. Nếu bạn gặp sự cố khi kết nối, bạn cần đưa bất kỳ địa chỉ IP nào vào danh sách trắng để có thể chạy được code.

Bạn sẽ cần cung cấp cơ sở dữ liệu của mình cho các IP của Google, đây là các phạm vi (nguồn). Dưới đây là một ví dụ về cách một nhóm bảo mật AWS sẽ tìm kiếm SQL Server:

– Bạn có thể dán chuỗi bên dưới vào một hộp trên AWS và nó sẽ tự động tạo các phạm vi bạn cần:

64.18.0.0/255, 64.233.160.0/255, 66.102.0.0/255, 66.249.80.0/255, 72.14.192.0/255, 74.125.0.0/16,173.194.0.0/255, 207.126.144.0/255, 209.85.128.0/255, 216.239.32.0/255, 35.193.207.79/32, 35.239.74.213/32

– Nếu bạn không sử dụng Google Cloud SQL, bạn cũng cần thêm tất cả các IP từ 0 đến 255. Đọc thêm bài viết Remote MySQL® – Kết nối database mysql từ xa để biết cách add while list.

64.18.0.0 - 64.18.15.255
64.233.160.0 - 64.233.191.255
66.102.0.0 - 66.102.15.255
66.249.80.0 - 66.249.95.255
72.14.192.0 - 72.14.255.255
74.125.0.0 - 74.125.255.255
173.194.0.0 - 173.194.255.255
207.126.144.0 - 207.126.159.255
209.85.128.0 - 209.85.255.255
216.239.32.0 - 216.239.63.255

Đây là một ví dụ với MySQL trên Cpanel:

Kết luận

Trên đây là hướng dẫn kết nối rất chi tiết về Cách kết nối Google Sheets với Mysql Data bằng Apps Script. Nếu bạn thấy bài viết này hay thì hãy chia sẻ và để lại bình luận bên dưới nhé. Chúc bạn thành công!