워크플로에서 Google Sheets에 쓰기

Google Sheets는 실시간 공동작업을 지원하고 데이터를 시각화, 처리, 소통하는 도구를 제공하는 클라우드 기반의 스프레드시트 솔루션입니다.

다음 예시는 워크플로에서 Sheets에 쓰는 방법을 보여줍니다. 이 워크플로는 BigQuery 데이터 세트를 쿼리하고 결과를 Sheets 스프레드시트에 씁니다. 워크플로 커넥터를 사용하여 Google Cloud API 호출을 간소화합니다.

시작하기 전에

이 문서의 예시를 시도하려면 다음 작업이 완료되었는지 확인합니다.

  1. Compute Engine, Google Drive, Google Sheets, Workflows API를 사용 설정합니다.

    콘솔

    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

    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 값이 포함됩니다. Google Sheets URL에서 스프레드시트 ID를 확인할 수 있습니다.

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

  3. 이 ID는 워크플로를 작성할 때 필요하므로 참고하세요.

  4. 스프레드시트를 이전에 만든 Google Drive 폴더로 이동합니다.

    1. 스프레드시트에서 파일 > 이동을 선택합니다.
    2. 만든 폴더로 이동합니다.
    3. 이동을 클릭합니다.

Google Sheets API 커넥터를 사용하여 스프레드시트 만들기

Google Sheets API 커넥터를 사용하여 스프레드시트를 만들 수 있습니다. 워크플로에서 서비스 계정을 트리거 ID로 사용하므로 스프레드시트는 서비스 계정의 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를 스프레드시트를 이동하려는 폴더 ID로 바꿉니다. 모든 Drive 폴더에는 문자, 숫자, 하이픈, 밑줄로 구성된 고유한 ID가 포함됩니다. 폴더 ID는 폴더 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. 쿼리 편집기 텍스트 영역에 다음 BigQuery SQL 쿼리를 입력합니다.

    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. 실행을 클릭합니다.

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 API 커넥터를 사용하여 BigQuery 데이터 세트를 쿼리하고 Google Sheets API 커넥터를 사용하여 결과를 Sheets 스프레드시트에 쓰는 워크플로를 배포합니다.

콘솔

  1. Google Cloud 콘솔에서 Workflows 페이지로 이동합니다.

    Workflows로 이동

  2. 만들기를 클릭합니다.

  3. 새 워크플로의 이름 read-bigquery-write-sheets를 입력합니다.

  4. 리전 목록에서 us-central1(아이오와)을 선택합니다.

  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 프로젝트 번호로 바꿉니다. 프로젝트 번호는 Google Cloud 콘솔의 시작 페이지에서 찾을 수 있습니다.

워크플로 실행 및 결과 확인

워크플로를 실행하면 워크플로와 연결된 현재 워크플로 정의가 실행됩니다.

  1. 워크플로를 실행합니다.

    콘솔

    1. Google Cloud 콘솔에서 Workflows 페이지로 이동합니다.

      Workflows로 이동

    2. 워크플로 페이지에서 read-bigquery-write-sheets 워크플로를 선택하여 세부정보 페이지로 이동합니다.

    3. 워크플로 세부정보 페이지에서 실행을 클릭합니다.

    4. 실행을 다시 클릭합니다.

    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 값과 일치해야 합니다.

다음 단계