Memcached Question posted in
A decent usage manual can be found here.

Memcached – Unable to read/write to Google Sheets from a deployed Cloud Function using google.auth.default credentials

I want to use a Google Cloud Function to read/write to a Google Sheet using the Google Drive API. I’ve tried deploying the code below as a Cloud Function (triggered by a Pub/Sub):

def hello_pubsub(event, context):
    from googleapiclient.discovery import build
    import google.auth

    credentials, project_id = google.auth.default(scopes=['https://www.googleapis.com/auth/spreadsheets'])

    service = build('sheets', 'v4', credentials=credentials)
    spreadsheet_id = 'my_spreadsheet_id_goes_here'

    # Call the Sheets API
    sheet = service.spreadsheets()
    result = sheet.values().get(spreadsheetId=spreadsheet_id,range="A1:B4").execute()
    values = result.get('values', [])
    for value in values:
        print(value)

    range_ = 'A7'
    request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_, valueInputOption='RAW', insertDataOption='OVERWRITE', body={"values":[['test cell input']]})
    response = request.execute()
    print(response)

if __name__ == '__main__':
    hello_pubsub('a', 'b')

I’m getting several errors in Google Cloud Platform > Logging:
file_cache is unavailable when using oauth2client >= 4.0.0
File “/env/local/lib/python3.7/site-packages/google_api_python_client-1.8.0-py3.7.egg/googleapiclient/discovery_cache/init.py”, line 36, in autodetect
from google.appengine.api import memcache ModuleNotFoundError: No module named ‘google.appengine’

I can run this same script locally from Terminal and it works perfectly.

Any help or ideas are appreciated. Again, I want to read/write to a Google Sheet from a Cloud Function. It is my own Google Sheet run through the same Google Cloud account.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    To answer my own question, I was deploying the Cloud Function wrong. To have it work correctly, I had to put my function main.py, requirements.txt and my service_account.json in the same folder, then deploy it from Terminal.

    There are two ways I found to read/write to a sheet:

    # METHOD 1 -- using googleapiclient.discovery.build
def hello_pubsub(event, context):
    from googleapiclient.discovery import build
    import google.auth

    credentials, project_id = google.auth.default(scopes=['https://www.googleapis.com/auth/spreadsheets'])
    service = build('sheets', 'v4', credentials=None)
    spreadsheet_id = 'redacted'
    # Call the Sheets API
    sheet = service.spreadsheets()
    result = sheet.values().get(spreadsheetId=spreadsheet_id,range="A1:B4").execute()
    values = result.get('values', [])
    # Print the values in the sheet in range A1:B4
    for value in values:
        print(value)

    # Insert values into the sheet in cell A7
    range_ = 'A7'
    request = service.spreadsheets().values().append(
        spreadsheetId=spreadsheet_id, 
        range=range_, 
        valueInputOption='RAW', 
        insertDataOption='OVERWRITE', 
        body={"values":[['This value gets inserted into the cell A7']]})
    response = request.execute()
    print(response)

# METHOD 2 -- using gspread (I prefer this because it's cleaner & does all I need)
import gspread
def hello_pubsub(event, context):
    credentials_filepath = 'default_app_engine_service_account_key.json'
    gc = gspread.service_account(filename=credentials_filepath)
    sh = gc.open("the-title-of-your-sheet-goes-here")
    # Print all values in the sheet
    print(sh.sheet1.get_all_records())
    sh.sheet1.append_row(['This value gets appended to the last row in the sheet'], 
                        value_input_option='RAW', 
                        insert_data_option=None, 
                        table_range=None)

    Note: deploy the main.py with:

    gcloud functions deploy hello_pubsub --runtime python37 --trigger-topic test-topic

    and don't forget your requirements.txt file


  2. 0

    I think you have the dependencies installed globally and you use them. But when you deploy on Cloud Function, your local context is lost and the required library are missing.

    Add a requirements.txt file in the root directory of your function with this content

    google-api-python-client==1.8.2
google-auth==1.14.1

    And deploy again.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search