Google Sheets
Mage supports writing/reading from Google Sheets using the Google Sheets API via the gspread library. Read on to learn about reading/writing data to Sheets.
Configuration
To configure Google Sheets with Mage, you’ll need to enable several APIs in your GCP project and add credentials to Mage.
Enable relevant APIs
Using Google Sheets in Mage requires the following to be enabled via GCP:
- Google Drive API
- Google Sheets API
Additionaly, the Google Sheet must be shared with the service account used in Mage.
You can follow this guide to enable relevant services.
Add credentials
Adding credentials for Sheets is the same as adding credentials for other Google services, i.e. BigQuery, GCP.
Before you begin, you’ll need to create a service account key. Please read Google Cloud’s documentation on how to create that.
Once your finished, following these steps:
- Create a new pipeline or open an existing pipeline.
- Expand the left side of your screen to view the file browser.
- Scroll down and click on a file named
io_config.yaml
. - Enter the following keys and values under the key named
default
(you can have multiple profiles, add it under whichever is relevant to you) - Note: you only need to add the keys under
GOOGLE_SERVICE_ACC_KEY
or the value for keyGOOGLE_SERVICE_ACC_KEY_FILEPATH
(both are not simultaneously required. If you useGOOGLE_SERVICE_ACC_KEY_FILEPATH
, please deleteGOOGLE_SERVICE_ACC_KEY
in theio_config.yaml
).
version: 0.1.1
default:
GOOGLE_SERVICE_ACC_KEY:
type: service_account
project_id: project-id
private_key_id: key-id
private_key:
"-----BEGIN PRIVATE KEY-----\nyour_private_key\n-----END_PRIVATE_KEY"
client_email: your_service_account_email
auth_uri: "https://accounts.google.com/o/oauth2/auth"
token_uri: "https://accounts.google.com/o/oauth2/token"
auth_provider_x509_cert_url: "https://www.googleapis.com/oauth2/v1/certs"
client_x509_cert_url: "https://www.googleapis.com/robot/v1/metadata/x509/your_service_account_email"
GOOGLE_SERVICE_ACC_KEY_FILEPATH: "/path/to/your/service/account/key.json"
Usage
We have two templates for using Google Sheets with Mage. They can be found in Python “Data Loader” and “Data Exporter” templates.
You can see the source code for the templates here and here.
Was this page helpful?