private void DeleteSheet(int id)
{
var deleSheetRequest = new DeleteSheetRequest();
deleSheetRequest.SheetId = id;
var batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request {
DeleteSheet = deleSheetRequest
});
var req = _sheets.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, _ownSpreadsheetId);
req.Execute();
}
/// <summary>
/// Creates a request to delete the tab with the specified name
/// <para></para>
/// THROWS: InvalidOperationException
/// </summary>
/// <param name="tabName">The name of the tab to be deleted within the sheets doc.</param>
/// <param name="spreadsheetId">The id of the spreadsheet to be read. docs.google.com/spreadsheets/d/[SpreadsheetIdIsHere]/edit#gid=0</param>
/// <returns></returns>
private static Request requestDeleteSheet(string spreadsheetId, string tabName)
{
int?sheetIndex = getTabIndex(spreadsheetId, tabName);
// if the name was not found return
if (sheetIndex == null)
{
throw new InvalidOperationException(m_errorTabDoesNotExist);
}
// create a deletesheet request
DeleteSheetRequest deleteSheet = new DeleteSheetRequest();
deleteSheet.SheetId = sheetIndex;
// create base request and apply the delete request
Request request = new Request();
request.DeleteSheet = deleteSheet;
return(request);
}
/*
* delete all sheets except main sheet.
* we can't delete all sheets ,therefore don't delete main sheet.
*/
private void deleteTabs()
{
var deleteSheets = new BatchUpdateSpreadsheetRequest {
Requests = new List <Request>()
};
var spreadsheet = sheetsService.Spreadsheets.Get(spreadsheetId).Execute();
foreach (var sheet in spreadsheet.Sheets)
{
if (sheet.Properties.Title != "main")
{
var deleteSheet = new DeleteSheetRequest();
deleteSheet.SheetId = (int)sheet.Properties.SheetId;
deleteSheets.Requests.Add(new Request {
DeleteSheet = deleteSheet
});
}
}
var batchUpdateRequest = sheetsService.Spreadsheets.BatchUpdate(deleteSheets, spreadsheetId);
batchUpdateRequest.Execute();
}
private static async System.Threading.Tasks.Task DeleteOriginalSheet(SheetsService service, string spreadsheetId)
{
try
{
var requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
var requests = new List <Request>();
DeleteSheetRequest deleteSheet = new DeleteSheetRequest();
deleteSheet.SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, SheetOneTitle).ConfigureAwait(false);
var deleteRequest = new Request()
{
DeleteSheet = deleteSheet
};
requests.Add(deleteRequest);
requestBody.Requests = requests;
var batchRequest = service.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
await batchRequest.ExecuteAsync().ConfigureAwait(false);
}
finally
{
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
}
// Removes Sheet specified by provided spreadsheetId and non-null sheetId.
internal static SpreadsheetsResource.BatchUpdateRequest GetRemoveSheetRequest(SheetsService sheetsService, string spreadsheetId, int?sheetId)
{
// Get remove existing spreadsheet, existing sheet request.
DeleteSheetRequest deleteSheetRequest = new DeleteSheetRequest();
// Assign sheet Id, if provided sheetId is null, obtain it based on the provided sheeTitleId. If provided sheetId is null, throw appropriate exception.
deleteSheetRequest.SheetId = sheetId ?? throw new ArgumentNullException(nameof(sheetId));
// Create new instance of BatchUpdateSpreadsheetRequest, data of the to be returned instance of SpreadsheetsResource.BatchUpdateRequest ..
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
// .. initialize new list of partial requests ..
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
// .. and add sheet partial request to it.
batchUpdateSpreadsheetRequest.Requests.Add(new Request {
DeleteSheet = deleteSheetRequest
});
// Set ResponseIncludeGridData flag to false to increase performance.
batchUpdateSpreadsheetRequest.ResponseIncludeGridData = false;
// Construct and return SpreadsheetsResource.BatchUpdateRequest.
return(sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId));
}
/// <summary>
/// 刪除工作表
/// </summary>
public static void DeleteSheet(this SheetsService service, string spreadsheetId, string sheetName)
{
var sheet = service.GetSheet(spreadsheetId, sheetName);
if (sheet == null)
{
throw new NullReferenceException($"Can't find the spreadsheetId {spreadsheetId}, sheetName {sheetName}");
}
// 創建刪除Sheet指令
var deleteSheetRequest = new DeleteSheetRequest
{
SheetId = sheet.Properties.SheetId
};
// 創建一個要求
var request = new Request
{
DeleteSheet = deleteSheetRequest
};
// 送出請求
service.BatchUpdateSpreadsheet(spreadsheetId, request);
}
public async Task <IActionResult> Index()
{
try
{
// Define request parameters.
var accessTokenGoogle = await HttpContext.GetTokenAsync("Google", "access_token");
var refreshToken = await HttpContext.GetTokenAsync("Google", "refresh_token");
var accessToken = await HttpContext.GetTokenAsync("access_token");
var user = await _userManager.GetUserAsync(User);
string externalAccessToken = null;
ExternalLoginInfo info = await _signInManager.GetExternalLoginInfoAsync();
if (User.Identity.IsAuthenticated)
{
var userFromManager = await _userManager.GetUserAsync(User);
string authenticationMethod = User.Claims.FirstOrDefault(c => c.Type == ClaimTypes.AuthenticationMethod)?.Value;
if (authenticationMethod != null)
{
externalAccessToken = await _userManager.GetAuthenticationTokenAsync(userFromManager, authenticationMethod, "access_token");
}
else
{
externalAccessToken = await _userManager.GetAuthenticationTokenAsync(userFromManager, "Google", "access_token");
}
}
GoogleCredential googleCredential = GoogleCredential.FromAccessToken(externalAccessToken);
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = googleCredential,
ApplicationName = "Gdevapps Portal"
});
// String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";//1IYcktfn-VBqzPDh_meN1TfpL5vCJmTx6YybrlPqhOQc
//String range = "Class Data!A2:E";
//String spreadsheetId = "1IYcktfn-VBqzPDh_meN1TfpL5vCJmTx6YybrlPqhOQc";//
//String range = "GradeBook!A2:E";
String spreadsheetId = "1JF459hxZuD_uKdBI7QXzfOhV8w8b_oaVpV3Ap6HYBC0";//
String range = "Data!A1:C16";
//Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
ValueRange response = await service.Spreadsheets.Values.Get(spreadsheetId, range).ExecuteAsync();
IList <IList <Object> > values = response.Values;
for (var a = 0; a < values.Count; a++)
{
for (var b = 0; b < values[a].Count; b++)
{
//var val = values[a][b].ToString();
//values[a][b] = string.IsNullOrEmpty(val) ? val : val.Replace("_1", "*");
var val = "=Column()+1";
values[a][b] = val;
}
}
var valueRange = new ValueRange()
{
Range = range,
MajorDimension = "ROWS",
Values = values
};
var newSpreadSheet = await service.Spreadsheets.Create(new Spreadsheet()
{
Properties = new SpreadsheetProperties()
{
Title = "Pash_test"
}
}).ExecuteAsync();
CopySheetToAnotherSpreadsheetRequest copyToRequest = new CopySheetToAnotherSpreadsheetRequest();
copyToRequest.DestinationSpreadsheetId = newSpreadSheet.SpreadsheetId;
var result = await service.Spreadsheets.Sheets.CopyTo(copyToRequest, spreadsheetId, 0).ExecuteAsync();
DeleteSheetRequest deleteSheetRequest = new DeleteSheetRequest()
{
SheetId = 0
};
BatchUpdateSpreadsheetRequest updateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest()
{
Requests = new List <Request>
{
new Request()
{
DeleteSheet = deleteSheetRequest
}
},
ResponseIncludeGridData = false,
IncludeSpreadsheetInResponse = false
};
await service.Spreadsheets.BatchUpdate(updateSpreadsheetRequest, newSpreadSheet.SpreadsheetId).ExecuteAsync();
if (result != null)
{
return(Ok(result));
}
// var updateRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
//Formula
// updateRequest.ResponseValueRenderOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ResponseValueRenderOptionEnum.FORMULA;
// updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
//service.Spreadsheets.Values.Update().
// await updateRequest.ExecuteAsync();
if (values != null && values.Count > 0)
{
return(Ok(values));
}
else
{
return(BadRequest("No data found."));
}
}
catch (Exception ex)
{
string message = ex.Message;
throw;
}
}
/// <summary>
/// Метод для отправки данных баз знаний
/// </summary>
/// <param name="Databases"></param>
/// <returns></returns>
public static bool SetDataToTable(List <List <List <object> > > Databases)
{
//авторизация
UserCredential credential;
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(new ClientSecrets()
{
ClientId = Cache.Settings.GoogleConnection.ClientId,
ClientSecret = Cache.Settings.GoogleConnection.ClientSecret
},
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
//запускаем сервис для отправки реквестов на API
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "db-table",
});
//собираем реквест для обновления данных
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.ValueInputOption = "RAW";
List <string> serverNames = new List <string>();
List <ValueRange> valueRanges = new List <ValueRange>();
foreach (var db in Databases)
{
string serverName = (string)db.Select(x => x.Select(y => y).First()).Last();
serverNames.Add(serverName);
ValueRange data = new ValueRange()
{
Values = db.Select(x => x.Select(y => y).ToArray()).ToArray(),
Range = $"{serverName}!A1:D{db.Count}"
};
valueRanges.Add(data);
}
//получаем все листы в таблице
var SpreadsheetRequest = service.Spreadsheets.Get(Cache.Settings.GoogleConnection.SheetID);
var SheetsInSpreadsheet = SpreadsheetRequest.Execute().Sheets
.Select(x => x.Properties)
.ToDictionary(x => x.Title, y => y.SheetId);
//создание новых листов
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
foreach (var name in serverNames)
{
if (SheetsInSpreadsheet.Keys.Where(x => x == name).ToList().Count != 0)
{
continue;
}
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
var addSheetRequest = new AddSheetRequest();
addSheetRequest.Properties = new SheetProperties();
addSheetRequest.Properties.Title = name;
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
AddSheet = addSheetRequest
});
}
//удаление неактуальных листов
foreach (var sheet in SheetsInSpreadsheet)
{
if (serverNames.Contains(sheet.Key))
{
continue;
}
var deleteSheetRequest = new DeleteSheetRequest();
deleteSheetRequest.SheetId = sheet.Value;
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
DeleteSheet = deleteSheetRequest
});
}
//если ничего в плане листов не поменялось то ничего и не шлём
if (batchUpdateSpreadsheetRequest.Requests.Count != 0)
{
var batchUpdateRequest =
service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, Cache.Settings.GoogleConnection.SheetID);
batchUpdateRequest.Execute();
}
requestBody.Data = valueRanges.ToArray();
var request = service.Spreadsheets.Values.BatchUpdate(requestBody, Cache.Settings.GoogleConnection.SheetID);
var response = request.Execute();
Console.WriteLine($"Таблица обновлена {DateTime.Now} \nCледующая проверка {DateTime.Now.Add(Cache.Settings.TimeOffset.ToTimespan())}");
return(true);
}
