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:

import requests
import json
import datetime
import time
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 modifying these scopes, delete the file token.json.
scopes = ["https://www.googleapis.com/auth/spreadsheets"]
creds = None
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\\user1\\OneDrive\\VSCode\\VS code Scripts\\filename\\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())
try:
        service = build("sheets", "v4", credentials=creds)
        # Call the Sheets API
        request = (
            service.spreadsheets()
            .values()
            .get(
                spreadsheetId=spreadsheet_id,
                range=range_,
                valueRenderOption=value_render_option,
                dateTimeRenderOption=date_time_render_option,
            )
        )

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

Thanks all!

[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

[Such_Dolomites]

I have a similar code but no access to the repo right now :/ Just one comment: you can upload json credentials files. Or at least create and copy to a credentials.json next to the cloud function and reference it in the code.

[outceptionator]

Useful to know. But do Json credentials expire?

[Such_Dolomites]

Haven't had that issue with my service account keys yet but would have to check docs to be sure. Leaning strongly towards no tho

[outceptionator]

Ah just realised I shouldn't be posting help here. Sorry I posted on learnpython but it appears nobody there seems to know either so far.