Google API Quota Limits

Background

If you notice a red Error lozenge on the Published Sheets screen, or notice that issue data changes in Jira are not reflected in your realtime update sheets, your Google Service Account may be hitting Google API quota limits. While the Google APIs used by this add-on have no daily usage limits, they do have default time-based read and write limits that can be exceeded by larger Jira instances with a higher number of users or published sheets. The default limits are 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Because this add-on's requests are all performed by a single user, the 100 requests per 100 seconds per user limit is more often the problem.

Investigation

To determine whether one or both of the APIs used by this add-on are hitting usage limits, perform the following steps:

1. Open the console at https://console.cloud.google.com/
2. Select the appropriate project from the drop down in the top left (if not selected by default)
3. In the dashboard that is displayed, locate the APIs tile in the top middle of the screen, and click on Go to APIs overview
4. The APIs Overview will list the Google Drive API and Google Sheets API, and show errors graphs and individual error ratios.
   
5. Examine each error ratio listed at the bottom of the dashboard. Error ratios that are less than or equal to 1% are not worth investigating. If an error ratio is greater than 1%, click on that API's name to load the API's overview. Perform the following steps for each API with an error ratio greater than 1%.
   
   
   
6. Click on the Metrics item on the left of the Overview screen to load the API's metrics.
   
   
   
7. On the Metrics screen, you will see a Traffic by response code graph, Errors by API method graph, and other graphs. If the Traffic by response code graph lists response codes 429 or 503, there is a quota problem. When this happens it is also likely that you will see a higher percentage of errors associated with google.apps.sheets.v4.SpreadsheetsService.BatchGetValues method in the Errors by API method graph.

Resolution

If, after following the investigation steps outlined above, it appears that your Google Service Account user is hitting API quota limits, perform the following steps to determine an appropriate higher limit and request a higher limit.

1. From the API Overview, click on the Quotas menu item on the left. This screen allows you to drill down in to the API requests.
   
   
   
2. Since read and write requests per day are unlimited, the default graphs are not very helpful. Switch the Read requests graph to read requests per 100 seconds by changing the drop down value at the top left of the graph, and change the time range to 6h by selecting the time range from the options at the top right of the graph.
   
   
   
3. If you notice any spikes above 100 in the resulting graph, you will need to increase the quota. Perform the same steps outlined above on the Write requests graph and look for spikes there as well.
4. After examining each of these graphs, click on the pencil icon next to the Read requests per 100 seconds, Read requests per 100 seconds per user, Write requests per 100 seconds, and/or Write requets per 100 seconds per user to set higher quotas. Use the peaks of the graphs from steps 2 and 3 to help you determine what an appropriate quota might be.
   
   
   If the quotas are already at their maximum, you will need to request a higher quota from Google by clicking on the apply for a higher quota link in the dialog, and filling out the form.
   
   

Occasionally, Google may request additional information regarding the use of their APIs. See below for sample questions and responses.

1. Which API methods are being called and what is the frequency?

   1. The following methods are being called in the Drive API (in no particular order):

      1. drives.list

      2. files.get

      3. files.list

      4. files.update

      5. files.delete

      6. permissions.create

      7. permissions.list

      8. permissions.delete
         
         

   2. The following methods are being called in the Sheets API (in no particular order):

      1. spreadsheets.values.append

      2. spreadsheets.values.update

      3. spreadsheets.values.batchget

      4. spreadsheets.batchupdate

      5. spreadsheets.get
         
         

   3. The frequency of these calls will depend on your Jira instance's use. You should be able to get a general idea from the Google Project Dashboard for each API. Most likely you are seeing a majority of the requests going to spreadsheets.values.batchget, spreadsheets.values.append, and spreadsheets.values.update.
      
      

2. The API provides batch APIs which allows for reading or updating multiple ranges of a spreadsheet in a single API request to use less quota. Is the application taking advantage of these batch APIs?

   1. Yes, the application is using these APIs whenever possible.
      
      

3. Could you describe the application’s request pattern including, the type of user making the requests (end users vs. service accounts)?

   1. Requests are made by a single service account.
      
      

   2. When Jira issue data is changed the following occurs (in order):

      1. We make a call to drive.files.list to find the realtime-update sheets. This is based on information stored in the file’s appProperties. This response is cached and updated as users change data, so this call should only go out once every half hour.

      2. With the results from drive.files.list, we make calls to spreadsheets.values.batchget for each file as necessary. Responses from this call are cached as well, so the number of requests in this batch will range from 0 to 2.

      3. Using the JQL and issue lists retrieved from the previous step, we run queries Jira-side to determine which sheets need to change, and which changes need to be made.

      4. Operating on the results of the previous step, we make calls to spreadsheets.values.update or spreadsheets.values.append for the subset of sheets that need to have issue data added or updated, and spreadsheets.batchupdate for the subset of sheets that need to have an issue removed. A spreadsheets.batchupdate call here is much less common.
         
         

4. Could you provide the rough number of spreadsheets being read / updated simultaneously by each API user (for example, multiple requests to a small set of spreadsheets vs. a large variety of spreadsheets)

   1. The answer to this question depends on your Jira instance, but it is generally safe to say it is a large variety of spreadsheets. The number of realtime update sheets in your instance is a good estimate.
      
      

5. Are you polling the API to check if files have been modified?

   1. No, we are not.
      
      

6. Have you implemented exponential backoff? Exponential backoff can be used to retry requests after a delay when the application encounters a quota error.

   1. Yes, exponential backoff/retry has been implemented. Each request is retried up to 5 times, with the following delays between subsequent requests: 1 second, 4 seconds, 9 seconds, 16 seconds, and 25 seconds.

Please contact us if you have any additional questions or need support.