1

I have created a script on Lambda in Python, primarily using the library pygsheets, that manipulates Google Sheets when invoked. Project is up and working but an odd occurance has been noted. Locally, my project runs quickly (~15 sec) where as on the cloud it takes considerably longer at times (~3 min avg). I find this has to do with pygsheets waiting due to 429 HTTP errors from hitting the Google Sheets API to many times. Implementation of some sleep functionality has stymied this.

What is more concerning here is when this script runs locally it reads from the Google Sheet ~2x/tab + 2 times to connect to the Google Sheet itself. So, for example, in a sheet with 5 tabs it will request from the Google API about 12 times. However when I use the same script on Lambda the API at almost 10 times as much, spiking to 120 requests in one second at one point.

Any advice on what to check on Lambda that may be cause this? Funny enough, the project, which writes info to the sheets, stays steady at the exact amount of times needed. I vetted my project's python code/classes to store connections for later use rather than constantly pinging sheets when needed. At the moment I have the run time of my function down to about 30 seconds with sleep functions where needed but I fear this issue of high volume requests will cause issues for me later as I implement on larger sheets.

I would like to know of avenues/options/settings ect that I could possibly check on AWS/Lambda's side to fix this issue? Or is this an innate problem with using a cloud service?

Thanks!

About this chart: The low volume of requests at ~5:05 is a local run while the two other spikes are on Lambda.

enter image description here

eccadena
  • 171
  • 1
  • 8
  • I'm seeing the same problem. Have you ever figured out why or any workarounds? – Andy Li Jan 06 '21 at 17:05
  • Not directly but, a workaround... kinda. My workaround was to identify the calls being made by pygsheets and scrutinizing the level at which I used these calls, saving connections and not requesting/re-establishing them again... essentially not a source code fix (should probably open one on github) but drying out my code. It helped enough to get me under the limits. – eccadena Jan 06 '21 at 19:46
  • you can try the batching and get_values_batch to reduce api calls significantly. https://pygsheets.readthedocs.io/en/staging/worksheet.html#pygsheets.Worksheet.get_values_batch – Nithin Jan 06 '21 at 21:57
  • If you could single out which calls to pygsheets are getting amplified, i can take a look under the hood. – Nithin Jan 06 '21 at 21:59

0 Answers0