פונקציה של Cloud Functions ליצירת אוטומציה של ייבוא נתוני CSV ל-Google Sheets

1. מבוא

ב-codelab הזה נסביר איך לכתוב Cloud Function שתגיב להעלאה של קובץ CSV ל-Cloud Storage, תקרא את התוכן שלו ותשתמש בו כדי לעדכן גיליון אלקטרוני ב-Google Sheets באמצעות Sheets API.

אפשר לראות בזה אוטומציה של השלב 'ייבוא כקובץ CSV', שבדרך כלל מתבצע באופן ידני. כך תוכלו לנתח נתונים (אולי כאלה שנוצרו על ידי צוות אחר) בגיליון אלקטרוני ברגע שהם יהיו זמינים.

כך נראה היישום :

2. הגדרה ודרישות

הגדרת סביבה בקצב אישי

נכנסים אל Cloud Console ויוצרים פרויקט חדש או משתמשים בפרויקט קיים. (אם עדיין אין לכם חשבון Gmail או G Suite, אתם צריכים ליצור חשבון).

dMbN6g9RawQj_VXCSYpdYncY-DbaRzr2GbnwoV7jFf1u3avxJtmGPmKpMYgiaMH-qu80a_NJ9p2IIXFppYk8x3wyymZXavjglNLJJhuXieCem56H30hwXtd8PvXGpXJO9gEUDu3cZw

ci9Oe6PgnbNuSYlMyvbXF1JdQyiHoEgnhl4PlV_MFagm2ppzhueRkqX4eLjJllZco_2zCp0V0bpTupUSKji9KkQyWqj11pqit1K1faS1V6aFxLGQdkuzGp4rsQTan7F01iePL5DtqQ

8-tA_Lheyo8SscAVKrGii2coplQp2_D1Iosb2ViABY0UUO1A8cimXUu6Wf1R9zJIRExL5OB2j946aIiFtyKTzxDcNnuznmR45vZ2HMoK3o67jxuoUJCAnqvEX6NgPGFjCVNgASc-lg

חשוב לזכור את מזהה הפרויקט, שהוא שם ייחודי בכל הפרויקטים ב-Google Cloud (השם שלמעלה כבר תפוס ולא יתאים לכם, מצטערים!). בהמשך ה-codelab הזה נתייחס אליו כאל PROJECT_ID.

לאחר מכן, תצטרכו להפעיל את החיוב ב-Cloud Console כדי להשתמש במשאבים של Google Cloud.

העלות של התרגול הזה לא אמורה להיות גבוהה, ואולי אפילו לא תצטרכו לשלם בכלל. חשוב לפעול לפי ההוראות בקטע 'ניקוי' כדי להשבית את המשאבים, וכך לא תחויבו אחרי שתסיימו את המדריך הזה. משתמשים חדשים ב-Google Cloud זכאים לתוכנית תקופת ניסיון בחינם בשווי 300$.

3. יצירה והגדרה של גיליון אלקטרוני ב-Google Sheets והפעלת ה-API

קודם יוצרים מסמך חדש ב-Sheets (הגיליון הזה יכול להיות שייך לכל משתמש). אחרי שיוצרים את הפונקציה, חשוב לזכור את המזהה שלה. הוא ישמש כמשתנה סביבה עבור הפונקציה שנכתוב :

ב-GCP Console, מפעילים את Google Sheets API בפרויקט החדש שיצרתם. לשם כך, עוברים לקטע APIs and Services (ממשקי API ושירותים) ואז לקטע API Library (ספריית ממשקי API):

בקטע IAM & admin (ניהול הרשאות וחשבונות אדמין), עוברים אל Service accounts (חשבונות שירות) ורושמים את כתובת האימייל של חשבון השירות שמוגדר כברירת מחדל ב-App Engine. הפורמט הנדרש הוא your-project-id@appspot.gserviceaccount.com. כמובן שאפשר גם ליצור חשבון שירות משלכם שמוקדש לפעולה הזו.

לבסוף, פשוט מעניקים לחשבון השירות הזה הרשאות עריכה בגיליון האלקטרוני באמצעות הלחצן 'שיתוף':

אחרי ההגדרה הזו, אפשר לכתוב את Cloud Function ולהגדיר אותה לשימוש בחשבון השירות הזה. הוא יוכל לכתוב בגיליון האלקטרוני שיצרנו עכשיו.

4. יצירה של קטגוריית אחסון

ניצור את הקטגוריה שהפונקציה שלנו ב-Cloud תעקוב אחריה כדי לזהות קובצי CSV חדשים.

