Cloud Function จะนำเข้าข้อมูล CSV ไปยัง Google ชีตโดยอัตโนมัติ

1. บทนำ

เป้าหมายของโค้ดแล็บนี้คือการช่วยให้คุณเข้าใจวิธีเขียน Cloud Function เพื่อตอบสนองต่อการอัปโหลดไฟล์ CSV ไปยัง Cloud Storage, อ่านเนื้อหาของไฟล์ และใช้เนื้อหาดังกล่าวเพื่ออัปเดต Google ชีตโดยใช้ Sheets API

ซึ่งถือได้ว่าเป็นการทำงานอัตโนมัติของขั้นตอน "นำเข้าเป็น CSV" ที่ต้องทำด้วยตนเอง ซึ่งจะช่วยให้คุณวิเคราะห์ข้อมูล (อาจสร้างโดยทีมอื่น) ในสเปรดชีตได้ทันทีที่พร้อมใช้งาน

การติดตั้งใช้งานจะมีลักษณะดังนี้

2. การตั้งค่าและข้อกำหนด

การตั้งค่าสภาพแวดล้อมแบบเรียนรู้ด้วยตนเอง

ลงชื่อเข้าใช้ Cloud Console แล้วสร้างโปรเจ็กต์ใหม่หรือใช้โปรเจ็กต์ที่มีอยู่ซ้ำ (หากยังไม่มีบัญชี Gmail หรือ G Suite คุณต้องสร้างบัญชี)

dMbN6g9RawQj_VXCSYpdYncY-DbaRzr2GbnwoV7jFf1u3avxJtmGPmKpMYgiaMH-qu80a_NJ9p2IIXFppYk8x3wyymZXavjglNLJJhuXieCem56H30hwXtd8PvXGpXJO9gEUDu3cZw

ci9Oe6PgnbNuSYlMyvbXF1JdQyiHoEgnhl4PlV_MFagm2ppzhueRkqX4eLjJllZco_2zCp0V0bpTupUSKji9KkQyWqj11pqit1K1faS1V6aFxLGQdkuzGp4rsQTan7F01iePL5DtqQ

8-tA_Lheyo8SscAVKrGii2coplQp2_D1Iosb2ViABY0UUO1A8cimXUu6Wf1R9zJIRExL5OB2j946aIiFtyKTzxDcNnuznmR45vZ2HMoK3o67jxuoUJCAnqvEX6NgPGFjCVNgASc-lg

โปรดจดจำรหัสโปรเจ็กต์ ซึ่งเป็นชื่อที่ไม่ซ้ำกันในโปรเจ็กต์ Google Cloud ทั้งหมด (ชื่อด้านบนมีผู้ใช้แล้วและจะใช้ไม่ได้ ขออภัย) ซึ่งจะเรียกว่า PROJECT_ID ในภายหลังใน Codelab นี้

จากนั้นคุณจะต้องเปิดใช้การเรียกเก็บเงินใน Cloud Console เพื่อใช้ทรัพยากร Google Cloud

การทำตาม Codelab นี้ไม่ควรมีค่าใช้จ่ายมากนัก หรืออาจไม่มีเลย โปรดทำตามวิธีการในส่วน "การล้างข้อมูล" ซึ่งจะแนะนำวิธีปิดทรัพยากรเพื่อไม่ให้มีการเรียกเก็บเงินนอกเหนือจากบทแนะนำนี้ ผู้ใช้ Google Cloud รายใหม่มีสิทธิ์เข้าร่วมโปรแกรมช่วงทดลองใช้ฟรีมูลค่า$300 USD

3. สร้างและกำหนดค่า Google ชีต แล้วเปิดใช้ API

ก่อนอื่นมาสร้างเอกสารชีตใหม่กัน (ชีตนี้จะเป็นของผู้ใช้คนใดก็ได้) เมื่อสร้างแล้ว ให้จดจำตัวระบุของฟังก์ชันดังกล่าวไว้ เนื่องจากเราจะใช้ตัวระบุนี้เป็นตัวแปรสภาพแวดล้อมสำหรับฟังก์ชันที่เราจะเขียน

