Search code examples
pythonpygsheets

pygsheets - Using dotenv instead of json file

Connecting to gsheets: the usual way would be to run gc = pygsheets.authorize() with the creds.json file in the same root folder as the script gsheets.py (folder structure below)

├── creds.json
├── gsheets.py

Would like to use service_account_env_var instead of using a json file

The creds.json file looks something like this:

{
    "token": null, 
    "refresh_token": "token", 
    "id_token": null, 
    "token_uri": "token_uri", 
    "client_id": "client_id.apps.googleusercontent.com", 
    "client_secret": "secret"
}

How would I create a .env file with the credentials in the json file ?

As per the pygsheets docs: pygsheets - Environment variables

This assumes you have set an environment variable key GDRIVE_API_CREDENTIALS set with the value of the Service Account .json file described in the above Service Account section.

Unfortunately, there was no guidance as to how one would set the environment variable. I was thinking along the lines of:

Create the .env file (am really not sure how to express json in env file format so wrapping the individual key:value pairs in {}...) :

# .env
GDRIVE_API_CREDENTIALS = {
token = null
refresh_token = token
id_token = null
token_uri = token_uri
client_id = client_id.apps.googleusercontent.com
client_secret = secret }

Create a settings.py:

# settings.py

from dotenv import load_dotenv
load_dotenv()

import os
creds = os.getenv('GDRIVE_API_CREDENTIALS')

pygsheets.authorize(service_account_env_var = creds)

The end result would be successful connection when creds is supplied as an arg to service_account_env_var

Solution

  • If you look at the pygsheets code, you can see that its using json.loads. So in your case just store the whole json file as a string in an env variable.

    Also note that this options is for service file, your file look like an user credential.

    Edit: If you have completed auth flow, you can use the generated token file directly as a json and create credentials from that. There is no direct option for that, you will have to create credentials object from that. https://github.com/nithinmurali/pygsheets/blob/staging/pygsheets/authorization.py#L37 see this code for reference.

    PS. Traveling now, will update to include code sample.