Chức năng đám mây giúp tự động nhập dữ liệu CSV vào Google Trang tính

1. Giới thiệu

Mục tiêu của lớp học lập trình này là giúp bạn hiểu cách viết một Cloud Function để phản ứng với một tệp CSV được tải lên Cloud Storage, đọc nội dung của tệp đó và dùng nội dung đó để cập nhật một Google Trang tính bằng API Trang tính.

Bạn có thể xem đây là bước tự động hoá của bước "nhập dưới dạng CSV" theo cách thủ công. Điều này sẽ đảm bảo rằng bạn có thể phân tích dữ liệu (có thể do một nhóm khác tạo ra) trong bảng tính ngay khi dữ liệu đó có sẵn.

Phương thức triển khai có dạng như sau :

2. Thiết lập và yêu cầu

Thiết lập môi trường theo tốc độ của riêng bạn

Đăng nhập vào Cloud Console rồi tạo một dự án mới hoặc sử dụng lại một dự án hiện có. (Nếu chưa có tài khoản Gmail hoặc G Suite, bạn phải tạo một tài khoản.)

dMbN6g9RawQj_VXCSYpdYncY-DbaRzr2GbnwoV7jFf1u3avxJtmGPmKpMYgiaMH-qu80a_NJ9p2IIXFppYk8x3wyymZXavjglNLJJhuXieCem56H30hwXtd8PvXGpXJO9gEUDu3cZw

ci9Oe6PgnbNuSYlMyvbXF1JdQyiHoEgnhl4PlV_MFagm2ppzhueRkqX4eLjJllZco_2zCp0V0bpTupUSKji9KkQyWqj11pqit1K1faS1V6aFxLGQdkuzGp4rsQTan7F01iePL5DtqQ

8-tA_Lheyo8SscAVKrGii2coplQp2_D1Iosb2ViABY0UUO1A8cimXUu6Wf1R9zJIRExL5OB2j946aIiFtyKTzxDcNnuznmR45vZ2HMoK3o67jxuoUJCAnqvEX6NgPGFjCVNgASc-lg

Hãy nhớ mã dự án, một tên duy nhất trên tất cả các dự án trên Google Cloud (tên ở trên đã được sử dụng và sẽ không hoạt động đối với bạn, xin lỗi!). Sau này trong lớp học lập trình này, chúng ta sẽ gọi nó là PROJECT_ID.

Tiếp theo, bạn cần bật tính năng thanh toán trong Cloud Console để sử dụng các tài nguyên của Google Cloud.

Việc thực hiện lớp học lập trình này sẽ không tốn nhiều chi phí, nếu có. Hãy nhớ làm theo mọi hướng dẫn trong phần "Dọn dẹp" để biết cách tắt các tài nguyên nhằm tránh bị tính phí ngoài phạm vi hướng dẫn này. Người dùng mới của Google Cloud đủ điều kiện tham gia chương trình Dùng thử miễn phí trị giá 300 USD.

3. Tạo và định cấu hình một tệp Google Trang tính, đồng thời bật API

Trước tiên, hãy tạo một tài liệu Sheets mới (tài liệu này có thể thuộc về bất kỳ người dùng nào). Sau khi tạo, hãy nhớ mã nhận dạng của nó; mã nhận dạng này sẽ được dùng làm biến môi trường cho hàm mà chúng ta sẽ viết :

Trong bảng điều khiển GCP, hãy bật API Google Trang tính trên dự án bạn vừa tạo bằng cách chuyển đến phần "API và dịch vụ" rồi đến phần "Thư viện API":

Trong phần "IAM và quản trị", hãy chuyển đến "Tài khoản dịch vụ" và ghi lại Email cho tài khoản dịch vụ mặc định của App Engine. Giá trị này phải có dạng your-project-id@appspot.gserviceaccount.com. Tất nhiên, bạn cũng có thể tạo tài khoản dịch vụ riêng dành riêng cho hành động này.

Cuối cùng, chỉ cần cấp cho tài khoản dịch vụ này quyền chỉnh sửa bảng tính bằng nút "Chia sẻ":

