כתיבה אל Google Sheets מתוך תהליך עבודה

מחברים של Workflows מאפשרים להפוך לאוטומטי את תהליך השאילתות של מערך נתונים ב-BigQuery ואת כתיבת התוצאות בגיליון אלקטרוני של Google Sheets. מחברים מפשטים את הקריאה לממשקי API Google Cloud על ידי טיפול באימות ובפורמט הבקשה בתהליך העבודה.

לפני שמתחילים

לפני שמנסים את הדוגמה במסמך הזה, חשוב לוודא שביצעתם את הפעולות הבאות.

  1. מפעילים את ממשקי ה-API של Compute Engine,‏ Google Drive,‏ Google Sheets ו-Workflows.

    המסוף

    הפעלת ממשקי ה-API

    gcloud 

    gcloud services enable \
    compute.googleapis.com \
    drive.googleapis.com \
    sheets.googleapis.com \
    workflows.googleapis.com

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

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    אפשר לראות את מספר הפרויקט בדף Welcome במסוף Google Cloud , או לאחזר אותו:

    gcloud projects describe PROJECT_ID

    בסביבות ייצור, מומלץ מאוד ליצור חשבון שירות חדש ולהקצות לו תפקיד אחד או יותר ב-IAM שמכילים את ההרשאות המינימליות הנדרשות, ולפעול לפי העיקרון של הרשאות מינימליות.

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

    1. בקר בכתובת drive.google.com.
    2. לוחצים על חדש > תיקייה חדשה.
    3. נותנים שם לתיקייה.
    4. לוחצים על יצירה.
    5. לוחצים לחיצה ימנית על התיקייה החדשה ובוחרים באפשרות שיתוף.

    6. מוסיפים את כתובת האימייל של חשבון השירות שמוגדר כברירת המחדל של Compute Engine.

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

    7. בוחרים את התפקיד עריכה.

    8. מבטלים את הסימון של התיבה שליחת הודעה לאנשים.

    9. לוחצים על שיתוף.

יצירת גיליון אלקטרוני

אפשר ליצור גיליון אלקטרוני באחת מהדרכים הבאות:

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

יצירת גיליון אלקטרוני באמצעות Google Sheets

כשיוצרים גיליון אלקטרוני באמצעות Google Sheets, הוא נשמר ב-Google Drive. כברירת מחדל, הגיליון האלקטרוני נשמר בתיקיית הבסיס ב-Drive.

  1. נכנסים אל sheets.google.com.

  2. לוחצים על חדש Plus.

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

    https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0

  3. חשוב לזכור את המזהה הזה, כי תצטרכו להשתמש בו כשתיצרו את תהליך העבודה.

  4. מעבירים את הגיליון האלקטרוני לתיקיית Google Drive שיצרתם קודם:

    1. בגיליון האלקטרוני, בוחרים באפשרות קובץ > העברה.
    2. עוברים לתיקייה שיצרתם.
    3. לוחצים על Move.

יצירת גיליון אלקטרוני באמצעות מחבר Google Sheets API

אתם יכולים להשתמש במחבר Google Sheets API כדי ליצור גיליון אלקטרוני. מכיוון שכלי Workflows משתמש בחשבון השירות כזהות של הטריגר, הגיליון האלקטרוני נוצר בתיקיית הבסיס של חשבון השירות ב-Google Drive. אחר כך תוכלו להעביר את הגיליון האלקטרוני לתיקייה אחרת.

בתהליך העבודה הבא, הערך spreadsheetId מאוחזר מהתוצאה resp:

YAML

