Connect Google Sheets to LessonQ

To send your records to Google Sheets, LessonQ writes as a service account from your own Google Cloud project. Here you hand LessonQ that account’s key. Once it’s connected, you share spreadsheets with the account’s address, and LessonQ can write to those spreadsheets and nothing else.

In this video · 1:58

1. 0:00Connect service account
2. 0:19The wrong file
3. 0:28Your key
4. 1:04The share address
5. 1:14The service account

Read the narration

1. 0:05Now give LessonQ the key you downloaded from Google.
2. 0:09In Google Sheets, click Connect service account.
3. 0:13Upload the file, drop it anywhere on this window, or paste what is in it.
4. 0:19First, a common mistake: an OAuth client file instead of a service account key.
5. 0:26LessonQ says so at once, and saves nothing. Click Clear.
6. 0:31Now the right file. Click Upload .json file, and pick the key you downloaded.
7. 0:37Each check turns green: valid JSON, a service account key, its email, and a complete private key.
8. 0:45You also see its Project, and its Share address.
9. 0:49The key itself stays folded away. LessonQ stores it encrypted, and never shows it again.
10. 0:56Click Test & connect. Nothing is saved until Google accepts the key.
11. 1:02Google accepted it. The panel now says Connected.
12. 1:06This is the share address. Every spreadsheet you want filled is shared with it.
13. 1:12Click it to copy the address.
14. 1:15Click Service account to see the connection.
15. 1:18Its status, and the day Google last checked the key.
16. 1:23Choosing sheets says how you add a spreadsheet: picked from Google Drive, or by its link when the Drive API is off.
17. 1:31Replace key puts in a new key. If it belongs to another service account, share your sheets with its address too.
18. 1:39Disconnect removes the key and every sheet. Your spreadsheets and their rows stay as they are.
19. 1:48More guides are at help.lessonq.com.

Before you start

• Who can do this: the academy owner and admins.
• You need the key file you downloaded from Google Cloud: a .json file whose name starts with your project’s name. If you don’t have one yet, follow Get your Google key for Google Sheets first.
• One account per academy. Every sheet the academy connects uses the same service account.

Connect the key

In the sidebar, open Settings → Google Sheets. It says Not connected.

1. Connect service account. The same button is in the setup card below.

1. Click Connect service account.
2. Give it the key, in any of three ways: Upload .json file, drop the file anywhere on the window, or copy what’s in the file and Paste from clipboard.
3. Look at What we check on the right: every line should be ticked.
4. Click Test & connect. LessonQ asks Google to accept the key, and saves nothing until it does.

1. Upload .json file.
2. Paste from clipboard.

When it’s the right file

1. Project: the Google Cloud project the key belongs to.
2. Share address: the service account’s email. You’ll share spreadsheets with it.
3. Show key contents. The key itself stays folded away. LessonQ stores it encrypted and never shows it again.
4. What we check: valid JSON, a service account key (not an OAuth client), its email, and a complete private key. The last line, Google accepts the key, is checked when you connect.
5. Test & connect.

When it’s the wrong file

The usual mistake is an OAuth client file instead of a service account key. Google offers both, and their files look alike. LessonQ spots it at once and saves nothing.

1. Problem: what’s wrong with the file.
2. Clear, to try another file.

What LessonQ says	What it means
This is an OAuth client secret, not a service account key.	You downloaded the wrong kind of file. Make a key from the service account’s Keys tab instead.
This isn’t valid JSON.	Part of the file is missing. Upload the file itself, or copy all of it, braces included.
This JSON is not a service account key.	It’s some other JSON file.
The private_key looks incomplete.	Only part of the file was pasted. Upload the file instead.

The share address

Once Google accepts the key, the panel says Connected, and the service account’s address sits at the top.

1. Connected.
2. The share address, with a button to copy it. Every spreadsheet you want LessonQ to fill is shared with this address, as an Editor.
3. Service account: the connection’s details.

The service account window

Click Service account to see the connection.

1. Status, and the day Google last checked the key.
2. Share address, with Copy.
3. Choosing sheets: Pick from Google Drive when the Drive API is on in your project; otherwise you add a spreadsheet by pasting its link.
4. Replace key: put in a new key. If it belongs to another service account, share your spreadsheets with the new address too, or they stop updating.
5. Disconnect: removes the key and every sheet from LessonQ. Your spreadsheets and the rows already in them stay as they are.

Note

Keeping the key safe. Anyone who has the key file can write to the spreadsheets you shared with it. Keep the file like a password. If it’s ever lost, delete the key in Google Cloud (the service account’s Keys tab) and give LessonQ a new one with Replace key.

Next, share a spreadsheet and choose what goes into it: Add a sheet.