Adding rows on a spreadsheet exceeds 100 sec user quota

Question

I am writing a script in Python to add rows into a spreadsheet using gspread.

client = gspread.authorize(creds)
ws = client.open("my spreadsheet").sheet1
...

for xml in for xml in soup.findAll('items'):
  item = {
      ...
  }
  ws.append_row(item)

This work until I reach around 100 items and then it gives me an error

"error": {
"code": 429,
"message": "Insufficient tokens for quota 'WriteGroup' and limit 'USER-100s' of service 'sheets.googleapis.com' for consumer 'project_number:644051582230'.",
"status": "RESOURCE_EXHAUSTED"

Any ideas on how to write this in a different way to avoid that many requests or a way of not getting this quota limit error?

client = gspread.authorize(creds) ws = client.open("my spreadsheet").sheet1 — r.yaz, Sep 21 '18 at 16:24
Please include it as an edit. I have never been kicked off the API with gspread so I'm assuming you're hammering the API somehow. I used to read spreadsheets every 2 seconds 24/7 — roganjosh, Sep 21 '18 at 16:25
i edited my post, the append command is the only time i submit a request to the api, — r.yaz, Sep 21 '18 at 16:26
Why would you need to call the API that fast? Why don't you build the list and send a single query to update multiple rows? — roganjosh, Sep 21 '18 at 16:30
i thought about it but i couldn't find a way so instead of append_row i did something like: item_list.append(item) but didn't know how to push that through the api — r.yaz, Sep 21 '18 at 16:31
I gave such an answer [here](https://stackoverflow.com/questions/34640175/can-i-write-whole-lines-in-google-spreadsheets-using-gspread-in-python/34837122#34837122). I need to clean it up now I've got better at programming :) — roganjosh, Sep 21 '18 at 16:34
i see what you did but doesn't that also submit a request per row to add? — r.yaz, Sep 21 '18 at 16:38
No, a single request. And it will go a lot faster because the API is really slow, so every call is the major part of the time to actually write data. If you do in blocks of 100 rows, you only get 1 API call for all of the data — roganjosh, Sep 21 '18 at 16:39

score 0 · Accepted Answer · answered Sep 26 '18 at 14:19

column_names= ['','A','B','C','D','E','F','G','H']
cell_range = 'A1:' + str(column_names[len(items_list[0])]) + str(len(items_list))
cells = sheet.range(cell_range)
flattened_data = []

for x in items_list:
 for y in x:
  flattened_data.append(y)


for x in range(len(flattened_data)):
   cells[x].value = flattened_data[x].decode('utf-8')

sheet.insert_row(title, index=1)

That worked for me, thanks to roganshosh's comments to my question

Adding rows on a spreadsheet exceeds 100 sec user quota

1 Answers1

Linked