// Очистка старых записей(если количество БД на сервере уменьшилось, лишние строки в таблице должны быть удалены) и публикация новых.
public async Task UpdateSpreadSheetAsync(Dictionary <string, List <DbSize> > serverInfoCollection)
{
BatchClearValuesRequest clearRequest = new BatchClearValuesRequest();
clearRequest.Ranges = new List <string>();
foreach (var server in serverInfoCollection)
{
clearRequest.Ranges.Add($"{server.Key}!A2:D50");
}
// Асинхронно запускаем очистку таблицы
var clearTask = _service.Spreadsheets.Values.BatchClear(clearRequest, _spreadSheetId).ExecuteAsync();
BatchUpdateValuesRequest addRequest = new BatchUpdateValuesRequest();
addRequest.Data = new List <ValueRange>();
addRequest.ValueInputOption = "USER_ENTERED";
foreach (var server in serverInfoCollection)
{
addRequest.Data.Add(replaceContnet(server.Key, server.Value));
}
//Ждем пока завершится асинхронная задача очистики и отправляем запрос на запись обновленных данных
await clearTask;
await _service.Spreadsheets.Values.BatchUpdate(addRequest, _spreadSheetId).ExecuteAsync();
}
private void deleteBtn_Click(object sender, EventArgs e)
{
var service = CreateGoogleService();
string range;
//delete selected one
switch (currentPage)
{
case Pages.SignIn:
range = "Winter 2018!I" + (rowStart + actualRowIndex + listView1.SelectedItems[0].Index).ToString() + ":N" + (rowStart + actualRowIndex + listView1.SelectedItems[0].Index).ToString();
break;
case Pages.LabEquipment:
range = "Winter 2018!A" + (rowStart + actualRowIndex + listView1.SelectedItems[0].Index).ToString() + ":G" + (rowStart + actualRowIndex + listView1.SelectedItems[0].Index).ToString();
break;
default:
range = null;
break;
}
// The ranges to clear, in A1 notation.
List <string> ranges = new List <string>(); // TODO: Update placeholder value.
ranges.Add(range);
// TODO: Assign values to desired properties of `requestBody`:
BatchClearValuesRequest requestBody = new BatchClearValuesRequest();
requestBody.Ranges = ranges;
SpreadsheetsResource.ValuesResource.BatchClearRequest delete = service.Spreadsheets.Values.BatchClear(requestBody, spreadsheetId);
BatchClearValuesResponse result2 = delete.Execute();
//BatchUpdateSpreadsheetRequest requestMoveUpBody = new BatchUpdateSpreadsheetRequest();
//requestMoveUpBody.Requests =
//SpreadsheetsResource.BatchUpdateRequest moveUp = service.Spreadsheets.BatchUpdate(requestMoveUpBody, spreadsheetId);
listView1.Items.Remove(listView1.SelectedItems[0]);
returnBtn.Enabled = false;
returnBtn.Visible = false;
deleteBtn.Enabled = false;
deleteBtn.Visible = false;
copyBtn.Enabled = false;
copyBtn.Visible = false;
}
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}\""));
}
}