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); }