main:
  steps:
    - init:
        assign:
          - folder_id: 'FOLDER_ID'
          - drive_url: 'https://www.googleapis.com/drive/v3/files/'
          - drive_auth_scope: 'https://www.googleapis.com/auth/drive'
    - create_sheet:
        call: googleapis.sheets.v4.spreadsheets.create
        args:
          body: null
          connector_params:
            scopes: 'https://www.googleapis.com/auth/drive'
        result: resp
    - get_sheet_info:
        call: http.get
        args:
          url: '${drive_url + resp.spreadsheetId}'
          query:
            fields: parents
          auth:
            type: OAuth2
            scopes: '${drive_auth_scope}'
        result: sheet_info
    - move_sheet:
        call: http.patch
        args:
          url: '${drive_url + resp.spreadsheetId}'
          query:
            addParents: '${folder_id}'
            removeParents: '${sheet_info["body"]["parents"][0]}'
          auth:
            type: OAuth2
            scopes: '${drive_auth_scope}'
        result: resp
    - return:
        return: '${resp}'

JSON

{
  "main": {
    "steps": [
      {
        "init": {
          "assign": [
            {
              "folder_id": "FOLDER_ID"
            },
            {
              "drive_url": "https://www.googleapis.com/drive/v3/files/"
            },
            {
              "drive_auth_scope": "https://www.googleapis.com/auth/drive"
            }
          ]
        }
      },
      {
        "create_sheet": {
          "call": "googleapis.sheets.v4.spreadsheets.create",
          "args": {
            "body": null,
            "connector_params": {
              "scopes": "https://www.googleapis.com/auth/drive"
            }
          },
          "result": "resp"
        }
      },
      {
        "get_sheet_info": {
          "call": "http.get",
          "args": {
            "url": "${drive_url + resp.spreadsheetId}",
            "query": {
              "fields": "parents"
            },
            "auth": {
              "type": "OAuth2",
              "scopes": "${drive_auth_scope}"
            }
          },
          "result": "sheet_info"
        }
      },
      {
        "move_sheet": {
          "call": "http.patch",
          "args": {
            "url": "${drive_url + resp.spreadsheetId}",
            "query": {
              "addParents": "${folder_id}",
              "removeParents": "${sheet_info[\"body\"][\"parents\"][0]}"
            },
            "auth": {
              "type": "OAuth2",
              "scopes": "${drive_auth_scope}"
            }
          },
          "result": "resp"
        }
      },
      {
        "return": {
          "return": "${resp}"
        }
      }
    ]
  }
}

מחליפים את FOLDER_ID במזהה של התיקייה שאליה רוצים להעביר את הגיליון האלקטרוני. לכל תיקייה ב-Drive יש מזהה ייחודי שמורכב מאותיות, ממספרים, ממקפים או מקווים תחתונים. אפשר למצוא את מזהה התיקייה בכתובת ה-URL של התיקייה:

https://drive.google.com/drive/folders/FOLDER_ID/edit#gid=0

מידע נוסף זמין במאמר בנושא יצירה של תיקיות והוספת קבצים לתיקיות.

הפלט של תהליך העבודה אמור להיראות כך, כאשר הערך id הוא spreadsheetId:

"body": {
    "id": "spreadsheetId",
    "kind": "drive#file",
    "mimeType": "application/vnd.google-apps.spreadsheet",
    "name": "Untitled spreadsheet"
  }

סקירה של מערך נתונים ציבורי ב-BigQuery

ב-BigQuery יש מספר מערכי נתונים ציבוריים שזמינים לציבור הרחב להרצת שאילתות.

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

המסוף

  1. במסוף Google Cloud , עוברים לדף BigQuery.

    כניסה לדף BigQuery

  2. מזינים את שאילתת ה-SQL הבאה של BigQuery באזור הטקסט של עורך השאילתות:

    SELECT name, gender, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY name, gender
ORDER BY total DESC
LIMIT 100

  3. לוחצים על Run.

BQ

בטרמינל, מזינים את הפקודה הבאה של bq query כדי להריץ שאילתה אינטראקטיבית באמצעות תחביר SQL סטנדרטי:

    bq query \
    --use_legacy_sql=false \
    'SELECT
      name, gender, SUM(number) AS total
    FROM
      `bigquery-public-data.usa_names.usa_1910_2013`
    GROUP BY
      name, gender
    ORDER BY
      total DESC
    LIMIT 100'

