Read/Write Google Sheets from within a Cloud Function

Question

I am trying to call the Sheets API from within a google cloud function in order to read from Google Sheets and Write to Google Sheets. My credentials to access are stored within a google storage bucket called creds_bucket in a file called my_creds.json. The two spreadsheets I'm using both have the 'my_creds' email linked to them and all the relevant APIs have bee enabled for project.

Here is my code.... I get a KEY error that says it doesn't recognise the creds_bucket . What would be driving this?

import httplib2
from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials
import os
import json
from google.cloud import storage

def main(data, context):

    blob = storage.Client().get_bucket(os.environ['creds_bucket']).get_blob('my_creds.json').download_as_string()

    parsed_json_creds = json.loads(blob)

    scope = ['https://www.googleapis.com/auth/drive','https://www.googleapis.com/auth/spreadsheets']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(parsed_json_creds, scope)

    service = build('sheets', 'v4', http=credentials.authorize(httplib2.Http()))

    spreadsheet_id_input = 'spreadsheetinput_ID'
    range_input = 'Sheet1!A1:D10'

    spreadsheet_id_ouput = 'SPREADHEET_Output_ID'
    range_ouput = 'Sheet1!A1:D10'

    # pull data from input google sheet
    response = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id_input, range=range_input).execute()
    # export pulled data from input sheet to output sheet
    request2 = service.spreadsheets().values().update(spreadsheetId=spreadsheet_id_ouput, range=range_ouput, body=response)
    response2 = request2.execute()

  File "/env/local/lib/python3.7/site-packages/google/cloud/functions/worker.py", line 383, in run_background_function
    _function_handler.invoke_user_function(event_object)
  File "/env/local/lib/python3.7/site-packages/google/cloud/functions/worker.py", line 217, in invoke_user_function
    return call_user_function(request_or_event)
  File "/env/local/lib/python3.7/site-packages/google/cloud/functions/worker.py", line 214, in call_user_function
    event_context.Context(**request_or_event.context))
  File "/user_code/main.py", line 10, in main
    blob = storage.Client().get_bucket(os.environ['creds_bucket']).get_blob('my_creds.json').download_as_string()
  File "/env/lib/python3.7/os.py", line 678, in __getitem__
    raise KeyError(key) from None
KeyError: 'creds_bucket'

mgoya · Accepted Answer · 2019-07-05 14:32:09Z

1

The KeyError error you are getting is caused by the os.environ function not finding any environment variable called “creds_bucket”.

To fix it try attaching an environment variable with that name to the function, to do that you can:

Edit the function in the Console and adding a variable in the “Environment” section

Or

Redeploy the functions using the gcloud command with the “--update-env-vars” flag

answered Jul 5, 2019 at 14:32

mgoya

5323 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jwlon81 Over a year ago

I'm purposely trying to avoid using the environment variable option as it poses a security risk to what I'm trying to do. I read that storing creds in a GCS bucket is a good alternative here due to it's ability to encrypt this sensitive data at rest. An additional annoyance here is that Google's Cloud KMS doesn't work with Cloud Functions so it looks like my options are limited to using GCS.

Mayeru Over a year ago

The answer from @mgoya would be the proper one in this case, you are indeed facing that error because you have not attached any environment variable. However, why would you want to look into an environment variable? to access your bucket you just need to specify the name of your bucket (creds_bucket), remove the "os.environ" part and it should work as you want.

Collectives™ on Stack Overflow

Read/Write Google Sheets from within a Cloud Function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related