Với chế độ thiết lập này, giờ đây, chúng ta có thể viết Cloud Function và định cấu hình để sử dụng tài khoản dịch vụ này. Ứng dụng sẽ có thể ghi vào tài liệu bảng tính mà chúng ta vừa tạo.

4. Tạo bộ chứa lưu trữ

Hãy tạo bộ chứa mà hàm đám mây của chúng ta sẽ giám sát để tìm các tệp CSV mới.

Trong bảng điều khiển, hãy sử dụng trình đơn bên trái để chuyển đến phần "Bộ nhớ"... :

... và tạo một vùng chứa mới có tên là csv2sheet-POSTFIX (thay thế POSTFIX bằng một giá trị riêng biệt) với tất cả các chế độ cài đặt khác được đặt thành giá trị mặc định :

5. Tạo Cloud Functions

Giờ đây, chúng ta có thể tạo một Cloud Function có tên là csv2sheet. Cloud Function này sẽ được kích hoạt khi có tệp tải lên một bộ chứa Cloud Storage cụ thể. Mã sẽ được viết bằng Node.js 8 với các hàm không đồng bộ bằng trình chỉnh sửa nội tuyến ngay trong Cloud Console :

Hãy nhớ đặt Trình kích hoạt thành "Cloud Storage" và điều chỉnh tên vùng chứa thành tên mà bạn đã tạo ở bước trước.

Ngoài ra, hãy cập nhật điểm truy cập cho hàm mà chúng ta sắp viết thành csv2sheet :

Bây giờ, hãy thay đổi phần nội dung hàm thành :

sử dụng API Cloud Storage và Sheets
đánh dấu hàm csv2sheet là async
lấy fileName từ siêu dữ liệu sự kiện Cloud Storage và suy ra tên cho trang tính mới mà chúng ta sẽ tạo :

const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")

exports.csv2sheet = async (data, context) => {
  var fileName = data.name;
  // basic check that this is a *.csv file, etc...
  if (!fileName.endsWith(".csv")) {
    console.log("Not a .csv file, ignoring.");
    return;
  }
  // define name of new sheet  
  const sheetName = fileName.slice(0, -4);

  // TODO!
};

Bạn phải sử dụng async ở đây để dùng await như chúng ta sẽ thấy trong giây lát.

Một số lựa chọn quan trọng trong khi tạo hàm này bao gồm (nhấp vào đường liên kết "Tuỳ chọn khác" ở cuối màn hình) :

Sử dụng trình đơn thả xuống để chọn tài khoản dịch vụ đã thảo luận ở trên
Xác định một biến môi trường có tên là SPREADSHEET_ID. Biến này phải khớp với tài liệu trang tính mà bạn đã tạo trước đó :

Là bước thiết lập cuối cùng, đây là nội dung package.json với Cloud Storage và Google Sheet API là hai phần phụ thuộc mà chúng ta sẽ sử dụng (sử dụng thẻ PACKAGE.JSON của trình chỉnh sửa nội tuyến của bảng điều khiển) :

{
    "name": "csv2sheet",
    "version": "0.0.42",
    "dependencies": {
        "googleapis": "^51.0.0",
        "@google-cloud/storage": "^5.0.1"
    }
}

Sau khi bạn đã định cấu hình mọi thứ như mô tả, hãy nhấp vào "Tạo"! Sau một phút ngắn ngủi, hàm của bạn sẽ được tạo và triển khai.

6. Thiết lập chế độ xác thực và API Trang tính

Trước khi viết thêm mã trong Hàm trên đám mây bằng trình chỉnh sửa nội tuyến, chúng ta cần chặn việc tạo một Google Client API có phạm vi Storage và Sheet phù hợp (hãy nhớ rằng đây là một phần của hàm async).

Trong trình chỉnh sửa hàm của bảng điều khiển, hãy nhấp vào "CHỈNH SỬA" rồi thêm mã sau vào nội dung hàm csv2sheet :

// block on auth + getting the sheets API object
const auth = await google.auth.getClient({
  scopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/devstorage.read_only"
  ]
});

