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.
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
- 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_KEYor the value for key
GOOGLE_SERVICE_ACC_KEY_FILEPATH(both are not simultaneously required. If you use
GOOGLE_SERVICE_ACC_KEY_FILEPATH, please delete
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"
We have two templates for using Google Sheets with Mage. They can be found in Python “Data Loader” and “Data Exporter” templates.