จากคอนโซล GCP ให้เปิดใช้ Google Sheets API ในโปรเจ็กต์ที่สร้างขึ้นใหม่โดยไปที่ส่วน "API และบริการ" แล้วไปที่ "คลัง API" ดังนี้

ในส่วน "IAM และผู้ดูแลระบบ" ให้ไปที่ "บัญชีบริการ" และจดอีเมลสำหรับบัญชีบริการเริ่มต้นของ App Engine โดยควรอยู่ในรูปแบบ your-project-id@appspot.gserviceaccount.com แน่นอนว่าคุณยังสร้างบัญชีบริการของคุณเองที่ใช้สำหรับการดำเนินการนี้โดยเฉพาะได้ด้วย

สุดท้าย ให้สิทธิ์แก้ไขสเปรดชีตแก่บัญชีบริการนี้โดยใช้ปุ่ม "แชร์" ดังนี้

เมื่อตั้งค่าแล้ว เราจะเขียน Cloud Function และกำหนดค่าให้ใช้บัญชีบริการนี้ได้ โดยจะเขียนลงในเอกสารสเปรดชีตที่เราเพิ่งสร้างได้

4. สร้างที่เก็บข้อมูล

มาสร้าง Bucket ที่ฟังก์ชันระบบคลาวด์จะใช้ตรวจสอบไฟล์ CSV ใหม่กัน

ในคอนโซล ให้ใช้เมนูด้านซ้ายเพื่อไปที่ "พื้นที่เก็บข้อมูล"... :

... และสร้างที่เก็บข้อมูลใหม่ชื่อ csv2sheet-POSTFIX (แทนที่ POSTFIX ด้วยชื่อที่ไม่ซ้ำกัน) โดยตั้งค่าอื่นๆ ทั้งหมดเป็นค่าเริ่มต้น

5. สร้าง Cloud Function

ตอนนี้เราสามารถสร้าง Cloud Function ที่ชื่อ csv2sheet ซึ่งจะทริกเกอร์เมื่อมีการอัปโหลดไฟล์ไปยัง Bucket ของ Cloud Storage ที่เฉพาะเจาะจง โค้ดจะเขียนใน Node.js 8 ด้วยฟังก์ชันแบบอะซิงโครนัสโดยใช้เครื่องมือแก้ไขแบบอินไลน์ใน Cloud Console โดยตรง

โปรดตรวจสอบว่าได้ตั้งค่าทริกเกอร์เป็น "Cloud Storage" และปรับชื่อ Bucket เป็นชื่อที่คุณสร้างในขั้นตอนก่อนหน้า

นอกจากนี้ ให้อัปเดตจุดแรกเข้าสำหรับฟังก์ชันที่เรากำลังจะเขียนเป็น csv2sheet ดังนี้

ตอนนี้ให้เปลี่ยนเนื้อหาของฟังก์ชันเป็น

ใช้ Cloud Storage และ Sheets API
ทำเครื่องหมายฟังก์ชัน csv2sheet เป็น async
รับ fileName จากข้อมูลเมตาของเหตุการณ์ Cloud Storage และตั้งชื่อชีตใหม่ที่เราจะสร้าง

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!
};

ต้องใช้ async ที่นี่เพื่อใช้ await ดังที่เราจะเห็นในอีกสักครู่

ตัวเลือกสำคัญ 2 อย่างขณะสร้างฟังก์ชันนี้ ได้แก่ (คลิกลิงก์ "เพิ่มเติม" ที่ด้านล่างของหน้าจอ)

ใช้เมนูแบบเลื่อนลงเพื่อเลือกบัญชีบริการที่กล่าวถึงข้างต้น
กำหนดตัวแปรสภาพแวดล้อมชื่อ SPREADSHEET_ID ซึ่งควรตรงกับเอกสารชีตที่คุณสร้างไว้ก่อนหน้านี้ ดังนี้

ขั้นตอนการตั้งค่าสุดท้ายคือpackage.json เนื้อหาที่มี Cloud Storage และ Google Sheet API เป็น 2 การขึ้นต่อกันที่เราจะใช้ (ใช้แท็บ PACKAGE.JSON ของโปรแกรมแก้ไขแบบอินไลน์ของคอนโซล)

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