Từ đó, chúng ta có thể tạo một ứng dụng API Trang tính :

const sheetsAPI = google.sheets({version: 'v4', auth});

7. Sử dụng API Trang tính để tạo một trang tính trống

Với một ứng dụng API Trang tính, chúng ta có thể tạo một trang tính mới đơn giản trong tài liệu của mình. Tuy nhiên, trước khi đi xa hơn, bạn cần lưu ý nhanh về từ vựng:

bảng tính là tài liệu thực tế và được tham chiếu theo giá trị nhận dạng của tài liệu (được thảo luận ở trên và xuất hiện trong URL của tài liệu)
trang tính là một trong các thẻ trong tài liệu và có thể được tham chiếu theo tên (tên thẻ) hoặc một giá trị nhận dạng được tạo khi trang tính được tạo

Với thông tin này, sau đây là một hàm sử dụng API Trang tính để tạo một trang tính trống ở vị trí 2 (thường là sau "Trang tính 1" mặc định), có 26 cột, 2.000 hàng, với hàng đầu tiên được cố định (thêm hàng này vào hàm bằng trình chỉnh sửa nội tuyến) :

function addEmptySheet(sheetsAPI, sheetName) {
  return new Promise((resolve, reject) => {
    const emptySheetParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            addSheet: {
              properties: {
                title: sheetName,
                index: 1,
                gridProperties: {
                  rowCount: 2000,
                  columnCount: 26,
                  frozenRowCount: 1
                }
              }
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          const sheetId = response.data.replies[0].addSheet.properties.sheetId;
          console.log("Created empty sheet: " + sheetId);
          resolve(sheetId);
        }
      }
    );
  });
}

Xin lưu ý rằng thay vì mã hoá cứng tham chiếu đến bảng tính, chúng ta dựa vào biến môi trường SPREADSHEET_ID đã tạo trước đó.

Chúng ta cần nhớ sheetId cho các yêu cầu tiếp theo được gửi đến trang tính cụ thể này. Ngoài ra, tên trang tính phải là duy nhất và quá trình tạo sẽ thất bại nếu đã có một trang tính có tên sheetName.

Hàm batchUpdate trong API Trang tính là một cách phổ biến để tương tác với tài liệu và được mô tả tại đây.

8. Đọc dữ liệu từ tệp CSV lưu trữ

Bây giờ, khi đã có nơi để lưu trữ dữ liệu, hãy tiếp tục phát triển hàm đám mây trong trình chỉnh sửa nội tuyến và sử dụng Cloud Storage API để lấy dữ liệu thực từ tệp vừa tải lên và lưu trữ dữ liệu đó trong một chuỗi:

function readCSVContent(sheetsAPI, file, sheetName) {
  return new Promise((resolve, reject) => {
    const storage = new Storage();
    let fileContents = new Buffer('');
    storage.bucket(file.bucket).file(file.name).createReadStream()
    .on('error', function(err) {
      reject('The Storage API returned an error: ' + err);
    })
    .on('data', function(chunk) {
      fileContents = Buffer.concat([fileContents, chunk]);
    })  
    .on('end', function() {
      let content = fileContents.toString('utf8');
      console.log("CSV content read as string : " + content );
      resolve(content);
    });
  });
}

9. Điền dữ liệu vào trang tính mới tạo

Bây giờ, hãy điền dữ liệu vào trang tính mà chúng ta vừa tạo bằng cùng một API ứng dụng Trang tính và dữ liệu mà chúng ta vừa thu thập. Chúng ta cũng sẽ nhân cơ hội này để thêm một số kiểu cho các cột của trang tính (thay đổi cỡ chữ của hàng trên cùng và làm cho hàng đó trở nên đậm) :

