Trang này được dịch bởi Cloud Translation API.

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 nắm được cách viết Hàm đám mây để phản hồi tệp CSV tải lên Cloud Storage, cũng như đọc nội dung và sử dụng nội dung trong tệp để cập nhật Google Trang tính bằng API Trang tính.

Đây có thể được xem là quá trình tự động "nhập dưới dạng tệp CSV" theo cách thủ công . Điều này đả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 có.

Quy trình triển khai sẽ diễn ra như sau :

52df703605ae4bd3.pngs

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

Thiết lập môi trường theo tiến độ riêng

Đăng nhập vào Cloud Console rồi tạo dự án mới hoặc sử dụng lại 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

Xin lưu ý rằng mã dự án là một tên riêng biệt trong tất cả dự án Google Cloud (tên ở trên đã được sử dụng nên sẽ không phù hợp với bạn!). Lớp này sẽ được đề cập sau trong lớp học lập trình này là PROJECT_ID.

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

Việc chạy qua lớp học lập trình này sẽ không tốn nhiều chi phí. Hãy nhớ làm theo mọi hướng dẫn trong phần "Dọn dẹp" sẽ tư vấn cho bạn cách tắt tài nguyên để bạn không phải chịu thanh toán ngoài 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í 300 USD.

3. Tạo và định cấu hình Google Trang tính rồi bật API

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

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

Trong phần "IAM và quản trị viên" hãy chuyển đến "Tài khoản dịch vụ" và ghi lại Email của tài khoản dịch vụ mặc định của App Engine. Mã này phải có định 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ụ của riêng mình dành riêng cho hành động này.

6e279d7e07d4fcf.png.

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

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

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

Hãy tạo bộ chứa mà chức năng đám mây của chúng ta sẽ giám sát 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ớ"... :

2ddcb54423979d25.pngS

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

5. Tạo hàm đám mây

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

6ee1a5ce63174ae8.png.

Đảm bảo đặt Trình kích hoạt thành "Cloud Storage" và để điều chỉnh tên nhóm theo tên bạn đã tạo ở bước trước.

Đồng thời, hãy cập nhật điểm nhập cho hàm chúng ta sắp ghi vào csv2sheet :

446e7c7c992c2d8a.png.

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

sử dụng API Trang tính và Cloud Storage
đánh dấu hàm csv2sheet là async
giữ fileName từ siêu dữ liệu sự kiện của Cloud Storage và đặt 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 bắt buộc phải sử dụng async ở đây để sử dụng await như chúng ta sẽ thấy ở đây.

Một số tuỳ chọn quan trọng khi tạo hàm này bao gồm (nhấp vào đường liên kết "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ụ như đã 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 đó :

Bước thiết lập cuối cùng là nội dung package.json, trong đó Cloud Storage và Google Trang tính 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 cùng dòng trên 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ư được mô tả, hãy tiếp tục và 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 tính năng xác thực và API Trang tính

Trước khi viết thêm mã trong hàm Cloud bằng trình chỉnh sửa cùng dòng, chúng ta cần chặn hoạt động tạo API ứng dụng của Google có phạm vi phù hợp cho Bộ nhớ và Trang tính (lưu ý 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 phần 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. Dùng API Trang tính để tạo một trang tính trống

Với ứng dụng Trang tính API, chúng ta có thể tạo một trang tính mới đơn giản trong tài liệu. Tuy nhiên, trước khi tiếp tục, hãy ghi chú nhanh về từ vựng:

bảng tính là tài liệu thực 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à hiển thị trong URL 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 giá trị nhận dạng được tạo khi tạo trang tính

Với lưu ý này, đây là một hàm sử dụng ứng dụng khách API Trang tính để tạo một trang tính trống ở vị trí 2 (thường sau "Sheet1" mặc định"), với 26 cột, 2000 hàng, với hàng đầu tiên bị cố định (thêm nó vào hàm của bạn bằng cách sử dụ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);
        }
      }
    );
  });
}

Lưu ý cách thay vì mã hoá cứng tham chiếu đến bảng tính, chúng ta sử dụng biến môi trường SPREADSHEET_ID đã tạo trước đó.

Chúng ta cần ghi nhớ sheetId đối với các yêu cầu khác đượ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ẽ không thành công 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ữ

Giờ đây, chúng ta đã có nơi để kết xuất dữ liệu, hãy phát triển thêm chức năng đá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ừ tệp vừa được 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 trang tính mới tạo

Đã đến lúc điền trang tính mà chúng ta đã tạo bằng cùng một API ứng dụng trang tính và dữ liệu chúng ta vừa thu thập được. Chúng ta cũng sẽ nhân cơ hội này để thêm một số kiểu vào 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 chữ được in đậ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();
      }
    });    
  });
}

Mã này sẽ được thêm vào hàm Cloud của chúng ta, hiện đã hoàn tất 99%!

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

Ngoài ra, xin lưu ý rằng chúng tôi xác định phạm vi chỉnh sửa phù hợp với kích thước của trang tính mà chúng tôi đã 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 dùng được với mã cụ thể này.

Nếu mọi việc đều suôn sẻ, thì tại thời điểm này bạn có thể:

lưu hàm đã cập nhật
thả một tệp CSV vào bộ chứa
hãy xem cửa sổ dữ liệu tương ứng bật lên trong bảng tính!

10. Kết hợp kiến thức đã học và kiểm thử luồng

Bạn có thể thực hiện các lệnh gọi đến các hàm vừa thảo luận dưới dạng 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 của hàm hoàn chỉnh, thì bạn có thể xem mã tại đây (có thể bạn sẽ dễ dàng lấy tất cả trong một bộ mã nguồn).

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

1efae021942e64fa.png.

Hãy thử tải một số tệp lên bộ chứa để xem điều gì sẽ xảy ra!

11. Vậy là xong! Đã đến lúc phá bỏ cơ sở hạ tầng

Đùa chút thôi, không có cơ sở hạ tầng nào để phá hỏng. Mọi việc đều được thực hiện mà không máy chủ!

Nếu muốn, bạn có thể xoá chức năng của đám mây và bộ chứa 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ớp học lập trình này sẽ hướng dẫn bạn các bước để nghe nội dung tải lên bộ chứa Cloud Storage trong Cloud Function để cập nhật Google Trang tính bằng API thích hợp.

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

Hãy xem hướng dẫn về Cloud Functions (bao gồm một số phương pháp hay nhất)
Xem qua một trong những hướng dẫn về Chức năng đám mây
Tìm hiểu thêm về API Google Trang tính

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

Chúng tôi rất mong nhận được ý kiến phản hồi của bạn!