เมื่อกำหนดค่าทุกอย่างตามที่อธิบายไว้แล้ว ให้คลิก "สร้าง" ได้เลย หลังจากผ่านไปไม่นาน ระบบจะสร้างและติดตั้งใช้งานฟังก์ชัน

6. ตั้งค่าการให้สิทธิ์และ Sheets API

ก่อนที่จะเขียนโค้ดเพิ่มเติมในฟังก์ชัน Cloud โดยใช้โปรแกรมแก้ไขแบบอินไลน์ เราต้องบล็อกการสร้าง Google Client API ด้วยขอบเขต Storage และชีตที่เหมาะสม (โปรดทราบว่านี่เป็นส่วนหนึ่งของasyncฟังก์ชัน)

ในตัวแก้ไขฟังก์ชันของคอนโซล ให้คลิก "แก้ไข" แล้วเพิ่มโค้ดต่อไปนี้ลงในส่วนเนื้อหาของฟังก์ชัน 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"
  ]
});

จากนั้นเราจะสร้างไคลเอ็นต์ Sheets API ได้โดยทำดังนี้

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

7. ใช้ชีต API เพื่อสร้างชีตเปล่า

เมื่อใช้ไคลเอ็นต์ชีต API เราจะสร้างชีตใหม่แบบง่ายๆ ในเอกสารได้ แต่ก่อนที่จะดำเนินการต่อ เรามาดูคำศัพท์ที่ใช้กันก่อน

สเปรดชีตคือเอกสารจริงและอ้างอิงโดยตัวระบุ (อธิบายไว้ข้างต้นและมองเห็นได้ใน URL ของเอกสาร)
ชีตเป็นหนึ่งในแท็บในเอกสาร และสามารถอ้างอิงได้ตามชื่อ (ชื่อแท็บ) หรือตัวระบุที่สร้างขึ้นเมื่อสร้างชีต

ด้วยเหตุนี้ เราจึงมีฟังก์ชันที่ใช้ไคลเอ็นต์ Sheets API เพื่อสร้างชีตเปล่าที่ตำแหน่ง 2 (โดยปกติจะอยู่หลัง "ชีต1" เริ่มต้น) โดยมี 26 คอลัมน์ 2,000 แถว และตรึงแถวแรกไว้ (เพิ่มลงในฟังก์ชันโดยใช้เครื่องมือแก้ไขแบบอินไลน์) ดังนี้

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);
        }
      }
    );
  });
}

โปรดสังเกตว่าแทนที่จะเขียนโค้ดอ้างอิงไปยังสเปรดชีต เราจะใช้ตัวแปรสภาพแวดล้อม SPREADSHEET_ID ที่สร้างไว้ก่อนหน้านี้

เราต้องจดจำ sheetId สำหรับคำขอเพิ่มเติมที่ส่งไปยังชีตนี้ นอกจากนี้ ชื่อชีตต้องไม่ซ้ำกัน และการสร้างจะล้มเหลวหากมีชีตชื่อ sheetName อยู่แล้ว

ฟังก์ชัน batchUpdate ในชีต API เป็นวิธีทั่วไปในการโต้ตอบกับเอกสารและอธิบายไว้ที่นี่

8. อ่านข้อมูลจากไฟล์ CSV ของพื้นที่เก็บข้อมูล

ตอนนี้เรามีที่เก็บข้อมูลแล้ว มาพัฒนาฟังก์ชัน Cloud ในเครื่องมือแก้ไขแบบอินไลน์ต่อ และใช้ Cloud Storage API เพื่อไปดึงข้อมูลจริงจากไฟล์ที่เพิ่งอัปโหลด แล้วจัดเก็บไว้ในสตริงกัน

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. ป้อนข้อมูลในชีตที่สร้างขึ้นใหม่