function populateAndStyle(sheetsAPI, theData, sheetId) {
  return new Promise((resolve, reject) => {
    // Using 'batchUpdate' allows for multiple 'requests' to be sent in a single batch.
    // Populate the sheet referenced by its ID with the data received (a CSV string)
    // Style: set first row font size to 11 and to Bold. Exercise left for the reader: resize columns
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            pasteData: {
              coordinate: {
                sheetId: sheetId,
                rowIndex: 0,
                columnIndex: 0
              },
              data: theData,
              delimiter: ","
            }
          },
          {
            repeatCell: {
              range: {
                sheetId: sheetId,
                startRowIndex: 0,
                endRowIndex: 1
              },
              cell: {
                userEnteredFormat: {
                  textFormat: {
                    fontSize: 11,
                    bold: true
                  }
                }
              },
              fields: "userEnteredFormat(textFormat)"
            }
          }       
        ]
      }
    };
        
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err, response) {
      if (err) {
        reject("The Sheets API returned an error: " + err);
      } else {
        console.log(sheetId + " sheet populated with " + theData.length + " rows and column style set.");
        resolve();
      }
    });    
  });
}

Bạn nên thêm mã này vào hàm Cloud của chúng ta. Hàm này hiện đã hoàn tất 99%!

Lưu ý cách cả dữ liệu và kiểu dáng được kết hợp thành nhiều requests trong một lệnh gọi batchUpdate duy nhất của API Trang tính. Điều này giúp quá trình cập nhật hiệu quả và có tính nguyên tử hơn.

Cũng xin lưu ý rằng chúng ta xác định một dải ô chỉnh sửa khớp với kích thước của trang tính mà chúng ta đã tạo. Điều này có nghĩa là nội dung vượt quá 26 cột (giá trị columnCount được dùng khi tạo trang tính) sẽ không thành công với mã cụ thể này.

Nếu mọi thứ diễn ra suôn sẻ, tại thời điểm này, bạn có thể:

lưu hàm đã cập nhật
thả tệp CSV vào vùng lưu trữ
xem dữ liệu tương ứng xuất hiện trong bảng tính của bạn!

10. Tổng hợp mọi yếu tố và kiểm thử quy trình

Bạn có thể thực hiện các lệnh gọi đến các hàm mà chúng ta vừa thảo luận dưới dạng các lệnh gọi chặn liên tiếp trong hàm csv2sheet ban đầu:

  const sheetId = await addEmptySheet(sheetsAPI, sheetName);
  const theData = await readCSVContent(sheetsAPI, data, sheetName);
  await populateAndStyle(sheetsAPI, theData, sheetId);

Nếu bạn cần mã nguồn hàm hoàn chỉnh, bạn có thể xem tại đây (có lẽ sẽ dễ dàng hơn nếu bạn nhận được tất cả trong một bộ).

Sau khi mọi thứ đã sẵn sàng, bạn chỉ cần tải một tệp CSV lên đúng nhóm và xem bảng tính của bạn được cập nhật bằng một trang tính mới có nội dung của tệp đó. Đây là tệp CSV mẫu nếu bạn chưa có sẵn tệp này.

Hãy thử tải một số tệp lên vùng lưu trữ để xem điều gì sẽ xảy ra!

11. Vậy là xong! Đã đến lúc tháo dỡ cơ sở hạ tầng

Đùa thôi, không có cơ sở hạ tầng nào cần phải gỡ bỏ, tất cả đều được thực hiện mà không cần máy chủ!

Nếu muốn, bạn có thể xoá hàm trên đám mây và nhóm mà bạn đã tạo, hoặc thậm chí là toàn bộ dự án.

12. Tiếp theo là gì?

Đến đây là kết thúc lớp học lập trình này. Lớp học lập trình này hướng dẫn bạn các bước để theo dõi nội dung tải lên một bộ chứa Cloud Storage trong một Cloud Function nhằm cập nhật một Google Trang tính bằng cách sử dụng API phù hợp.

Sau đây là một số bước tiếp theo :

Xem hướng dẫn cách thực hiện về Cloud Functions (bao gồm một số phương pháp hay nhất)
Xem một trong các hướng dẫn về Cloud Functions
Khám phá thêm về API Google Trang tính

Nếu bạn gặp vấn đề với lớp học lập trình này, vui lòng báo cáo mọi vấn đề bằng cách sử dụng đường liên kết ở góc dưới bên trái.

Cảm ơn bạn đã đóng góp ý kiến phản hồi!