Transfer · From Tasks · from_google_sheets

Purpose

Imports data from a Google Spreadsheet.

Method of use

Before you can download data from a Google Spreadsheet you have to give acces. There are two ways to do this:

Acces with Service Account

Take the steps below to download a Google Spreadsheet with a service account.

  1. Create a service account or re-use a service account of a Google Cloud environment.
  2. Have the service account added to the Transfer connections by a Onesecondbefore staff member
  3. If you use Google Cloud, you're done. If you use another cloud, add the connection string to conn_id
  4. Give the client_email from the Service Account `Read` access to the Google spreadsheet
  5. You should now be able to download the contents of the first sheet of the Google Spreadsheet

Access with Delegated Account

Take the steps below to download a Google Spreadsheet with a delegated account. Please take note of the security precautions needed to secure this connection method.

  1. Take the steps 1 till 3 in the Service Account flow.
  2. Enable domain-wide delegation for the service account.
  3. Give the delegated account `Read` access to the Google spreadsheet
  4. You should now be able to download the contents of the first sheet of the Google Spreadsheet

Retrieve the file ID

To retrieve the file ID do the following:

  1. Open the spreadsheet whose data you want to download. The API only allows you to donwload the data of the first sheet
  2. Click `Share`
  3. Press `Copy Link`
  4. You should see a link like: https://docs.google.com/spreadsheets/d/1orAc9s3DWu5_G5m3UGxuf9ibMh2P7rrmirNoT-Iw_pI/edit?usp=sharing
  5. The bold part is the sheet_id

Configuration

Example usage

extract:
    sheet_id: 1orAc9s3DWu5_G5m3UGxuf9ibMh2P7rrmirNoT-Iw_pI
    sheet_name: Sheet2
    range: A2:B1001

Properties

propertytyperequireddescription
sheet_idstringyesContains the id of the spreadsheet.
sheet_namestringnoName of the sheet you want to import. Use the exact name as seen in the Google Spreadsheet. If no sheet_name is provided, the first sheet will be imported.
rangestringnoRange of the data you want to import. Notation is like: A2:B1001. This would select a matrix in your sheet from top left A2 till bottom right B1001. If no range is provided all data from the sheet is imported.
conn_idstringnoConnection string as handed to you by the Onesecondbefore team. If your cloud is Google Cloud, the conn_id is not obliged. It will take the client_cloud.db_conn_id.
delegated_accountstringnoDelegated account to use to download the content of the Google Drive spreadsheet.

Details

itemdescription
APIGoogle Sheets API
Pre-formatted schemaNo. This from task does not come with a pre-formatted schema. Do not define file and only define schema if you use Snowflake. BigQuery can auto-detect the source file.