Google Sheets Integration

Features

Enabling the Google Sheets Integration in SchoolFi grants users the following features:

• An Export to Google Sheets button on any screen where there is an Export to Excel button.
  • In both the Employee Portal and the SchoolFi software.
• A Google sheets option when running most reports that have an Excel option.
• A Google sheets option when running any report writer report that supports Excel output.
• A Google sheets button on any Web Desk panel that has an Excel button
  • In both the Employee Portal and the SchoolFi software.

How it works

SchoolFi uses a Service Account in Google Cloud to create Google Sheets documents on behalf of users in the district's Google Workspace.

Create the SchoolFi Sheets Integration Cloud Project

1. Log into the district's Google Cloud Console.  https://cloud.google.com
2. Create a new project for "SchoolFi Sheets Integration"
3. Go to the APIs & Services section
4. Go to Library
5. Seach for "Sheets"
6. Click the Sheets Icon in the search results and enable it.

Creating the Cloud Service Account

1. Click on the project at the top of the screen, and click on it again in the popup to go to it's home page.
2. Click on IAM & Admin
3. Click on Service Accounts in the left link bar
4. Click Create Service account at the top of the screen
5. Enter a name, for example : schoolfi-sheets-integration
6. Click Create and continue
7. In the permissions section. Choose under Quick Access: Basic -> Editor
8. Click Continue
9. Click Done
10. If not already, click on the Service Accounts in the left bar
11. Click on the link to the service account
12. Click on the keys tab
13. Click on the Add Key -> Create new Key command
14. Choose JSON and click Create.  Save it to your Windows / Apple desktop. 
15. Copy the text in the OAuth 2 Client Id Column to a clipboard or notepad document

Connect the Cloud Service account to Google Workspace

In another tab in the browser, open up the Workspace Console (Admin Console) https://workspace.google.com/

In the Workspace navigation on the left side, go to Security -> Access and Data Control -> API Controls

There is a 'notecard' on the screen called "Domain wide delegation".  Click on the hyperlink "MANAGE DOMAIN WIDE DELEGATION"

click the "Add new" hyperlink

Paste in the OAuth 2 Client Id that we put in our clipboard / notepad document earlier from the Google Cloud Project.

Copy and paste this text into the OAuth Scopes (comma-delimited) field

https://www.googleapis.com/auth/spreadsheets,https://www.googleapis.com/auth/drive

Configure SchoolFi to connect to the Google Cloud and Workspace

In SchoolFi as a System Administrator,

1. Go to Setup -> Google -> Workspace Integration
2. Put the email domain of your users in the setup screen and click save.  This will be the @domain.com of the users in your Google Workspace -> Directory-> Users screen.
3. Go to Setup -> Google -> Sheets
4. Click on the Upload JSON Key button, and upload the .JSON file that was downloaded earlier.  It should be on your desktop.
5. Enable the integration by checking the Enabled checkbox and click Save.

To globally disable the sheets icon from all screens, uncheck the enabled button and click save from this screen.