ตอนนี้ก็ถึงเวลาป้อนข้อมูลลงในชีตที่เราสร้างขึ้นโดยใช้ API ไคลเอ็นต์ชีตเดียวกันและข้อมูลที่เราเพิ่งรวบรวม เราจะใช้โอกาสนี้ในการเพิ่มการจัดรูปแบบบางอย่างลงในคอลัมน์ของชีตด้วย (เปลี่ยนขนาดแบบอักษรของแถวบนสุดและทำให้เป็นตัวหนา) ดังนี้

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();
      }
    });    
  });
}

เราควรเพิ่มโค้ดนี้ลงใน Cloud Function ซึ่งตอนนี้เสร็จสมบูรณ์แล้ว 99%

โปรดสังเกตว่าทั้งข้อมูลและการจัดรูปแบบจะรวมกันเป็นหลายๆ requests ในการเรียกใช้ Sheets API batchUpdate ครั้งเดียว ซึ่งจะช่วยให้การอัปเดตมีประสิทธิภาพและเป็นแบบอะตอมมากขึ้น

นอกจากนี้ โปรดทราบว่าเรากำหนดช่วงการแก้ไขให้ตรงกับขนาดของชีตที่เราสร้างขึ้น ซึ่งหมายความว่าเนื้อหาที่มีคอลัมน์เกิน 26 คอลัมน์ (ค่า columnCount ที่ใช้เมื่อสร้างชีต) จะล้มเหลวด้วยโค้ดนี้

หากทุกอย่างเป็นไปด้วยดี คุณจะทำสิ่งต่อไปนี้ได้

บันทึกฟังก์ชันที่อัปเดต
วางไฟล์ CSV ลงในที่เก็บข้อมูล
ดูข้อมูลที่เกี่ยวข้องปรากฏขึ้นในสเปรดชีต

10. การรวมทุกอย่างเข้าด้วยกันและการทดสอบโฟลว์

การเรียกใช้ฟังก์ชันที่เราเพิ่งพูดถึงสามารถทำได้เป็นการเรียกใช้แบบบล็อกต่อเนื่องในฟังก์ชัน csv2sheet เดิม ดังนี้

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

หากต้องการซอร์สโค้ดฟังก์ชันทั้งหมด คุณสามารถดูได้ที่นี่ (อาจจะง่ายกว่าหากจะรับทั้งหมดในชุดเดียว)

เมื่อทุกอย่างพร้อมแล้ว เพียงอัปโหลดไฟล์ CSV ไปยังที่เก็บข้อมูลที่ถูกต้อง แล้วดูการอัปเดตสเปรดชีตด้วยชีตใหม่ที่มีเนื้อหาของไฟล์ หากคุณยังไม่มีไฟล์ CSV โปรดดูไฟล์ CSV ตัวอย่าง

ลองอัปโหลดไฟล์หลายไฟล์ไปยังที่เก็บข้อมูลเพื่อดูว่าจะเกิดอะไรขึ้น

11. เท่านี้ก็เรียบร้อย ได้เวลาทำลายโครงสร้างพื้นฐานแล้ว

ล้อเล่นนะ เราไม่ต้องรื้อโครงสร้างพื้นฐานใดๆ เพราะทุกอย่างทำแบบ Serverless

คุณสามารถลบฟังก์ชัน Cloud และที่เก็บข้อมูลที่สร้างขึ้น หรือแม้แต่ทั้งโปรเจ็กต์ได้หากต้องการ

12. ขั้นตอนต่อไปคืออะไร

จบ Codelab นี้แล้ว ซึ่งจะแนะนำขั้นตอนในการฟังการอัปโหลดไปยังที่เก็บข้อมูล Cloud Storage ใน Cloud Function เพื่ออัปเดต Google ชีตโดยใช้ API ที่เหมาะสม

ขั้นตอนการติดตามมีดังนี้

ดูคำแนะนำวิธีใช้ของ Cloud Functions (รวมถึงแนวทางปฏิบัติแนะนำบางส่วน)
ดูบทแนะนำของ Cloud Functions
สำรวจ Google Sheets API เพิ่มเติม

หากพบปัญหาเกี่ยวกับโค้ดแล็บนี้ โปรดรายงานปัญหาโดยใช้ลิงก์ที่มุมซ้ายล่าง

ขอขอบคุณสำหรับความคิดเห็นของคุณ