Google Sheets API works locally but timeout on connection when running from AWS Lambda

Question

I have some code that works perfectly locally and doesn't work at all from AWS Lambda. It's almost like the API is blocked and I'm not sure what to look for next.

I can hit other things "out on the net" so it's not a generic routing issue and I get a socket timeout error from the AWS Run.

I've tried a few different libraries including an older version of the main library. Everyone of them works locally, doesn't work from AWS.

#!/usr/bin/env python3

# replace
creds_file = "/path/to/creds.json"

import pickle
import os.path
from googleapiclient.discovery import build
from google.oauth2 import service_account

scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly']

SAMPLE_SPREADSHEET_ID = "<spreadsheetid>"
# Sample Range
SAMPLE_RANGE_NAME = "Sheet1!A1:D"

creds = None

# The file token.pickle stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.

if os.path.exists('/tmp/token.pickle'):
    with open('token.pickle', 'rb') as token:
        creds = pickle.load(token)
# 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:
        # Customized
        creds = service_account.Credentials.from_service_account_file(creds_file)
        creds_w_scopes = creds.with_scopes(SCOPES)

    # Save the credentials for the next run
    with open('/tmp/token.pickle', 'wb') as token:
        pickle.dump(creds_w_scopes, token)

# Timeout is Here in the Cloud
service = build('sheets', 'v4', credentials=creds_w_scopes)

# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                            range=SAMPLE_RANGE_NAME).execute()
values = result.get('values', [])

print(values)

Locally I get the results of the sheet (just some smample data) in the cloud though it hangs on this call

service = build('sheets', 'v4', credentials=creds)

And then times out with a socket.timeout error.

I have same problem. I thought it's about security groups and I gave permission but it's not working. Can you find a solution? — Umut ADALI
– Umut ADALI, Commented Sep 11, 2021 at 13:39

Matthias S · Accepted Answer · 2021-10-05 17:04:10Z

2

I had the exact same issue today and after quite some time I figured out that for me the problem was lack of memory size for the lambdas.

If you look into CloudWatch you can see how much RAM (memory) your Lambda has available and how much it is using. If you see the usage being equal to the maximum RAM available, you should probably increase the available RAM (or make your code more efficient).

In our case, simply increasing RAM from 128MB to 384MB resolved the 60 second timeouts and made the lambda run in a few seconds instead.

answered Oct 5, 2021 at 17:04

Matthias S

3,5834 gold badges26 silver badges33 bronze badges

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

Comments

Timofey Nikolaev · Accepted Answer · 2023-02-07 15:02:07Z

0

This happens because you have ipv4 stack locally and ipv6 stack at AWS. I found one solution that helps, and it's socket patching:

import socket

old_getaddrinfo = socket.getaddrinfo
def new_getaddrinfo(*args, **kwargs):
    responses = old_getaddrinfo(*args, **kwargs)
    return [response
            for response in responses
            if response[0] == socket.AF_INET]
socket.getaddrinfo = new_getaddrinfo

Note that it should be called before importing googleapiclient.discovery and google.oauth2 and can be troublemaker for other libraries in your code. I ended up with moving these calls to python subprocess with patched socket library.

answered Feb 7, 2023 at 15:02

Timofey Nikolaev

214 bronze badges

Collectives™ on Stack Overflow

Google Sheets API works locally but timeout on connection when running from AWS Lambda

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related