Menulis ke Google Spreadsheet dari alur kerja

Google Spreadsheet adalah solusi spreadsheet berbasis cloud yang mendukung kolaborasi real-time dan menyediakan alat untuk memvisualisasikan, memproses, dan mengomunikasikan data.

Contoh berikut menunjukkan cara menulis ke Spreadsheet dari alur kerja. Alur kerja mengkueri set data BigQuery dan menulis hasilnya ke spreadsheet Spreadsheet. Alur kerja ini menggunakan konektor Workflows untuk menyederhanakan panggilan Google Cloud API.

Sebelum memulai

Sebelum mencoba contoh dalam dokumen ini, pastikan Anda telah menyelesaikan hal berikut.

  1. Aktifkan Compute Engine, Google Drive, Google Spreadsheet, dan Workflows API.

    Konsol

    Aktifkan API

    gcloud 

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

  2. Catat akun layanan default Compute Engine Compute Engine karena Anda akan mengaitkannya dengan contoh alur kerja untuk tujuan pengujian. Project baru yang telah mengaktifkan Compute Engine API memiliki akun layanan ini yang dibuat dengan peran Editor dasar IAM, dan dengan format email berikut:

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    Anda dapat menemukan nomor project di halaman Selamat Datang di Google Cloud konsol atau Anda dapat mengambil nomor project:

    gcloud projects describe PROJECT_ID

    Untuk lingkungan produksi, sebaiknya buat akun layanan baru dan berikan satu atau beberapa peran IAM berisi izin minimum yang diperlukan dan mengikuti prinsip hak istimewa terendah.

  3. Buat folder baru di Google Drive. Folder ini digunakan untuk menyimpan spreadsheet Anda. Dengan menyiapkan izin untuk folder bersama, alur kerja Anda diizinkan untuk menulis ke spreadsheet.

    1. Buka drive.google.com.
    2. Klik Baru > Folder baru.
    3. Masukkan nama folder.
    4. Klik Buat.
    5. Klik kanan folder baru Anda, lalu pilih Bagikan.

    6. Tambahkan alamat email untuk akun layanan default Compute Engine.

      Tindakan ini akan memberikan akses akun layanan ke folder. Saat Anda mengaitkan akun layanan dengan alur kerja, alur kerja akan memiliki akses edit ke file apa pun di folder tersebut. Pelajari lebih lanjut cara berbagi file, folder, & drive.

    7. Pilih peran Editor.

    8. Hapus centang pada kotak Beri tahu orang.

    9. Klik Bagikan.

Membuat spreadsheet

Anda dapat membuat spreadsheet dengan salah satu cara berikut:

Tidak ada opsi untuk membuat spreadsheet langsung dalam folder tertentu menggunakan Google Sheets API. Namun, ada alternatifnya, termasuk memindahkan spreadsheet ke folder tertentu setelah Anda membuatnya, seperti yang dilakukan dalam contoh berikut. Untuk mengetahui informasi selengkapnya, lihat Bekerja dengan folder Google Drive.

Membuat spreadsheet menggunakan Google Spreadsheet

Saat Anda membuat spreadsheet melalui Google Spreadsheet, spreadsheet tersebut akan disimpan di Google Drive. Secara default, spreadsheet akan disimpan ke folder root Anda di Drive.

  1. Buka sheets.google.com.

  2. Klik Baru Plus.

    Tindakan ini akan membuat dan membuka spreadsheet baru Anda. Setiap spreadsheet memiliki nilai unik spreadsheetId yang berisi huruf, angka, tanda hubung, atau garis bawah. Anda dapat menemukan ID spreadsheet di URL Google Spreadsheet:

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

  3. Catat ID ini karena akan diperlukan saat Anda membuat alur kerja.

  4. Pindahkan spreadsheet ke folder Google Drive yang Anda buat sebelumnya:

    1. Di spreadsheet, pilih File > Pindahkan.
    2. Buka folder yang Anda buat.
    3. Klik Pindahkan.

Membuat spreadsheet menggunakan konektor Google Sheets API

