public object Edit(SheetRow _Row)
{
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = WebApiApplication.credential,
ApplicationName = WebApiApplication.ApplicationName
});
ValueRange v = new ValueRange();
v.Range = "Data!A" + _Row.RowIndex;
IList <IList <object> > ret = new List <IList <object> >();
IList <object> row = new List <object>();
IList <ValueRange> lst = new List <ValueRange>();
foreach (string s in _Row.Row)
{
row.Add(s);
}
ret.Add(row);
v.Values = ret;
lst.Add(v);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest _body = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
_body.Data = lst;
_body.ValueInputOption = "RAW";
v.MajorDimension = "ROWS";
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 =
service.Spreadsheets.Values.BatchUpdate(_body, spreadsheetId);
request2.Fields = "totalUpdatedColumns,totalUpdatedRows";
BatchUpdateValuesResponse response2 = request2.Execute();
return(response2.TotalUpdatedRows);
}
/// <summary>
///
/// </summary>
/// <param name="spreadsheetId"></param>
/// <param name="sheetTitle"></param>
/// <param name="headers"></param>
/// <param name="data"></param>
/// <returns></returns>
public async Task <BatchUpdateValuesResponse> BatchUpdateAsync(string spreadsheetId, string sheetTitle, List <IList <object> > headers, List <IList <object> > data)
{
// How the input data should be interpreted.
const string valueInputOption = "USER_ENTERED";
var headerRange = CalculateCellRange(headers, "A", 1);
var dataRange = CalculateCellRange(data, "A", 2);
// The new values to apply to the spreadsheet.
var reqData = new List <ValueRange>
{
new ValueRange
{
Range = $"{sheetTitle}!{headerRange}",
Values = headers
},
new ValueRange
{
Range = $"{sheetTitle}!{dataRange}",
Values = data
}
};
var requestBody = new BatchUpdateValuesRequest
{
ValueInputOption = valueInputOption,
IncludeValuesInResponse = true,
Data = reqData
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest batchUpdateRequest = _sheetsService.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
BatchUpdateValuesResponse batchUpdateResponse = await batchUpdateRequest.ExecuteAsync();
return(batchUpdateResponse);
}
public static string UpdateData(List <IList <object> > data)
{
String range = "Page1!A1";
string valueInputOption = "USER_ENTERED";
// The new values to apply to the spreadsheet.
List <ValueRange> updateData = new List <ValueRange>();
var dataValueRange = new ValueRange();
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
var request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadSheetId);
BatchUpdateValuesResponse response = request.Execute();
// Data.BatchUpdateValuesResponse response = await request.ExecuteAsync(); // For async
return(JsonConvert.SerializeObject(response));
}
private void UpdateGoogleSpreadsheetCell(string spreadSheetID, string cell, string value)
{
List <List <string> > valueList = new List <List <string> >
{
new List <string> {
value
}
};
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string ApplicationName = "Update Poker Data";
UserCredential credential;
string credentialsPath = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + "\\Dropbox\\Projects\\Poker Wizard\\Poker Wizard\\credentials.json";
using (var stream =
new FileStream(credentialsPath, FileMode.Open, FileAccess.ReadWrite))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
List <ValueRange> dataList = new List <ValueRange>();
var obList = new List <object>();
var data = new ValueRange
{
Values = valueList.Select(list => list.ToArray()).ToArray(),
Range = cell
};
dataList.Add(data);
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest
{
ValueInputOption = "RAW",
Data = dataList
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadSheetID);
BatchUpdateValuesResponse response = request.Execute();
}
// vieno operatoriaus vienos GS lentelės duomenis - fetchina, paverčia WeldingInspection ir updateinaDB bei GS
private static void fetchConvertUpdateSheet(SheetsService service,
Spreadsheets.Operator operatorData, // lemia, katro operatoriaus spreadsheet
Spreadsheets.SheetsRanges.SheetRangeData rangeData, // lemia, katrą to operatoriaus lentelę
ConvertDataToListMethod convertMethod, // --
DbUpdateMethod dbUpdateMethod) // --
{
try
{
List <IList <Object> > data = SheetDataFetcher.Fetch(
operatorData.SpreadsheetId,
rangeData.RangeAddress,
rangeData.FilterColumn.Index,
service).ToList();
List <WeldingInspection> inspectionList = convertMethod(data, rangeData.FieldMappings, operatorData.OperatorId);
dbUpdateMethod(inspectionList);
results.AppendFormat("{0} {1} db update OK", operatorData.OperatorId, rangeData.SheetName).AppendLine();
allInspections = allInspections.Concat(inspectionList);
}
catch (Exception ex)
{
results.AppendFormat("{0} {1} db update FAIL.", operatorData.OperatorId, rangeData.SheetName).AppendLine();
Exception e;
if (ex.InnerException != null)
{
e = ex.InnerException;
}
else
{
e = ex;
}
results.AppendFormat("Exception message: {0}", e.Message).AppendLine();
results.AppendLine("Google Sheets lentelėje duomenys nepažymimi kaip įvesti į DB ir šitie duomenys nebus įtraukti į ataskaitą.");
results.AppendLine();
return;
}
// updateinama Google Sheets lentelė.
// jeigu GS updateinimas nutrūktų, informuoja, kad tokio operatoriaus tokie patikrinimai į dB sukišti, bet lentelė neatnaujinta.
try
{
BatchUpdateValuesResponse response = GSheetsUpdater.BatchUpdateSheet(operatorData.SpreadsheetId, rangeData, service);
results.AppendFormat("{0} {1} Google Sheets update OK", operatorData.OperatorId, rangeData.SheetName).AppendLine();
results.AppendLine();
}
catch (Exception ex)
{
results.AppendFormat("{0} {1} Google Sheets update FAIL.", operatorData.OperatorId, rangeData.SheetName).AppendLine();
results.AppendFormat("Failure info: {0}", ex.Message).AppendLine();
results.Append("DĖMESIO! Kadangi duomenys į duomenų bazę sukelti, tie duomenys bus įtraukti į ataskaitą, kaip sutvarkyti. ");
results.AppendLine("Google Sheets lentelę reikėtų sutvarkyti rankiniu būdu - prisijungti prie Google Sheets, atsidaryti lentelę ir joje pažymėti, kurie duomenys yra sukelti į DB.");
results.AppendLine();
}
}
private static void Execute(SpreadsheetsResource.ValuesResource.BatchUpdateRequest request)
{
try
{
BatchUpdateValuesResponse response = request.Execute();
if (response != null)
{
}
}
catch (Exception ex)
{
string msg = ex.Message;
System.Diagnostics.Debugger.Break();
}
}
public void LogSpreadsheetUpdateDetails(BatchUpdateValuesResponse response)
{
C.WriteLine();
_helpers.Spotlight("UPDATE OUTCOMES");
C.WriteLine("-------------------------------------------------------");
C.WriteLine($"SPREADSHEET ID: | {response.SpreadsheetId}");
C.WriteLine($"# of SHEETS updated: | {response.TotalUpdatedSheets}");
C.WriteLine($"# of COLUMNS updated: | {response.TotalUpdatedColumns}");
C.WriteLine($"# of ROWS updated: | {response.TotalUpdatedRows}");
C.WriteLine($"# of CELLS updated: | {response.TotalUpdatedCells}");
C.WriteLine($"response.ETag: | {response.ETag}");
C.WriteLine("-------------------------------------------------------");
C.WriteLine();
}
/// <summary>
/// Register a user on the spread sheet
/// </summary>
/// <param name="id">Id of the user</param>
/// <param name="username">Username</param>
/// <returns>If the user was registered</returns>
public static bool register(Int64 id, string username)
{
//check that the user is not in the list
List <LoginToken> tokens = GetLoginTokens();
if (tokens.Any(item => item.id == id))
{
return(false);
}
// get the new user row number
int row = 2;
if (tokens.Count != 0)
{
row = tokens.Max(item => item.row) + 1;
}
// get the title of the spread sheet
var spreadSheet = Service.Spreadsheets.Get(info.SheetId).Execute();
string sheetTitle = spreadSheet.Sheets[0].Properties.Title;
// Formatt the user data
IList <IList <object> > data = new List <IList <object> >();
data.Add(new List <object>());
data[0].Add($"{id}");
data[0].Add(username);
data[0].Add(DateTime.Now.ToString("yyyy/MM/dd"));
List <ValueRange> ranges = new List <ValueRange>();
ranges.Add(new ValueRange()
{
Range = $"{sheetTitle}!A{row}:C{row}", Values = data
});
//Create a new request to change sheet cells
BatchUpdateValuesRequest body2 = new BatchUpdateValuesRequest();
body2.Data = ranges;
body2.ValueInputOption = "USER_ENTERED";
//submit the request to google sheets
BatchUpdateValuesResponse result = Service.Spreadsheets.Values.BatchUpdate(body2, info.SheetId).Execute();
return(result.Responses.Count == 1);
}
// STATUS [ August 3, 2019 ] : this works
/// <param name="data">todo: describe data parameter on WriteGoogleSheetColumnsAsync</param>
/// <param name="sheetName">todo: describe sheetName parameter on WriteGoogleSheetColumnsAsync</param>
/// <param name="range">todo: describe range parameter on WriteGoogleSheetColumnsAsync</param>
/// <param name="jsonGroupName">todo: describe jsonGroupName parameter on WriteGoogleSheetColumnsAsync</param>
/// <example>
/// await _gSC.WriteGoogleSheetColumnsAsync(listOfLists, "YAHOO_TRENDS","A1:Z1000","CoreCalculator");
/// </example>
public async Task <string> WriteGoogleSheetColumnsAsync(IList <IList <object> > data, string sheetName, string range, string jsonGroupName)
{
// _helpers.StartMethod();
ConnectToGoogle();
_spreadSheetId = SelectGoogleSheetToRead(jsonGroupName, "SpreadsheetId");
ValueRange dataValueRange = SetSheetDataValueRange(_columnsMajorDimension, sheetName, range, data);
List <ValueRange> updateData = CreateValueRangeList(dataValueRange);
BatchUpdateValuesRequest requestBody = CreateBatchUpdateValuesRequest(_userEnteredValueInputOption, updateData);
BatchUpdateRequest request = CreateBatchUpdateRequest(requestBody, _spreadSheetId);
BatchUpdateValuesResponse response = await CreateBatchUpdateValuesResponseAsync(request).ConfigureAwait(false);
// PRINTERS
// PrintRequestBodyData(requestBody);
// PrintUpdateRangeDetails(sheetName, range, jsonGroupName, spreadsheetId);
// LogSpreadsheetUpdateDetails(response);
return(JsonConvert.SerializeObject(response));
}
public object Create(SheetRow _Row)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = WebApiApplication.credential,
ApplicationName = WebApiApplication.ApplicationName
});
// Define request parameters.
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, "Data!A:B");
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
ValueRange v = new ValueRange();
v.Range = "Data!A" + (values.Count + 1) + ":Z" + (values.Count + 1);
IList <IList <object> > ret = new List <IList <object> >();
IList <object> row = new List <object>();
IList <ValueRange> lst = new List <ValueRange>();
foreach (string s in _Row.Row)
{
row.Add(s);
}
ret.Add(row);
v.Values = ret;
lst.Add(v);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest _body = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
_body.Data = lst;
_body.ValueInputOption = "RAW";
v.MajorDimension = "ROWS";
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 =
service.Spreadsheets.Values.BatchUpdate(_body, spreadsheetId);
request2.Fields = "totalUpdatedRows";
BatchUpdateValuesResponse response2 = request2.Execute();
return(response2.TotalUpdatedRows);
}
public static void AddList(List <ServiceRequestModel> requestModelList, string spreadSheetId)
{
// Specifying Column Range for reading...
var range = $"{sheet}!A:F";
List <ValueRange> valueRangesList = new List <ValueRange>();
// Data for another Student...
for (int i = 2; i < requestModelList.Count + 2; i++)
{
}
int index = 2;
foreach (var requestModel in requestModelList)
{
var valueRange = new ValueRange();
var oblist = new List <object>()
{
requestModel.TicketId, requestModel.FullName, requestModel.ServiceNm, requestModel.Status,
requestModel.StaffNm, requestModel.DepartmentNm
};
valueRange.Range = $"{sheet}!A{index}:F";
valueRange.Values = new List <IList <object> > {
oblist
};
valueRangesList.Add(valueRange);
index++;
}
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest()
{
ValueInputOption = "USER_ENTERED",
Data = valueRangesList
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadSheetId);
BatchUpdateValuesResponse response = request.Execute();
}
private void sendDataToGoogleSpreadsheets(object sender, EventArgs e)
{
UserCredential credential;
waitwindow f = new waitwindow();
f.Show();
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None).Result;
}
service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
String spreadsheetId = SheetId;
String range = "A:A";
SpreadsheetsResource.ValuesResource.GetRequest getRequest =
service.Spreadsheets.Values.Get(spreadsheetId, range);
ValueRange getResponse = getRequest.Execute();
IList <IList <Object> > getValues = getResponse.Values;
List <List <object> > data = new List <List <object> >();
int currentCount = getValues.Count() + 2;
String newRange = "A" + currentCount + ":A";
List <ValueRange> info = new List <ValueRange>();
int count = 3;
char cell = 'A';
foreach (var recipe in groceryList)
{
var reciperange = new ValueRange();
reciperange.Range = Char.ToString(cell) + count.ToString();
reciperange.MajorDimension = "COLUMNS";
List <object> ingreds = new List <object>();
foreach (var x in recipe)
{
string y = x.Substring(0, x.Length - 1);
ingreds.Add("----" + y + "----");
foreach (var item in ingredientList[x])
{
ingreds.Add(item);
}
ingreds.Add("");
}
reciperange.Values = new List <IList <object> > {
ingreds
};
info.Add(reciperange);
cell++;
}
string valueInputOption = "USER_ENTERED";
Google.Apis.Sheets.v4.Data.ClearValuesRequest yeah = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest clear = service.Spreadsheets.Values.Clear(yeah, spreadsheetId, "A1:G50");
clear.Execute();
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = info;
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
BatchUpdateValuesResponse response = request.Execute();
var headerRange = new ValueRange();
List <object> hd = new List <object>();
hd.Add("Monday");
hd.Add("Tuesday");
hd.Add("Wednesday");
hd.Add("Thursday");
hd.Add("Friday");
hd.Add("Saturday");
hd.Add("Sunday");
headerRange.Range = "A1:G1";
headerRange.Values = new List <IList <object> > {
hd
};
List <ValueRange> headerinfo = new List <ValueRange>();
headerinfo.Add(headerRange);
BatchUpdateValuesRequest requestBody2 = new BatchUpdateValuesRequest();
requestBody2.ValueInputOption = valueInputOption;
requestBody2.Data = headerinfo;
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 = service.Spreadsheets.Values.BatchUpdate(requestBody2, spreadsheetId);
BatchUpdateValuesResponse response2 = request2.Execute();
f.Close();
MessageBox.Show("Grocery Info Sent to Google Spreadsheet.");
}
//TODO start moving this crap to methods or other objects.
public void MainRun()
{
//TODO MEthod to connect and pull data from SQL
//TODO Method to clear the sheet
//TODO Method to write the header and data
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
UserCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
credPath = System.IO.Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Credential file saved to:\r\n" + credPath + "\r\n");
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Setup the SQL connection
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SqlConnection sqlConnection = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand
{
Connection = sqlConnection,
CommandTimeout = 60,
CommandType = CommandType.Text,
CommandText = select
};
try
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Open the SQL connection");
sqlConnection.Open();
}
catch (Exception e)
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Whoops we cannot connect to SQL - exception " + e.HResult.ToString());
//Environment.Exit(1);
}
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Please wait while reading data from SQL server");
SqlDataReader reader;
reader = cmd.ExecuteReader();
//API method to clear the sheet of all previous values
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Clear the Sheet");
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest cr = service.Spreadsheets.Values.Clear(clearValuesRequest, sID, dataRange);
// TODO add a try catch statement
ClearValuesResponse clearResponse = cr.Execute();
/*
* //API method to batch update and Delete all the rows
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Delete all rows in Sheet");
* DimensionRange dr = new DimensionRange
* {
* Dimension = "ROWS",
* StartIndex = 1000,
* SheetId = 1809337217 //this is a problem
* };
*
* DeleteDimensionRequest ddr = new DeleteDimensionRequest() { Range = dr };
*
* Request r = new Request { DeleteDimension = ddr };
*
* //THIS IS FOR deleteDimension { "requests": [{ "deleteDimension": { "range": { "sheetId": 1809337217, "startIndex": 1}} } ]};
* List<Request> batchRequests = new List<Request>() { r };
*
* BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest() { Requests = batchRequests };
* SpreadsheetsResource.BatchUpdateRequest bRequest = service.Spreadsheets.BatchUpdate(requestBody, sID);
* BatchUpdateSpreadsheetResponse busr = bRequest.Execute();
*/
/* I have added this to the batch method
* //API method to update the header in the Sheet
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Write the header to the Sheet");
* IList<object> headerList;
* headerList = header.Split(',');
*
* ValueRange valueRange = new ValueRange { MajorDimension = "ROWS" };
* valueRange.Values = new List<IList<object>> { headerList };
*
* SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, sID, dataRange);
* update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
* UpdateValuesResponse result;
* result = update.Execute();
*/
//API method to batch value update the data in the Sheet
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Write the data to the Sheet");
IList <object> headerList;
headerList = header.Split(',');
ValueRange headerVR = new ValueRange
{
MajorDimension = "ROWS",
Range = "A1",
Values = new List <IList <object> > {
headerList
}
};
ValueRange dataVR = new ValueRange()
{
MajorDimension = "ROWS",
Range = "A2:ZZ",
Values = new List <IList <object> >()
};
//Need a list of ValueRanges for the BatchUpdateValuesRequest API
List <ValueRange> data = new List <ValueRange>
{
headerVR,
dataVR
};
//Build the data 2D List object with SQL data
if (reader.HasRows)
{
Object[] colValues = new Object[reader.FieldCount];
List <IList <object> > valueList = new List <IList <object> >();
var rows = 0;
while (reader.Read())
{
List <object> rowData = new List <object>();
for (int i = 0; i < reader.GetValues(colValues); i++)
{
rowData.Add(colValues[i]);
}
valueList.Add(rowData);
rows++;
}
//after 2D list is built point it to the Values method in the ValueRange object
dataVR.Values = valueList;
}
else
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("No rows found");
}
BatchUpdateValuesRequest buvr = new BatchUpdateValuesRequest()
{
ValueInputOption = "RAW",
Data = data
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(buvr, sID);
BatchUpdateValuesResponse response = request.Execute();
/*
* //API to append data to sheet. This does not use the batchUpdate
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* // Data is accessible through the DataReader object here.
* ValueRange valueDataRange = new ValueRange() { MajorDimension = "ROWS" };
*
* var dataList = new List<object>();
* valueDataRange.Values = new List<IList<object>> { dataList };
*
* SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, sID, dataRange);
* appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
* appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
*
* if (reader.HasRows)
* {
* Object[] colValues = new Object[reader.FieldCount];
*
* int throttleCount = 0;
* int cnt = 0;
* while (reader.Read())
* {
* //This logic is flawed. If we get hit by the quota then the data row gets lost the next time this runs.
* dataList.Clear();
* for (int i = 0; i < reader.GetValues(colValues); i++)
* {
* dataList.Add(colValues[i]);
* }
*
* try
* {
* //This is the GOOGLE query Throttle they only allow 100 writes per 100 sec per user
* System.Threading.Thread.Sleep(20);
* AppendValuesResponse appendValueResponse = appendRequest.Execute();
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Writing to Sheet: row{" + cnt.ToString() + "}");
* }
* catch (Exception e)
* {
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Whoa buddy slowdown! Exception " + e.HResult.ToString());
* System.Threading.Thread.Sleep(3000);
* throttleCount++;
* }
* cnt++;
* }
* }
* else
* {
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("No rows found");
* }
*/
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Close reader and SQL");
reader.Close();
sqlConnection.Close();
}
private async Task <IResult <string> > UpdateGoogleSheet(
List <ValueRange> ranges, IList <string> rangesToClear, Uri sheetsUri, int attemptedRetries)
{
if (this.Service == null)
{
return(new FailureResult <string>(
"This instance of the bot doesn't support Google Sheets, because the Google account information for the bot isn't configured."));
}
IResult <string> sheetsIdResult = TryGetSheetsId(sheetsUri);
if (!sheetsIdResult.Success)
{
return(sheetsIdResult);
}
string sheetsId = sheetsIdResult.Value;
try
{
BatchUpdateValuesRequest updateValuesData = new BatchUpdateValuesRequest()
{
Data = ranges,
ValueInputOption = "RAW"
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest batchUpdateRequest = new SpreadsheetsResource.ValuesResource.BatchUpdateRequest(
this.Service, updateValuesData, sheetsId);
if (rangesToClear.Count > 0)
{
BatchClearValuesRequest clearValuesData = new BatchClearValuesRequest()
{
Ranges = rangesToClear
};
SpreadsheetsResource.ValuesResource.BatchClearRequest clearRequest = new SpreadsheetsResource.ValuesResource.BatchClearRequest(
this.Service, clearValuesData, sheetsId);
await clearRequest.ExecuteAsync();
}
BatchUpdateValuesResponse batchUpdateResponse = await batchUpdateRequest.ExecuteAsync();
if (batchUpdateResponse.Responses.Any(response => response.UpdatedCells == 0))
{
return(new FailureResult <string>("Could only partially update the spreadsheet. Try again."));
}
return(new SuccessResult <string>("Export successful"));
}
catch (Google.GoogleApiException exception)
{
// See https://developers.google.com/drive/api/v3/handle-errors
int errorCode = exception.Error?.Code ?? 0;
if (errorCode == 403 &&
exception.Error.Errors != null &&
exception.Error.Errors.Any(error => error.Reason == "appNotAuthorizedToFile" || error.Reason == "forbidden" || error.Reason == "insufficientFilePermissions"))
{
Logger.Error(exception, $"Error writing to the UCSD scoresheet: bot doesn't have permission");
return(new FailureResult <string>(
$"The bot doesn't have write permissions to the Google Sheet. Please give `{this.Options.CurrentValue.GoogleAppEmail}` access to the Sheet by sharing it with them as an Editor."));
}
else if (attemptedRetries < MaxRetries && (errorCode == 403 || errorCode == 429))
{
// Retry
attemptedRetries++;
Logger.Error(
exception,
$"Retry attempt {attemptedRetries} after getting a {errorCode} error for the UCSD scoresheet at the URL {sheetsUri.AbsoluteUri}");
// Use exponential back-off: wait for 2 seconds, then 5, then 9, etc.
await Task.Delay(1000 *(1 + (int)Math.Pow(2, attemptedRetries)));
return(await this.UpdateGoogleSheet(ranges, rangesToClear, sheetsUri, attemptedRetries));
}
// Log
Logger.Error(exception, $"Error writing to the UCSD scoresheet for URL {sheetsUri.AbsoluteUri}");
return(new FailureResult <string>($"Error writing to the Google Sheet: \"{exception.Message}\""));
}
}
// STATUS [ August 3, 2019 ] : this works
// BatchUpdateValuesResponse
// * "The response when updating a range of values in a spreadsheet"
// * Type is Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse
// * To do this async --> Data.BatchUpdateValuesResponse response = await request.ExecuteAsync();
// * See: https://bit.ly/2ZG0VJq
private async static Task <BatchUpdateValuesResponse> CreateBatchUpdateValuesResponseAsync(BatchUpdateRequest request)
{
BatchUpdateValuesResponse response = await request.ExecuteAsync().ConfigureAwait(false);
return(response);
}
// STATUS [ August 3, 2019 ] : this works
// BatchUpdateValuesResponse
// * "The response when updating a range of values in a spreadsheet"
// * Type is Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse
// * To do this async --> Data.BatchUpdateValuesResponse response = await request.ExecuteAsync();
// * See: https://bit.ly/2ZG0VJq
private static BatchUpdateValuesResponse CreateBatchUpdateValuesResponse(BatchUpdateRequest request)
{
BatchUpdateValuesResponse response = request.Execute();
return(response);
}
