Creating a GCP Service Account for MBPY

Problem & Context

The MBPY utility is capable to populated Google Spreadsheets and Google BigQuery with ManageBac data. However the MBPY utility needs to authenticate with the school's domain it communicate with Google Cloud Platform.

The following instructions demonstrate how to connect MBPY to your school's Google Cloud Platform.

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:

1. Navigate to the GCP dashboard
2. 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.
   
   1. Help on creating and managing projects is available via GCP help docs.
3. Then find the "IAM" (Identity Access Management) area, and navigate to that area within the project
4. Then find the "Service Accounts" section of the IAM area of the project.
5. Click "Create Service Account"
   1. Google help file for creating service accounts is here.
   2. Fill out the service account name, ID, and description as desired
   3. Granting access to project or granting users to the service account is optional
6. Download the credentials to authenticate with the service account
   1. Click on the three dots of the service account, and select "Manage Keys"
   2. Click "Add Key"
   3. "Create new key"
   4. Key type: JSON
   5. The credentials will download to your computer
   6. This is the credentials file 
7. 1. Go to IAM page in the Google Cloud (make sure you are on the project created for MB)
   2. Click "Grant Access" & add the service account you created earlier as 'New principals' and give it an 'Owner' role
   3. Finally, go back to the BQ, create a dataset for MB, if not already, and 'Copy ID' and include this ID in an email with the credentials fille

This credentials file can be sent security to Schools Technology official, preferably via encrypted email.

For Spreadsheet Services

If this service account will be used to populate a Google Spreadsheet, please follow these additional instructions.

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:

1. With the project created in the previous step active, navigate to "APIs & Services"
2. Click on "Enabled APIs & Services"
3. Click "Enable APIs and Services"
4. Type "Sheets" in the search bar
5. 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:

1. Navigate to the "IAM" and "Service Accounts"
2. In the list of service accounts, there is an email address corresponding to the service account created that ends with `gserviceaccount.com`.
3. Share the spreadsheet with this email address (with edit rights), in exactly the same way you would share with an individual.

Now the service account can be used to keep the spreadsheet updated.

For BigQuery Services

The service account needs to have the following permissions associated to it:

• All of the permissions of BigQuery Data Editor, plus
• bigquery.jobs.create permission, which can be granted by being made the owner

Go to IAM page in the Google Cloud (make sure you are on the project created for MB), click on 'Grant Access' & add the service account you created earlier as 'New principals' and give it an 'Owner' role. Finally, go back to the BQ, create a dataset for MB, if not already, and right-click on the three dots and find the "Copy ID", and provide to Schools Technology.

Configure MBPY to use credentials

You only need to follow the below instructions if you are setting up MBPY yourself. If you are using Schools Technology provided services instead, simply send the credentials file to the Faria employee  assisting you.

1. Place the downloaded files into a known location to you.
2. 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`.
3. Set these variables in the configuration environment file.