במסוף, משתמשים בתפריט הימני כדי לנווט אל 'אחסון'... :

‫… and create a new bucket called csv2sheet-POSTFIX (replace the POSTFIX with something unique) with all other settings set to their default values :

5. יצירת הפונקציה של Cloud Functions

עכשיו אפשר ליצור פונקציה של Cloud Functions בשם csv2sheet שמופעלת כשמעלים קבצים לקטגוריה של Cloud Storage ספציפית. הקוד ייכתב ב-Node.js 8 עם פונקציות אסינכרוניות באמצעות העורך המוטבע ישירות במסוף Cloud :

חשוב להגדיר את הטריגר ל-Cloud Storage ולשנות את שם הקטגוריה לשם הקטגוריה שיצרתם בשלב הקודם.

מעדכנים גם את נקודת הכניסה לפונקציה שאנחנו עומדים לכתוב ל-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, כפי שנראה בהמשך.

יש כמה אפשרויות חשובות שמופיעות כשיוצרים את הפונקציה הזו (צריך ללחוץ על הקישור 'עוד' בתחתית המסך) :

בתפריט הנפתח, בוחרים את חשבון השירות שצוין למעלה.
מגדירים משתנה סביבה בשם SPREADSHEET_ID שצריך להיות זהה למסמך הגיליון האלקטרוני שיצרתם קודם :

בשלב האחרון של ההגדרה, הנה תוכן הקובץ package.json עם Cloud Storage ו-Google Sheet APIs כשני יחסי התלות שבהם נשתמש (משתמשים בכרטיסייה PACKAGE.JSON של העורך המוטבע במסוף) :

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

אחרי שמגדירים את הכול כמו שמתואר, לוחצים על 'יצירה'. אחרי דקה קצרה, הפונקציה אמורה להיווצר ולהיפרס.

6. הגדרת אימות ו-Sheets API

לפני שנכתוב קוד נוסף בפונקציה של Cloud Functions באמצעות עורך בשורה, נצטרך לחסום את היצירה של Google Client API עם ההיקפים המתאימים של Storage ו-Sheet (חשוב לזכור שזה חלק מפונקציה של Cloud Functions async).

בעורך הפונקציות במסוף, לוחצים על EDIT (עריכה) ומוסיפים את הקוד הבא לגוף של פונקציית 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. שימוש ב-Sheets API כדי ליצור גיליון ריק

בעזרת לקוח Sheets API, אפשר ליצור גיליון אלקטרוני חדש ופשוט במסמך. אבל לפני שנמשיך, הנה הסבר קצר על המונחים:

גיליון אלקטרוני הוא המסמך בפועל, וההפניה אליו מתבצעת באמצעות המזהה שלו (שמוסבר למעלה ומופיע בכתובת ה-URL של המסמך)
גיליון הוא אחד מהכרטיסיות במסמך, ואפשר להתייחס אליו לפי השם שלו (שם הכרטיסייה) או לפי מזהה שנוצר כשהגיליון נוצר

בהתאם לכך, הנה פונקציה שמשתמשת בלקוח Sheets API כדי ליצור גיליון ריק במיקום 2 (בדרך כלל אחרי ברירת המחדל Sheet1), עם 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 ב-Sheets 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. מאכלסים את הגיליון החדש שנוצר

עכשיו צריך לאכלס את הגיליון שיצרנו באמצעות אותו Sheets client 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 שלנו, שהשלמנו כבר 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 לדוגמה.

כדאי לנסות להעלות כמה קבצים ל-bucket כדי לראות מה קורה.

11. זהו! הגיע הזמן להסיר את התשתית

סתם צוחק, אין תשתית לביטול, הכל נעשה ללא שרתים!

אם רוצים, אפשר למחוק את פונקציית הענן ואת הקטגוריה שיצרתם, או אפילו את הפרויקט כולו.

12. מה השלב הבא?

זה מסכם את ה-Codelab הזה, שמדריך אתכם בשלבים להאזנה להעלאות לקטגוריה של Cloud Storage בפונקציה של Cloud Functions כדי לעדכן גיליון אלקטרוני ב-Google Sheets באמצעות ה-API המתאים.

הנה כמה פעולות שאפשר לבצע :

כדאי לעיין במדריכים של Cloud Functions (כולל כמה שיטות מומלצות)
עוברים על אחד מהמדריכים של Cloud Functions
מידע נוסף על Google Sheets API

אם נתקלתם בבעיות ב-codelab הזה, אתם יכולים לדווח על כל בעיה באמצעות הקישור בפינה השמאלית התחתונה.

נשמח לקבל ממך משוב!