Trong hướng dẫn tạo api ghi dữ liệu lên google sheet mà Công ty dịch vụ SEO – Thiết kế Website Limodeo đưa ra dưới đây sẽ giúp bạn sẽ tìm hiểu cách kết nối với API bằng Google Apps Script để truy xuất và hiển thị dữ liệu của bên thứ ba trong Google Sheets của mình.
MỤC LỤC
1. API là gì?
Bạn có thể đã nghe thuật ngữ API trước đây. Bạn có thể đã nghe cách các công ty công nghệ sử dụng chúng để truyền dữ liệu giữa các ứng dụng. Các công ty cũng thường xây dựng các hệ thống phức tạp dựa trên các vi dịch vụ nhỏ hơn nhiều được kết nối thông qua API thay vì một chương trình độc lập.
API là viết tắt của “giao diện chương trình ứng dụng – – application program interface” và thuật ngữ này thường đề cập đến các URL web có thể được sử dụng để truy cập dữ liệu thô. Về cơ bản, API là giao diện người dùng cung cấp dữ liệu thô mà bất kỳ ai cũng có thể truy cập (mặc dù nhiều ứng dụng cũng yêu cầu một số hình thức xác thực).
Bạn có thể kết nối với API Google Sheets và nhập dữ liệu từ API (chẳng hạn như iTunes) trở lại Google Sheets bằng Google Apps Script.
2. AppsScript là gì?
Trong Hướng dẫn tạo api ghi dữ liệu lên google sheet, chúng ta sẽ sử dụng Google Apps Script để kết nối với các API bên ngoài. Google Apps Script là ngôn ngữ kịch bản dựa trên JavaScript được duy trì và thực thi trên các máy chủ của Google giúp mở rộng chức năng của Google Apps.
2.1 Ví dụ 1: Kết nối Google Sheets với API Numbers
Trong hướng dẫn tạo API google sheet này, chúng ta sẽ bắt đầu rất đơn giản để bạn có thể tập trung vào thông tin và không bị lạc trong những dòng mã dài vô tận. Chúng tôi đang viết một chương trình ngắn tên là Numbers API và chương trình này yêu cầu các phép tính cơ bản
- Bước 1: Mở trang mới
Mở một sheets Google trống mới và đổi tên nó Ví dụ API Numbers
- Bước 2: Chuyển đến trình chỉnh sửa apps script
Chuyển đến tools> script editor……
- Bước 3: Đặt tên cho dự án của bạn
Một tab mới sẽ mở ra và chúng tôi viết mã của mình tại đây. Đặt tên cho phiên bản API Numbers mới cho dự án của bạn
- Bước 4: Thêm mã API code
Xóa mã code trong tệp Code.gs và thay thế bằng mã bên dưới:
function callNumbers() {
// Call the Numbers API for random math fact
var response = UrlFetchApp.fetch(“http://numbersapi.com/random/math”);
Logger.log(response.getContentText());
}
Chúng tôi dùng lớp UrlFetchApp để giao tiếp với các ứng dụng Internet khác nhằm truy cập tài nguyên và truy xuất URL.
- Bước 5: Chạy hành động
Nhấp vào nút phát trên thanh công cụ để chạy hành động:
- Bước 6: Xác thực tập lệnh
Nó sẽ yêu cầu bạn cấp quyền cho tập lệnh của mình để kết nối với dịch vụ từ xa. Nhấp vào “review permissions” rồi nhấp vào “allow” để tiếp tục.
- Bước 7: Xem nhật ký
Xin chúc mừng, chương trình của bạn hiện đang hoạt động. Nó đã gửi yêu cầu cho bên thứ ba về một số dữ liệu (trong trường hợp này là dữ liệu toán học ngẫu nhiên) và dịch vụ đã phản hồi với dữ liệu đó. Nhưng chờ đã, đó là đâu? Làm thế nào để chúng ta nhìn thấy thông tin này? Bạn có thể thấy mã ở dòng 5 phía trên Logger.log(…), có nghĩa là chúng tôi đã ghi lại văn bản phản hồi logs của mình.
Vì vậy, hãy kiểm tra nó bằng cáh vào View > Logs. Bạn có thể tìm thấy câu trả lời ở đây
Hãy thử chạy nó một vài lần, kiểm tra lại nhật ký và bạn sẽ thấy thông tin khác. Sau đó, hãy thử thay đổi URL bằng các ví dụ sau để xem thông tin khác trong phản hồi:
Nếu chúng ta muốn in kết quả trong bảng tính của mình thì sao? Hãy chuyển sang bước 8.
- Bước 8: Thêm thông tin vào Sheets
Thêm một số dòng mã Code sau bên dưới Code hiện tại của bạn (dòng 7, 8 và 9):
function callNumbers() {
// Call the Numbers API for random math fact
var response = UrlFetchApp.fetch(“http://numbersapi.com/random/math”);
Logger.log(response.getContentText());
var fact = response.getContentText();
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1,1).setValue([fact]);
}
Dòng 7 chỉ cần gán văn bản phản hồi (dữ liệu của chúng tôi) cho một biến có tên là Fact,để chúng tôi có thể gọi nó bằng tên đó.
Dòng 8 lấy Sheets đang hoạt động hiện tại của chúng tôi (lấy mẫu API Numbers Sheet1) và gán biến này cho bảng đã đặt tên để chúng tôi có thể truy cập bảng với tên đó.
Cuối cùng, ở dòng 9, chúng ta lấy ô A1 (phạm vi 1,1) và đặt giá trị của ô đó bằng với biến Fact chứa văn bản trả lời.
- Bước 9: Chạy và ủy quyền lại
Khởi động lại chương trình. Bạn sẽ được hỏi có muốn cho phép chương trình xem và quản lý bảng tính của mình trong Google Drive hay không, vì vậy hãy nhấp vào allow.
- Bước 10: Xem lại dữ liệu bên ngoài các Sheets của bạn
Giờ đây, bạn có thể xem dữ liệu ngẫu nhiên trong Google Sheets của mình:
- Bước 11: Sao chép dữ liệu sang ô mới
Sau khi được viết, tập lệnh sẽ luôn thay thế ô A1 bằng dữ liệu mới của bạn mỗi khi bạn chạy chương trình. Để tạo một danh sách và thêm các sự kiện mới bên dưới các sự kiện hiện có, hãy thực hiện thay đổi nhỏ này đối với dòng 9 của mã của bạn (hiển thị bên dưới) để viết câu trả lời vào dòng trống đầu tiên:
function callNumbers() {
// Call the Numbers API for random math fact
var response = UrlFetchApp.fetch(“http://numbersapi.com/random/math”);
Logger.log(response.getContentText());
var fact = response.getContentText();
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(sheet.getLastRow() + 1,1).setValue([fact]);
}
Đầu ra của bạn bây giờ sẽ trông như thế này:
Điều cuối cùng chúng tôi muốn làm với chương trình này là thêm một menu vào Google Sheets để chúng tôi có thể chạy tập lệnh từ đó thay vì cửa sổ trình chỉnh sửa tập lệnh.
- Bước 12: Tạo menu tùy chỉnh bằng cách thêm mã Code
Thêm mã Code sau vào trình chỉnh sửa tập lệnh
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Numbers API Menu’)
.addItem(‘Display random number fact’,’callNumbers’)
.addToUi();
- Bước 13: Thêm menu tùy chỉnh
Chạy hàm onOpen để thêm menu vào trang tính. Chúng ta chỉ cần thực hiện bước này một lần.
- Bước 14: Chạy tập lệnh từ menu tùy chỉnh
Sử dụng trình đơn mới để chạy Google Sheets và hiển thị các sự kiện ngẫu nhiên trong Google Sheets.Đơn giản phải không? Hãy tiếp tục với một ví dụ phức tạp hơn một chút. Tạo ứng dụng tìm kiếm nhạc trong Google Sheets.
2.2 Ví dụ 2: Khám phá âm nhạc bằng API iTunes
Chương trình này lấy tên nghệ sĩ từ bảng tính Google, yêu cầu API iTunes cung cấp thông tin về nghệ sĩ và trả về. Sau đó, nó hiển thị các album, tên bài hát, tác phẩm nghệ thuật và thậm chí bao gồm một liên kết đến các mẫu bài hát: Nghe có vẻ không quá phức tạp phải không? Tôi hy vọng các bước này đơn giản.
- Bắt đầu với iTunes API Explorer
Bắt đầu với một Google Sheet trống, đặt tên là “iTunes API Explorer” và mở Google Apps Script Editor. Bắt đầu bằng cách và dán mã Google Apps Script hiện có:
- Xóa mã google apps script hiện có và dán mã code này vào để bắt đầu:
function calliTunes() {
// Call the iTunes API
var response = UrlFetchApp.fetch(“https://itunes.apple.com/search?term=coldplay”);
Logger.log(response.getContentText());
}
Thực hiện chương trình và chấp nhận các quyền cần thiết.
- Phân tích dữ liệu iTunes
Cập nhật mã code của bạn để phân tích cú pháp và truy xuất dữ liệu.
function calliTunes() {
// Call the iTunes API
var response = UrlFetchApp.fetch(“https://itunes.apple.com/search?term=coldplay”);
// Parse the JSON reply
var json = response.getContentText();
var data = JSON.parse(json);
Logger.Log(data);
Logger.Log(data[“results”]);
Logger.Log(data[“results”][0]);
Logger.Log(data[“results”][0][“artistName”]);
Logger.Log(data[“results”][0][“collectionName”]);
Logger.Log(data[“results”][0][“artworkUrl60”]);
Logger.Log(data[“results”][0][“previewUrl”]);
}
Dòng 4: Chúng tôi gửi yêu cầu tới iTunes API để truy xuất dữ liệu từ Coldplay. API phản hồi với thông tin này và chúng tôi gán nó cho một biến có tên là Response để chúng tôi có thể sử dụng tên đó khi đề cập đến nó.
Dòng 7 và 8: Chúng tôi trích xuất văn bản ngữ cảnh từ dữ liệu phản hồi, sau đó phân tích cú pháp chuỗi JSON phản hồi để lấy đối tượng ban đầu. Điều này cho phép chúng ta trích xuất các bit khác nhau của dữ liệu.
Vì vậy, trước tiên hãy nhìn vào dữ liệu ( dòng 10 ): Bạn có thể thấy nó là một đối tượng có dấu ngoặc nhọn ở đầu {
Cấu trúc như thế này:
{
resultCount = 50,
results = [ ….the data we`re after… ]
}
Dòng 11: chúng tôi trích xuất “Results”, là phần chứa thông tin về bài hát và nghệ sĩ, sử dụng:
data[“Results”]
Dòng 12: nhiều Album được gửilại cho nghệ sĩ này, vì vậy chúng tôi lấy Album đầu tiên đẻ tham chiếu [0] vì chỉ mục bắt đầu từ 0:
data[“Results”][0]
Điều này cho thấy tất cả thông tin có sẵn từ API iTunes cho album và nghệ sĩ cụ thể này:
Dòng 13 – 16 : ở phần dữ liệu này, chúng tôi có thể trích xuất các chi tiết cụ thể bằng cách tham khảo tên của chúng:
data[“Results”][0][“CollectionName”]
để đưa ra đáp án sau
dùng chú thích (“//” ở đầu dòng) để ngăn logger chép lại các đối tượng dữ liệu đầy đủ, tức là thay đổi các dòng 10, 11 và 12 thành:
// Logger.Log (data);
// Logger.Log (data [“results”]);
// Logger.Log (data [“results”] [0]);
Sẽ giúp bạn dễ dàng xem các chi tiết mà bạn đang trích xuất.
- Đưa toàn bộ các bước trên vào một công cụ
Nếu chúng tôi muốn xây dựng ứng dụng hiển thị trong gif ở đầu bài đăng này, thì chúng tôi cần thực hiện một số bước:
- Thiết lập Google Sheets
- Lấy tên nghệ sĩ từ Google Sheets bằng google apps script
- Yêu cầu dữ liệu từ iTunes cho nghệ sĩ này với google apps script
- Phân tích câu pháp phản hồi để trích xuất đối tượng dữ liệu có liên quan bằng google apps script
- Trích xuất các chi tiết cụ thể mà chúng chúng tôi muốn (tên Album, ảnh minh họa Album,tên bài hát, URL xem trước)
- Xóa hết đáp án trước đó trong Google Sheets trước khi đưa ra kết quả mới
- đưa ra các kết quả mới trong Google sheets của chúng ta
- Thêm menu tùy chỉnh để chạy chương trình từ Google Sheets, không phải trình chỉnh sửa tập lệnh
Bạn nên đưa ra một kế hoạch như thế này trước khi bạn viết bất kỳ một dòng code nào. Bằng cách này, bạn có thể suy nghĩ về toàn bộ công dụng và những gì nó sẽ làm, điều này cho phép bạn đưa ra các lựa chọn hiệu quả với cách bạn thiết lập mã code của mình.
Vì vậy, điều cần làm là thiết lập Google Sheets. Điều quan trọng cần chú ý là vị trí của ô mà người dùng nhập tên nghệ sĩ (hàng thứ 11, cột thứ 2). iTunes API explorer code
// ————————————————————————————————– //
// iTunes Music Discovery Application in Google Sheets
//
// ————————————————————————————————– // custom menu
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom iTunes Menu’)
.addItem(‘Get Artist Data’,’displayArtistData’)
.addToUi();
} // function to call iTunes API
function calliTunesAPI(artist) {
// Call the iTunes API
var response = UrlFetchApp.fetch(“https://itunes.apple.com/search?term=” + artist + “&limit=200”);
// Parse the JSON reply
var json = response.getContentText();
return JSON.parse(json);
} function displayArtistData() {
// pick up the search term from the Google Sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var artist = sheet.getRange(11,2).getValue();
var tracks = calliTunesAPI(artist);
var results = tracks[“results”];
var output = []
results.forEach(function(elem,i) {
var image = ‘=image(“‘ + elem[“artworkUrl60″] + ‘”,4,60,60)’;
var hyperlink = ‘=hyperlink(“‘ + elem[“previewUrl”] + ‘”,”Listen to preview”)’;
output.push([elem[“artistName”],elem[“collectionName”],elem[“trackName”],image,hyperlink]);
sheet.setRowHeight(i+15,65);
});
// sort by album
var sortedOutput = output.sort( function(a,b) {
var albumA = (a[1]) ? a[1] : ‘Not known’; // in case album name undefined
var albumB = (b[1]) ? b[1] : ‘Not known’; // in case album name undefined
if (albumA < albumB) { return -1; } else if (albumA > albumB) {
return 1;
}
// names are equal
return 0;
});
// adds an index number to the array
sortedOutput.forEach(function(elem,i) {
elem.unshift(i + 1);
});
var len = sortedOutput.length;
// clear any previous content
sheet.getRange(15,1,500,6).clearContent();
// paste in the values
sheet.getRange(15,1,len,6).setValues(sortedOutput);
// formatting
sheet.getRange(15,1,500,6).setVerticalAlignment(“middle”);
sheet.getRange(15,5,500,1).setHorizontalAlignment(“center”);
sheet.getRange(15,2,len,3).setWrap(true);
}
- Đoạn mã code này hoạt động như nào?
Hãy nói về những dòng mã quan trọng trong chương trình này: Các dòng 16-25 mô tả một hàm lấy tên nghệ sĩ, gọi API bằng tên nghệ sĩ đó, sau đó trả về kết quả tìm kiếm từ API. Chúng tôi đóng gói nó thành một chức năng riêng biệt để có thể sử dụng lại nó ở nơi khác trong chương trình của mình.
Chương trình chính bắt đầu trên dòng 28: Trong dòng 34, chúng tôi lấy tên của nghệ sĩ đã nhập trong Google Sheets và gọi hàm API của chúng tôi với tên đó trong dòng 36.
Trong các dòng 42-47, lấy kết quả do API trả về, lặp qua chúng và chỉ trích xuất thông tin chúng tôi muốn (tên nghệ sĩ, tên album, tên bản nhạc, ảnh bìa album và bản nhạc mẫu). Hãy đẩy tất cả vào một bảng mới gọi là đầu ra.
Sắp xếp và thêm một chỉ mục vào bảng, mặc dù không có bước nào trong số này là cần thiết. Trên dòng 68, hãy xóa tất cả nội dung trước đó khỏi trang của chúng tôi. Vì vậy, trong dòng 71, dữ liệu mới được nhập bắt đầu từ dòng 15.
Cuối cùng, dòng 74-76 là định dạng dữ liệu mới được dán, hình ảnh có không gian hiển thị chính xác. Chạy hàm onOpen() từ chương trình chỉnh sửa tập lệnh một lần để thêm menu tùy chỉnh vào Google Sheets của bạn. Sau đó, bạn sẽ có thể thực hiện mã iTunes của mình từ Google Sheets
2.3 Ví dụ 3: Tạo Star Wars Data Explorer với Star Wars API
Ví dụ này rất thú vị, đặc biệt nếu bạn là một người hâm mộ cuồng nhiệt của loạt phim Chiến tranh giữa các vì sao. API star wars là cơ sở dữ liệu về tất cả các tập phim, con người, hành tinh, phi thuyền, loài và phương tiện trong star wars. Các truy vấn rất dễ thực hiện và dữ liệu trả về rất thân thiện với người dùng.
Ví dụ này đơn giản hơn một chút so với iTunes API vì dữ liệu được trả về nhỏ hơn và dễ quản lý hơn, vì vậy sẽ dễ dàng phân tích cú pháp hơn khi bạn hiểu rõ về nó.
- Bắt đầu sử dụng API star wars
Như với cả hai API ở trên, bắt đầu với một lệnh gọi đơn giản để xem API trả về kết quả là như thế nào nhé!
/* * Step 1:
* Most basic call to the API
*/
function swapi() {
// Call the Star Wars API
var response = UrlFetchApp.fetch(“http://swapi.dev/api/planets/1/”);
Logger.log(response.getContentText());
Vì vậy, loại mã này làm cho việc lấy thông tin bạn muốn tương đối dễ dàng
/* * Step 2:
* Same basic call to the API
* Parse the JSON reply
*/
function swapi() {
// Call the Star Wars API
var response = UrlFetchApp.fetch(“http://swapi.dev/api/planets/1/”);
// Parse the JSON reply
var json = response.getContentText();
var data = JSON.parse(json);
Logger.log(data);
Logger.log(data.name);
Logger.log(data.population);
Logger.log(data.terrain);
}
- Thử một thủ thuật khác
Ngoài các menu để chạy tập lệnh Google Trang tính tùy chỉnh, bạn có thể thêm các nút vào Google Trang tính của mình và liên kết chúng với một tập lệnh để chạy tập lệnh khi nhấp chuột. Đây là những gì chúng tôi làm trong ví dụ này.
Từ menu, insert > drawing…
Tạo một nút bằng công cụ hình chữ nhật: Cuối cùng, nhấp chuột phải vào hình ảnh khi nó xuất hiện trên trang của bạn, chọn assign script và nhập tên của hành động sẽ được thực hiện.
Sử dụng công thức này để gắn dấu sao Google Trang tính:
= Char(9734)
Bạn có thể sử dụng phông chữ “orbitron” trên toàn Sheets và mặc dù đó không phải là phông chữ star wars nhưng nó vẫn rất phù hợp. Logo star wars được tạo bằng cách hợp nhất một loạt các ô và sử dụng hàm IMAGE() trên một hình ảnh phù hợp tìm thấy trên internet.
Hi vọng với hướng dẫn tạo API ghi dữ liệu lên google sheet trên của Công ty dịch vụ SEO – Thiết kế Website Limoseo đã giúp bạn hiểu được khái niệm cơ bản và nâng cao và ứng dụng để làm việc với Google Sheets một cách hiệu quả hơn!
