Does this risk me to exceed Google spreadsheet API calls quota?

0
votes

I am using google spreadsheet to collaborate on some common data, and processing this data using spreadsheet macros and saving it there itself in cells. This approach is error prone as the macro functions which are processing the data depend on inputs apart from what is given as parameters.

Example of how the common data-looks

Content Sheet enter image description here

Sections Sheet enter image description here

Pages Sheet enter image description here Explanation of this common data

  1. The three sheets are filled by various collaborators
  2. Content sheet defines base elements of a page, they are referred (using their UUIDs) in the sections sheet (using macros), and finally all sections add together to make publishable html page.
  3. The output html format varies depending upon the destination which are multiple - static html, Google Document, Google Slides, Word-press, MediaWiki etc. The formatting is done using GAS macros.

I tried various solutions, but nothing is working properly. Finally I have thought to keep the google spreadsheet as a data source only and move the entire computation to my local machine. This means I have to download the sheets data (3 sheets) which are some 1,000 rows, but can become voluminous with time.

Earlier when the entire computation was on google spreadsheets, I had to only fetch the final data and use it, which amounted to a lot fewer APIs calls. Referring to the example above, It means I would only fetch the output html of the "Pages sheet".

Q1) So my question is, given that I plan to move the entire computation to local machine, if I make only 3 APIs calls to bulk fetch data of three sheets, and pull the entire data, does it still counts as just 3 API calls, or big requests have a different API cost? Are there any hidden risks of over-running the quota in this approach?

Q2) And let's say i use hooks, both spreadsheet hooks and drive hooks to sync with real-time changes, what are my risks of running out of quota. Does each hook call constitute a API call. Any general advice on best practices for doing this?

Thanks

1
google-apps-scriptgoogle-sheetsgoogle-sheets-api
You have been viewed 16 times and you have no answers. I would recommend that you focus on you main question more quickly and use some simple code examples and/or graphics and images to explain your issue more quickly. Yes we who volunteer also have our own stuff to do so we tend to pick the easy ones first. So making your question more clear and focused helps you to get our attention more easily. When you were asking the question the site was providing you with the opportunity to read things that would help you to make a better question. Perhaps you should read some of it. – Cooper
Thanks @Cooper . It is still verbose, but I think it is more clear now. If you have knowledge on this subject, would you mind having a look. Thanks again. – Ivan Velikanova
what is the source of the common data? – Cooper
My opinion about your first three paragraphs is that while you are describing past problems you have completed omitted any detailed description if what you are doing. No past code, no explanation of what the common data is. So it's rather difficult for us to give you a specific answer because we still don't really know what you trying to do and all we can provide is opinions which puts the question clearly outside the bounds of this forum. So my advice is to eliminate any though of the first three paragraphs and just do one of your two alternatives and return more focused questions. – Cooper
@Cooper Thanks for your patience, I added screenshots of sample data, and some explanation. I not able to reduce the first 3 paragraphs. – Ivan Velikanova

1 Answers

0
votes

As per the documentation says by default Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user unless you change it on your project.

A1) If you make a single request it counts as 1 request no matter how large the data is, for that reason I'd make a single request in order to GET the entire Spreadsheet rather than making 3 API calls and then make the whole process you mentioned before.

A2) If you've considered using Push Notifications keep in mind that you will need some extra configuration such as a domain.

As a workaround I recommend you to use Drive File Stream

Since you want move the computation to your local machine, this option would be better but will need some changes. For example, you could change the output to CSV in order to better handle the data and it still will be a Google Sheets valid file type and then host your app in the same machine or make your local machine accessible.