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')