Getting Google Sheets Setup as an API
So you want to send some data to Google Sheets? Maybe you have a form you want to send to Google Sheets when it is submitted, or maybe you have an upload you want to send there. Either way, you’re in the right place. This is unfortunately not as quick as it could or should be, but it isn’t too bad. Let’s get started.
So you may already have a Google account and a Google Sheet setup. That’s great. Unfortunately Google makes you manage access to these things (for developer settings) from a different place: the Google Developer Console.
You’ll need to go to: https://console.developers.google.com/
Google Developer Console
Once you get in there (assuming you have a Google/Gmail account already), you should see a screen like this:
Google will most likely make an initial project for you, but if you want to create a new “project” you do it from the top left corner:
go to “Credentials” on the left sidebar:
Once there you can click “Create credentials”:
Click on “Service account key” from the dropdown:
This should bring you to a screen like this:
from the “Service account” dropdown choose “New service account” and your form should do a slight update to look like this now:
Fill in the “Service account name” with any name you’d like. I will use googlesheet-test. This will automatically fill in the field below called “Service account ID” as seen in the image below. I’ve blocked out the last half of that field, but you should see a string some random numbers and letters on your screen:
Make sure to copy-paste that “Service account ID” somewhere safe (dont post it online anywhere public!). You or your developer will need that ID number to connect with Google Sheets.
Keep the “Key type” option on JSON:
For “Role”, select “owner”. We’ve finished filling out this form so now click the blue “Create” button down at the bottom. This will trigger a download and a popup that looks like this:
Be sure to send the file that is downloaded to your developer (the file type ends in “json”).
Okay. We’re done with the Google Developer Console for now. Now lets go over to your Google Sheets account.
Create a new sheet or go into the sheet you want connected to your app. Now click the blue button in the top right corner marked “share”:
The share button will open up a popup form:
Under “People” you have the option to share this with somebody. This is where you are going to put in the Service account ID that we got from inside the Google Developer Console earlier. Remember this? From here:
That is why the Service account ID looks a little like an email. So we’re sharing our sheet with that Service account ID just like we’d share it with somebody else using their email address.
Once you’ve finished sharing the sheet, click done and the popup window should close.
THE LAST STEP
The last step is to look at the url for your Google Sheet that you want shared (the one you should currently have open). We need the “sheet ID” which is in the URL:
Now send the following three things to your developer:
- the sheet ID we just got from the URL
- the file we downloaded from the Google Developer Console with ‘.json’ at the end of the file name (json is the file’s type)
- The Service account ID (that thing that looked like an email that we shared our Google Sheet with)
THAT IS IT. YOU ARE DONE.
Nice job. Make sure to keep these three thing safe for your own records — and do not share them anywhere publicly online! They should be kept private.