1

I send 35 requests to googlesheets api once an hour.

But I get error "Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com'" often

Why?

My quotas:

Read requests per 100 seconds 500

Read requests per 100 seconds per user 100

I don't send other requests. No one else uses this API. I use BatchGetValues if I need several ranges

And general, 35 is less than 100

UPD

console.developers.google.com screenshot

Sheet sheet = new Sheet();
sheet.Authorize(Config.googleClientSecret);
sheet.GetSheetService();

//gets 35 sheets
sheetsToImport =  SheetImportInfo.FromDatabase();

foreach (SheetImportInfo i in sheetsToImport)
{
    try
    {
        if (i.range.Split(',').Length > 1)
        {
            IList<Range> resultList = sheet.BatchGetRange(i.spreadsheetId, rangeList);
        }
        else
        {
            IList<IList<object>> result = sheet.GetRange(i.spreadsheetId, i.range);
        }
    }
    catch (Exception err)
    {
        errors++;
    }
}

sheet class

public class Sheet
{

    private SheetsService service;
    private UserCredential credential;

    public void Authorize(string clientSecret)
    {
        string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };
        using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(clientSecret)))
        {
            string credPath = $"{System.AppDomain.CurrentDomain.BaseDirectory}\\credentials";
            this.credential = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result;
        }
    }

    public void GetSheetService()
    {
        string ApplicationName = "BI Google Sheets Importer";
        SheetsService service = new SheetsService(new BaseClientService.Initializer()
        {
            HttpClientInitializer = this.credential,
            ApplicationName = ApplicationName,
        });
        this.service = service;
    }

    public IList<IList<object>> GetRange(string spreadsheetId, string range)
    {
        //SheetsService service = GetSheetService();
        SpreadsheetsResource.ValuesResource.GetRequest request = this.service.Spreadsheets.Values.Get(spreadsheetId, range);
        ValueRange response = request.Execute();
        return response.Values;
    }   

    public IList<Range> BatchGetRange(string spreadsheetId, List<string> ranges)
    {
        //SheetsService service = GetSheetService();
        SpreadsheetsResource.ValuesResource.BatchGetRequest request = this.service.Spreadsheets.Values.BatchGet(spreadsheetId);
        request.Ranges = ranges;
        BatchGetValuesResponse response = request.Execute();
        IList<Range> resultList = new List<Range>();
        foreach (var valueRange in response.ValueRanges)
        {
            resultList.Add(new Range(valueRange.Range, valueRange.Values));
        }

        return resultList;
    }

}

public class SheetImportInfo
{
    public string spreadsheetId;
    public string range;

    public string sqlCmdPreImport;
    public string sqlDestTableName;
    public string sqlCmdPostImport;

    public bool createTable;

    public static List<SheetImportInfo> FromDatabase(string connectionString, string cmdText)
    {
        List<SheetImportInfo> result = new List<SheetImportInfo>();
        DataTable dt = Utils.Sql.Utils.GetDataTable(connectionString, cmdText);
        foreach(DataRow row in dt.Rows)
        {
            SheetImportInfo si = new SheetImportInfo();
            si.spreadsheetId = row["spreadsheet_id"].ToString();
            si.range = row["range"].ToString();
            si.sqlCmdPreImport = row["cmd_pre_import"].ToString();
            si.sqlDestTableName = row["dest_table_name"].ToString();
            si.sqlCmdPostImport = row["cmd_post_import"].ToString();
            si.createTable = (Convert.ToInt32(row["create_table"].ToString()) == 1);
            result.Add(si);
        }
        return result;
    }
} 

UPD2. In the last hour I sent 37 requests and got 4 errors[429](Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com')

Read requests per 100 seconds

Errors by API method

tdn680000
  • 11
  • 2
  • Can you show the code of your request? – ziganotschka Apr 27 '20 at 13:13
  • The answer is simple, you are calling more than 100 api requests in an hour. Without the relevant code you are saying you have no idea why the bank vault is empty but can't give us the surveillance footage. – CodeCamper Apr 27 '20 at 13:21
  • "I `think I am` send`ing` 35 requests to googlesheets api once an hour." – CodeCamper Apr 27 '20 at 13:28
  • @ziganotschka, I attached code and sreenshots. Can you help me understand the error? – tdn680000 Apr 28 '20 at 12:02
  • Wondering if the inner loop in `BatchGetRange()` contributes towards your quota. Try to comment it away to see if this is the culpite. Also, implement a counter variable in you for loops to keep track of how many iterations take place. – ziganotschka Apr 28 '20 at 13:51
  • There was only GetRange in my code a week ago. Count of request was about 50. I got [429] errors. Then I read here (https://stackoverflow.com/questions/45225734/how-to-increase-google-sheets-v4-api-quota-limitations), that using Spreadsheets.Values.BatchGet will reduce the count of requests. But it didn't help Screenshots of google console confirm that the count of requests does not exceed 100 – tdn680000 Apr 28 '20 at 14:40
  • Did you solve this? I am getting the same issue with `values.update` and `WriteGroup` – DaveB Aug 24 '20 at 14:58

0 Answers0