Problem & Context
The MBPY utility is capable to updating Google Spreadsheets with ManageBac data. However, MBPY is a command line tool that executes on a server, how does it communicate with Google, and how to set it up?
Creating a Service Account
A service account is a resource within the Google Cloud Platform, created by a user with Administrator privileges. This account then becomes the target principle that MBPY will use to authenticate with the Google Platform. By sharing a spreadsheet with the service account, MBPY will then have access rights to that particular document.
There are the steps required to create the service account:
- Navigate to the GCP dashboard
- Create a project, for example called "MBPY project". A project is the container for settings in the GCP, and will become the parent container for the service account that will be created in the next steps.
- Help on creating and managing projects is available via GCP help docs.
- Then find the "IAM" (Identity Access Management) area, and navigate to that area within the project
- Then find the "Service Accounts" section of the IAM area of the project.
- Click "Create Service Account"
- Google help file for creating service accounts is here.
- Fill out the service account name, ID, and description as desired
- Granting access to project or granting users to the service account is optional
- Download the credentials to authenticate with the service account
- Click on the three dots of the service account, and select "Manage Keys"
- Click "Add Key"
- "Create new key"
- Key type: JSON
- The credentials will download to your computer
- This is the credentials file
Enable Google Sheets API
The following steps are required to give permission for the service account to be able to use the Google Spreadsheet APIs. Please note that this does not give permission for all spreadsheets on the domain, it simply enables the ability to use the needed APIs.
Follow these steps to allow the service account to access Google Spreadsheet APIs:
- With the project created in the previous step active, navigate to "APIs & Services"
- Click on "Enabled APIs & Services"
- Click "Enable APIs and Services"
- Type "Sheets" in the search bar
- Enable the "Google Sheets API"
Share spreadsheet with the service account
MBPY will populate a blank spreadsheet, which you can create yourself, and then share with the service account. These are the steps to activate the APIs for the service account:
- Navigate to the "IAM" and "Service Accounts"
- In the list of service accounts, there is an email address corresponding to the service account created that ends with `gserviceaccount.com`.
- Share the spreadsheet with this email address (with edit rights), in exactly the same way you would share with an individual.
Done! Now the service account is able to keep the spreadsheet updated.
Configure MBPY to use credentials
If you are using MBPY directly, then please follow these instructions:
- Place the downloaded files into a known location to you.
- Inspect the help file for the gspread loader: `mbpy extract - gspread --help` which contains the names of the environment variables that need to be set, specifically `MBPY_GSPREAD_SERVICE_ACCOUNT_PATH`.
- Set these variables in the configuration environment file.