# Copyright 2022 Google LLC

#

# Licensed under the Apache License, Version 2.0 (the "License");

# you may not use this file except in compliance with the License.

# You may obtain a copy of the License at

#

# http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License.

# This workflow queries top names from a public dataset in BigQuery

# and writes the results to a Google Sheets spreadsheet.

# [START workflows_sheets_bigquery]

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}

# [END workflows_sheets_bigquery]