Source
yaml
id: gsheet-to-bigquery
namespace: company.team
tasks:
- id: read_gsheet
type: io.kestra.plugin.googleworkspace.sheets.Read
description: Read data from Google Spreadsheet
spreadsheetId: 1ybRy9G-sGznXI9GM6FEb0duQyByJbgq4LoYd7oYFr5c
store: true
valueRender: FORMATTED_VALUE
- id: write_csv
type: io.kestra.plugin.serdes.csv.IonToCsv
description: Write CSV into Kestra internal storage
from: "{{ outputs.read_gsheet.uris.Sheet }}"
- id: load_biqquery
type: io.kestra.plugin.gcp.bigquery.Load
description: Load data into BigQuery
autodetect: true
csvOptions:
fieldDelimiter: ","
destinationTable: kestra-dev.demo.spotify_song_feature
format: CSV
from: "{{ outputs.write_csv.uri }}"
About this blueprint
Ingest Data GCP
Read a Google Spreadsheet and upload corresponding data to BigQuery.
To connect to Google Sheets, follow these steps:
- Enable Google Sheets API: If not done already, enable the Google Sheets API from the Google Cloud Console. - Create Service Account & Download the Key:
- Navigate to IAM & Admin → Service Accounts in Google Cloud Console.
- Create a new service account and download the JSON key.
- Share Sheet: Open the Google Sheet and share it with the email address of the service account.
Then, make sure to add the the contents of the Service Account to the
serviceAccountproperty. Here is how to find the Spreadsheet ID: https://docs.google.com/spreadsheets/d/**your_id**/edit#gid=0 The flow performs the following tasks:
- Reads the Google Sheet and stores the results in Kestra's internal storage (store: true). 2. The file in the internal storage is then converted into a CSV file. 3. The CSV file is loaded into BigQuery.