פריסת תהליך עבודה שכותב ל-Sheets

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

המסוף

  1. נכנסים לדף Workflows במסוף Google Cloud :

    כניסה לדף Workflows

  2. לוחצים על Create.

  3. מזינים שם לתהליך העבודה החדש: read-bigquery-write-sheets.

  4. ברשימה Region בוחרים באפשרות us-central1 (Iowa).

  5. בקטע חשבון שירות, בוחרים את חשבון השירות שמוגדר כברירת מחדל ב-Compute Engine‏ (PROJECT_NUMBER-compute@developer.gserviceaccount.com).

  6. לוחצים על הבא.

  7. בעורך של תהליך העבודה, מזינים את ההגדרה הבאה של תהליך העבודה:

    main:
    steps:
    - init:
        assign:
        # Replace with your sheetId and make sure the service account
        # for the workflow has write permissions to the sheet
        - sheetId: "1D8n7uoU8kGwQvR4rcLkF10CdAfnUKE2o0yl6P-Z7nfM"
        - limit: 100
    - runQuery:
        call: googleapis.bigquery.v2.jobs.query
        args:
            projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
            body:
                useLegacySql: false
                # Query name and gender of most popular names
                query: ${"SELECT name, gender, SUM(number) AS total
                    FROM `bigquery-public-data.usa_names.usa_1910_2013`
                    GROUP BY name, gender
                    ORDER BY total DESC
                    LIMIT " + limit}
        result: queryResult
    - init_header_row:
        assign:
        - rows:
            - ["Name", "Gender", "Total"]
    - process_query_result:
        for:
            value: row
            in: ${queryResult.rows}
            steps:
            - process_each_row:
                assign:
                - name: ${row.f[0].v}
                - gender: ${row.f[1].v}
                - total: ${row.f[2].v}
                - row: ["${name}", "${gender}", "${total}"]
                - rows: ${list.concat(rows, row)}
    - clear_existing_values:
        call: googleapis.sheets.v4.spreadsheets.values.clear
        args:
            range: "Sheet1"
            spreadsheetId: ${sheetId}
        result: clearResult
    - update_sheet:
        call: googleapis.sheets.v4.spreadsheets.values.update
        args:
            range: ${"Sheet1!A1:C" + (limit + 1)}
            spreadsheetId: ${sheetId}
            valueInputOption: RAW
            body:
                majorDimension: "ROWS"
                values: ${rows}
        result: updateResult
    - returnResult:
        return: ${updateResult}

  8. מחליפים את הערך של הפלייסולדר sheetId בערך של spreadsheetId.

  9. לוחצים על פריסה.

gcloud

  1. יוצרים קובץ קוד מקור לתהליך העבודה:

    touch read-bigquery-write-sheets.yaml

  2. בכלי לעריכת טקסט, מעתיקים את זרימת העבודה הבאה לקובץ קוד המקור:

    main:
    steps:
    - init:
        assign:
        # Replace with your sheetId and make sure the service account
        # for the workflow has write permissions to the sheet
        - sheetId: "1D8n7uoU8kGwQvR4rcLkF10CdAfnUKE2o0yl6P-Z7nfM"
        - limit: 100
    - runQuery:
        call: googleapis.bigquery.v2.jobs.query
        args:
            projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
            body:
                useLegacySql: false
                # Query name and gender of most popular names
                query: ${"SELECT name, gender, SUM(number) AS total
                    FROM `bigquery-public-data.usa_names.usa_1910_2013`
                    GROUP BY name, gender
                    ORDER BY total DESC
                    LIMIT " + limit}
        result: queryResult
    - init_header_row:
        assign:
        - rows:
            - ["Name", "Gender", "Total"]
    - process_query_result:
        for:
            value: row
            in: ${queryResult.rows}
            steps:
            - process_each_row:
                assign:
                - name: ${row.f[0].v}
                - gender: ${row.f[1].v}
                - total: ${row.f[2].v}
                - row: ["${name}", "${gender}", "${total}"]
                - rows: ${list.concat(rows, row)}
    - clear_existing_values:
        call: googleapis.sheets.v4.spreadsheets.values.clear
        args:
            range: "Sheet1"
            spreadsheetId: ${sheetId}
        result: clearResult
    - update_sheet:
        call: googleapis.sheets.v4.spreadsheets.values.update
        args:
            range: ${"Sheet1!A1:C" + (limit + 1)}
            spreadsheetId: ${sheetId}
            valueInputOption: RAW
            body:
                majorDimension: "ROWS"
                values: ${rows}
        result: updateResult
    - returnResult:
        return: ${updateResult}

  3. מחליפים את הערך של הפלייסולדר sheetId בערך של spreadsheetId.

  4. מריצים את הפקודה הבאה כדי לפרוס את תהליך העבודה:

    gcloud workflows deploy read-bigquery-write-sheets \
    --source=read-bigquery-write-sheets.yaml \
    --location=us-central1 \
    --service-account=PROJECT_NUMBER-compute@developer.gserviceaccount.com

    מחליפים את PROJECT_NUMBER במספר הפרויקט ב- Google Cloud. אפשר לראות את מספר הפרויקט בדף Welcome במסוף Google Cloud .

מריצים את תהליך העבודה ומאמתים את התוצאות

כשמריצים תהליך עבודה, מופעלת ההגדרה הנוכחית של תהליך העבודה שמשויכת לתהליך העבודה.

  1. מריצים את תהליך העבודה:

    המסוף

    1. נכנסים לדף Workflows במסוף Google Cloud :

      כניסה לדף Workflows

    2. בדף Workflows, בוחרים את תהליך העבודה read-bigquery-write-sheets כדי לעבור לדף הפרטים שלו.

    3. בדף פרטי תהליך העבודה, לוחצים על הפעלה.

    4. לוחצים שוב על Execute.

    5. התוצאות של תהליך העבודה מוצגות בחלונית פלט.

      הפלט אמור להיראות כך:

      {
"spreadsheetId": "1pgkNY7mWICA6o6INUNLHGnmk0Tdioh0nUghZPOYPpZA",
"updatedCells": 303,
"updatedColumns": 3,
"updatedRange": "Sheet1!A1:C101",
"updatedRows": 101
}

    gcloud

    1. פותחים טרמינל.

    2. מריצים את תהליך העבודה:

      gcloud workflows run read-bigquery-write-sheets

      תוצאות ההרצה צריכות להיות דומות לתוצאות הבאות:

      Waiting for execution [4dcf737b-69d9-4081-b8d9-86d39ae86bd1] to complete...done.     
argument: 'null'
duration: 3.131912897s
endTime: '2023-01-25T14:59:46.818828242Z'
name: projects/918619793306/locations/us-central1/workflows/read-bigquery-write-sheets/executions/4dcf737b-69d9-4081-b8d9-86d39ae86bd1
result: '{"spreadsheetId":"1pgkNY7mWICA6o6INUNLHGnmk0Tdioh0nUghZPOYPpZA","updatedCells":303,"updatedColumns":3,"updatedRange":"Sheet1!A1:C101","updatedRows":101}'
startTime: '2023-01-25T14:59:43.686915345Z'
state: SUCCEEDED

  2. מוודאים שתהליך העבודה כתב את תוצאות השאילתה בגיליון האלקטרוני. לדוגמה, מספר העמודות והשורות בגיליון האלקטרוני צריך להיות זהה לערכים של updatedColumns ושל updatedRows.

המאמרים הבאים