/// <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);
}
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}\""));
}
}
// Add an event from raw data. Pretty much only used as a poor man's destructuring
private async Task AddEvent(DateTime date, string prop, string opp, string context, string motion, string judges, string remarks, char start_column = 'B')
{
// Get the row that the cursor is in
int row = GetNextRange();
if (row == -1)
{
throw new Exception("Cursor not found in sheets file!");
}
// Range of actual values to add
ValueRange range = new ValueRange();
var values = new List <IList <object> >
{
new List <object> {
date.ToString("dd/MM/yyyy"), date.DayOfWeek.ToString(), prop, opp, context, motion, judges, remarks
}
};
range.MajorDimension = "ROWS";
range.Range = $"'{SheetName}'!{start_column}{row}:{(char)(start_column + 9)}{row}";
range.Values = values;
// Range to move the cursor down
ValueRange cursorMover = new ValueRange();
var cVals = new List <IList <object> >
{
new List <object> {
Program.config.RegisterClubbyCursor
}
};
cursorMover.MajorDimension = "ROWS";
cursorMover.Range = $"'{SheetName}'!{CursorColumn}{row + 1}:{CursorColumn}{row + 1}";
cursorMover.Values = cVals;
// Range to delete the cursor from previous location
ValueRange cursor_deletor = new ValueRange();
var cDVals = new List <IList <object> >
{
new List <object> {
""
}
};
cursor_deletor.MajorDimension = "ROWS";
cursor_deletor.Range = $"'{SheetName}'!{CursorColumn}{row}:{CursorColumn}{row}";
cursor_deletor.Values = cDVals;
BatchUpdateValuesRequest req = new BatchUpdateValuesRequest
{
Data = new List <ValueRange>()
{
range, cursorMover, cursor_deletor
},
ValueInputOption = "USER_ENTERED"
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(req, spreadSheetId);
await request.ExecuteAsync();
}