I need some help in the batchupdate function for Google Sheet api. Currently in my project I am doing some processing and call my createEntry function to add a MSG to a specific cell on the gsheet using the append function as shown below. I realised every time I call that it adds to my quota of API calls requests and Google api stops and hence my program.
To workaround I thought I would store my data in 2D array and update all oneshot towards the end using batchUpdate to avoid exceeding the number of user requests per 100secs and per user set by Google API limits.
My questions are:
Is my logic correct in using batchupdate to solve the user request to Google API problem (keeping in mind I might be adding thousands of data across 6 columns later)?
PS: I have tried already putting the thread to sleep for 20 secs after every 20 or so inserts, but that is not solving the problem as it is unpredictable. sometimes I can go up to 400 cells of data and sometimes it will stop at just 70.
if yes, how can I batchupdate insert various data points in a range of cells?
My code I am using currently is-
public void CreateEntry(string col,int ctw, string msg)
{
var range = $"{sheet}!" + col.ToString() + ctw.ToString();
var valueRange = new ValueRange();
var oblist = new List<object>() { };
oblist.Add(msg);
valueRange.Values = new List<IList<object>> { oblist };
var appendRequest = service.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = appendRequest.Execute();
}
A simple sample of the gsheet data points for reference-
As of right now as you can see from my code above i am writing each cell individually. I want to now add all oneshot to avoid exceeding number of user requests per 100sec limit by Google.