gsheet_setup - jaybgager/tracertools GitHub Wiki

Introduction

Several of the functions in tracertools allow you to programmatically read from or write to Google Sheets. However, in order to do this you'll need to set up authentication credentials tied to a Google account. This is a requirement of the gspread Python package that tracertools uses to accomplish these tasks.

The tracertools package works with either of two authentication methods, OAuth Tokens or Google Service Accounts. The official guide for setting these up can be found here, but detailed instructions are given below for ease of access. It should be noted that a third method, API Keys, also exists, but is beyond the scope of the tracertools package and so is not covered here.

General Setup

Regardless of your chosen method, there are a few common steps to set up the initial authentication framework.

Google Account

First and foremost, you'll need a Google account. If you don't already have one, you can create one here. This isn't some special kind of account - if you use gmail or google drive it's just the account you use for those.

Making a Developer Project

Once you have an account, you'll need to set up a "Project" in the Google Developer Console that will generate, store, and check the credentials for your chosen authentication method.

One account can have multiple projects. This is in case you'd like to ensure certain scripts you're writing or spreadsheets you use are only accessible in a limited way, usually for security reasons. However, for most casual users you'll only need one project with one set of credentials you can use for everyday tasks.

  1. To make a new Project, make sure you're logged into your Google account and then go to the Developer Console.

  2. Click the Project Picker button in the top-left hand corner. The exact text of this button may vary depending on whether or not your account is already tied to any projects, but hovering over it will show the alt text Open project picker (Ctrl O).

  3. In the window that pops up, click the New Project button in the top-right corner.

  4. Give the project a name related to its purpose. For general use credentials, something like general_use would be a good choice. At this point you can also tie your project to an organization if you need to, but this guide won't cover that.

  5. Click the Create button below the name entry fields to make your project.

Enabling API Access

Now that you have a project, you have to tell Google which of their many programs you're going to use it with. Each program has what's called an Application Programming Interface (API). This is basically a series of commands Google (or any publisher) has created so that users like you can interact with their big, complicated programs in expected, controllable ways.

For our purposes, there are two APIs you'll need to enable for your project; Google Sheets (so your programs can communicate with your spreadsheets) and Google Drive (so your programs are able to read from and write to things in your cloud storage). To enable these, click the button labeled + Enable APIs and Services near the top of the Developer Console.

This should take you to another page called the API Library. Here you can search for Google Sheets API and Google Drive API. There will likely be several similar results for each search (e.g. Google Drive API also returns Google Drive Activity API, Google Drive MCP, and Drive Labels API), so be sure to select only the one you want.

Click on the panel for the API you want to enable. This should take you to a Product Details page. Click the Enable button to enable the API for your project. Do this for both the Google Sheets API and the Google Drive API.

Generating your Credentials

Navigate back to the main Developer Console page for your project. You should see an option in the left-hand sidebar that says Credentials with a little key icon. Click this to go to the Credentials page.

This should take you to a window with three headings: API Keys, OAuth 2.0 Client IDs, and Service Accounts. This is when you'll need to choose which authentication method is right for your purposes.

Choosing Service Account vs OAuth

The official guide linked above explains the use cases for each method, but in brief: Service Accounts are intended for long-term use by bots or developers, while OAuth tokens are intended for human end-users of your code, particularly where security is important.

Service accounts basically create a sort of "dummy" google account that's tied to your own. This account is then given whatever credentials you choose. Service accounts are useful because they have very long expiration periods, so you don't need to constantly refresh them, but you need to make sure you share any google sheet you want them to have access to the same way you would share it with a human collaborator.

OAuth tokens are independent of your Google account, but by default they expire after only 10 days and must be refreshed. This is because by default, your project will be set to "Testing" mode - intended for use during the development process. The only way to extend this time limit is by publishing and verifying your project as a public-facing app. Since that's not something you want to do with your general use credentials, OAuth tokens are really only a good idea if security is a high concern.

To use an analogy, OAuth Tokens work like the temporary keycards that hotels give guests for a short stay. They give a specific user access to a service, but they expire quickly to prevent unauthorized access. Service accounts are more like a social security card or a driver's license: long or no expiration period and very useful, but you need to keep a closer eye on them to make sure they don't get lost or stolen.

By default, the functions in tracertools assume you're using a service account stored in the default location. Any other use case requires extra arguments to be passed when calling gsheet_ functions.

Hopefully that's enough information to help you decide which is right for you. On to the setup.

Making a Service Account

  1. From the Credentials page of the Developer Console, click the + Create Credentials button near the top-left of the Credentials window.

  2. In the dropdown menu that appears, click Service Account. Choose a name, ID, and description that will help you remember the purpose of the service account.

  3. The name is just the name that shows up in the developer console above the account details. Something like General Service Account is probably a good idea.

  4. The ID is the actual username for the dummy account when you're giving it access to various spreadsheets or other files. Whatever you pick will automatically be appended with a bunch of other text, so something like general_sa will end up looking like this: general_sa@general_use-123456.iam.gserviceaccount.com (assuming you used general_use for your overall project name). This is the email address you share your files with if you want your service account to be able to modify them.

  5. The description really doesn't matter too much for our purposes, but can be used to add a detailed explanation of what a given account is for if you use multiple. Something like "general use account for basic tasks" is probably fine.

  6. If you'd like to set specific permissions for the account, you can click the Create and Continue button below the entry fields. For a general token, this shouldn't be necessary, so you can click the Create and close button.

  7. This should take you back to the Credentials screen. From here, click on the Manage service accounts just above the Service Accounts heading on the right side.

  8. Press the ⋮ icon under the Actions heading on the right side of the entry for your service account.

  9. In the dropdown menu that appears, click Manage keys.

  10. Click the ADD KEY button near the left, just above the key entries. Then, in the dropdown menu that appears, click Create new key.

  11. Choose JSON as the key type and click Create. Your browser will automatically download a JSON file in your computer's default Downloads folder. The contents should look something like this:

{
    "type": "service_account",
    "project_id": "api-project-XXX",
    "private_key_id": "2cd … ba4",
    "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n",
    "client_email": "[email protected]",
    "client_id": "473 … hd.apps.googleusercontent.com",
    ...
}
  1. Now you'll need to move this file to the location where the gspread package is going to look for your default credentials.

For Linux or Mac users, this will be ~/.config/gspread/service_account.json

For Windows users, this should be C:\Users\your_user_name\AppData\Roaming\gspread\service_account.json.

[!Note] Make sure you have hidden folders set to visible if you do this in a file explorer GUI, as both .config on Linux/Mac and Roaming on Windows are hidden by default. If the file isn't already named service_account.json name it that manually. Be sure not to add the file extension twice if your machine is set to automatically append file extensions (e.g you'll end up with a file called service_account.json.json, which won't work).

You're done! You should now be able to use all the gsheet_ functions in tracertools without passing any extra arguments. Be sure to share edit access for whatever spreadsheets you want to modify or read from with the email address of the service account. Otherwise, you'll get an error when trying to run your code.

Making an OAuth Token

Coming soon...

Using Custom Credential Locations

If, for some reason, you'd like to save your credentials somewhere other than the default location you can. You'll just need to be sure to pass that location whenever you use one of the gsheet_ functions.

For both service accounts and OAuth tokens, you'll need to pass the argument cred_path_override with a value of the absolute path to wherever you stored your credentials as a string.

If you're using an OAuth token, you'll also need to pass the absolute path to the authorized user file with the oauth_user_override argument as well.