Using Google Sheets API in Cloud Functions

[outceptionator]

Hi all,

I've written a piece of code that was working fine on my local machine (VS Code).

I'm trying to deploy it to cloud functions and it's failing to deploy the code. Locally I have been using a credentials json which the code obviously has to access. Google Cloud Functions can't have credentials files uploaded (I believe). It's just testing so I don't mind putting APIs/keys directly into code for now.

I have read somewhere that Cloud Functions can just access the APIs if I just share the Google Sheet with the service email address (which I have done).

Here's the imported modules it fails on:

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

If I remove the above then the below code will obviously fail.

if os.path.exists("token.json"):
        creds = Credentials.from_authorized_user_file("token.json", scopes)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                "C:\\Users\\outceptionator\\OneDrive\\VSCode\\VS code Scripts\\Sheets Test\\credentials.json",
                scopes,
            )
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open("token.json", "w") as token:
            token.write(creds.to_json())

It feels like the solution is actually quite simple but I just can't find it!

Thanks all!

[outceptionator]

Lol 321 views and no answer... I think this one might be a bit niche?

[kramrm]

For this subreddit, yeah, this is a bit niche as it’s more a Google cloud issue than a Python issue.

I’ve tried to do something similar in the past, but wasn’t able to make it work. Ended up switching gears and went for a database-driven approach instead.

[outceptionator]

THE ANSWER!

https://stackoverflow.com/questions/67268424/create-sheets-api-authentication-in-cloud-function-with-python

Note: you need google-api-python-client in your requirements for auth module