Google Sheets

Read and write a Google Sheet. Primarily used for the induction roster import and sync, which is a job schools already do in a spreadsheet.

Category	Spreadsheet
Authentication	Service account, optionally with delegation
Reaches	oauth2.googleapis.com, sheets.googleapis.com
Needs an agent	No
Demo mode	Yes

Capabilities

sheet.read, sheet.write

Creating the service account

The same flow as Google Workspace and Chrome Enterprise. Reuse an existing service account if you have one.

1. Google Cloud console, in a project for your school.
2. IAM and Admin, Service Accounts, Create service account. Name it plugboard.
3. Keys, Add key, Create new key, JSON. Download it.
4. Enable the Google Sheets API for the project under APIs and Services, Library.

Giving it access to the sheet

Two ways, and the first is simpler.

Share the sheet directly. Open the spreadsheet, click Share, and share it with the service account’s client_email address as an Editor. No delegation needed. Leave subject empty in the configuration.

Domain-wide delegation. If you would rather the service account act as a person, grant delegation as described for Chrome Enterprise with the scope https://www.googleapis.com/auth/spreadsheets, and set subject to that person’s address.

Sharing the sheet is less powerful and less to go wrong. Use it unless you have a reason not to.

Configuring it in Plugboard

Admin, Connectors, Google Sheets, Configure.

Settings

Field	Default	Value
spreadsheetId	empty	The id from the sheet’s URL
tab	Inductions	The worksheet or tab name
range	A1:Z1000	The A1 range within the tab
subject	empty	An optional delegated user to act as
demoMode	false	Return fixture data

The spreadsheet id is the long string in the URL between /d/ and /edit:

https://docs.google.com/spreadsheets/d/1AbC...XyZ/edit#gid=0
                                        ^^^^^^^^^^ this

Credentials

Field	Value
clientEmail	client_email from the downloaded JSON
privateKey	private_key from the JSON, including the BEGIN and END lines

Save and test.

Laying out the roster sheet

Keep the header row in the first row of the range. The columns that matter are the ones identifying a student and, if you are tracking it, the device they are being issued.

Practical advice from schools that do this every January:

• One tab per year, not one sheet per year. Change the tab setting and the history stays in the same file.
• Do not merge cells. A merged cell reads as a blank in every row but the first.
• Leave the range generous. A1:Z1000 covers a large year group and costs nothing.
• Freeze the header row so whoever is editing it during handout week does not scroll it away and start typing in the wrong column.

Why a spreadsheet at all

Because that is what schools use for this. A device handout list gets built in a spreadsheet by somebody in the office, edited by three people, and printed. Making Plugboard read that sheet is less work and less friction than making everybody change how they work in the busiest week of the year.

Once the roster is imported, the tracking happens in Plugboard. See inductions.

Troubleshooting

Symptom	Cause
403	The sheet is not shared with the service account, or delegation is not authorised
404	Wrong spreadsheet id. Check you copied the id and not the whole URL
“Unable to parse range”	The tab name does not match. It is case sensitive and spaces count
Empty result	The range does not cover the data, or the tab is empty
invalid_grant	The private key is malformed. Paste it complete
Sheets API not enabled	Enable it in the Cloud console for the project
Fixture rows appear	demoMode is still on