Anda dapat menggunakan konektor Google Sheets API untuk membuat spreadsheet. Karena Workflows menggunakan akun layanan sebagai identitas pemicu, spreadsheet akan dibuat di folder root Google Drive akun layanan. Kemudian, Anda dapat memindahkan spreadsheet ke folder lain.

Dalam alur kerja berikut, spreadsheetId diambil dari hasil 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}"
        }
      }
    ]
  }
}

Ganti FOLDER_ID dengan ID folder tempat Anda ingin memindahkan spreadsheet. Setiap folder Drive memiliki ID unik yang berisi huruf, angka, tanda hubung, atau garis bawah. Anda dapat menemukan ID folder di URL folder:

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

Untuk mengetahui informasi selengkapnya, lihat Membuat dan mengisi folder.

Output dari alur kerja akan terlihat seperti berikut dengan nilai id adalah spreadsheetId:

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

Menjelajahi set data publik BigQuery

BigQuery menghosting sejumlah set data publik yang tersedia untuk umum dan dapat dikueri.

Di BigQuery, Anda dapat menjalankan tugas kueri interaktif (sesuai permintaan) . Misalnya, kueri berikut menampilkan 100 nama paling populer dalam set data tertentu dan menulis output ke tabel sementara. Ini adalah kueri yang akan dijalankan oleh alur kerja Anda.

Konsol

  1. Di Google Cloud konsol, buka halaman BigQuery.

    Buka BigQuery

  2. Masukkan kueri BigQuery SQL berikut di area teks Editor kueri:

    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. Klik Run.

bq

Di terminal, masukkan perintah bq query berikut untuk menjalankan kueri interaktif menggunakan sintaksis SQL standar:

    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'

Men-deploy alur kerja yang menulis ke Spreadsheet

Deploy alur kerja yang mengkueri set data BigQuery menggunakan konektor BigQuery API dan yang menulis hasilnya ke spreadsheet Spreadsheet menggunakan konektor Google Sheets API.

Konsol

  1. Di Google Cloud konsol, buka halaman Workflows:

    Buka Workflows

  2. Klik Buat.

  3. Masukkan nama untuk alur kerja baru: read-bigquery-write-sheets.

  4. Di daftar Region, pilih us-central1 (Iowa).

  5. Untuk Service account, pilih akun layanan default Compute Engine service account (PROJECT_NUMBER-compute@developer.gserviceaccount.com).

  6. Klik Berikutnya.

  7. Di editor alur kerja, masukkan definisi berikut untuk alur kerja Anda:

    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. Ganti nilai placeholder sheetId dengan Anda spreadsheetId.

  9. Klik Deploy.

gcloud

  1. Buat file kode sumber untuk alur kerja Anda:

    touch read-bigquery-write-sheets.yaml

  2. Di editor teks, salin alur kerja berikut ke file kode sumber Anda:

    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. Ganti nilai placeholder sheetId dengan Anda spreadsheetId.

  4. Deploy alur kerja dengan memasukkan perintah berikut:

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

    Ganti PROJECT_NUMBER dengan nomor Google Cloud project Anda. Anda dapat menemukan nomor project di halaman Selamat Datang di Google Cloud konsol.

Menjalankan alur kerja dan memverifikasi hasilnya

Menjalankan alur kerja akan menjalankan definisi alur kerja saat ini yang terkait dengan alur kerja.

  1. Jalankan alur kerja:

    Konsol

    1. Di Google Cloud konsol, buka halaman Workflows:

      Buka Workflows

    2. Di halaman Workflows, pilih alur kerja read-bigquery-write-sheets untuk membuka halaman detailnya.

    3. Di halaman Workflow details, klik Execute.

    4. Klik Execute lagi.

    5. Lihat hasil alur kerja di panel Output.

      Output-nya akan mirip dengan berikut ini:

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

    gcloud

    1. Buka terminal.

    2. Jalankan alur kerja:

      gcloud workflows run read-bigquery-write-sheets

      Hasil eksekusi akan terlihat seperti berikut:

      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. Pastikan alur kerja telah menulis hasil kueri ke spreadsheet Anda. Misalnya, jumlah kolom dan baris dalam spreadsheet harus cocok dengan nilai updatedColumns dan updatedRows.

Langkah berikutnya