public static void AtualizarPlanilha() { SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(idPlanilha); planilhas = get.Execute(); valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2; foreach (Sheet planilha in planilhas.Sheets) { ClearValuesRequest clearRequest = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, idPlanilha, "!A1:Z1000"); request.Execute(); for (int i = 0; i <= 5; i++) { GerarTabelaMesSheet(i); } for (int i = 0; i <= 5; i++) { GerarTabelaSavingsSheet(i); } Layout(); } }
public void Clear(string sheetId, string range) { ClearValuesRequest body = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = _sheetService.Spreadsheets.Values.Clear(body, sheetId, range); clearRequest.Execute(); }
public void ClearSheetAtRange(string sheetName, string range) { ClearValuesRequest requestBody = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = Service.Spreadsheets.Values.Clear(requestBody, SheetId, GetSheetAndRangeName(sheetName, range)); clearRequest.Execute(); }
public static void UpdateEntry() { ReadCart(); var range = $"cart!A:C"; var valueRange = new ValueRange(); var oblist = new List <object>() { 0 }; valueRange.Values = new List <IList <object> > { oblist }; ClearValuesRequest requestBody = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = _service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, range); // To execute asynchronously in an async method, replace `request.Execute()` as shown: ClearValuesResponse response = request.Execute(); // Data.ClearValuesResponse response = await request.ExecuteAsync(); // TODO: Change code below to process the `response` object: Console.WriteLine(JsonConvert.SerializeObject(response)); }
private void clearColumn(string range) { ClearValuesRequest clearRequest = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest req = service.Spreadsheets.Values.Clear(clearRequest, spreadSheetId, range); req.Execute(); }
private void RemoveEntry(string SpreadSheet, List <object> objectArray) { ValueRange values = GetValues(SpreadSheet); int i = 1; bool hasBeenSet = false; foreach (IList <object> row in values.Values.Skip(1)) { i++; if (row[0].ToString() == objectArray[0].ToString()) { hasBeenSet = true; break; } } if (!hasBeenSet) { return; } string range = $"{SpreadSheet}!A{i}:Z{i}"; ClearValuesRequest valueRange = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = service.Spreadsheets.Values.Clear(valueRange, SpreadSheetID, range); ClearValuesResponse updateRespond = clearRequest.Execute(); }
public void ClearValues(string range) { var body = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = _service.Spreadsheets.Values.Clear(body, _sheetId, range); request.Execute(); }
private void ClearSpreadsheet() { string range = "Server List!A1:A255"; ClearValuesRequest requestBody = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = SheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range); ClearValuesResponse response = request.Execute(); }
private void CleanRange(SheetsService service, string range) { Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range); // To execute asynchronously in an async method, replace `request.Execute()` as shown: Data.ClearValuesResponse response = request.Execute(); }
private void cleanGoogleSheets() { Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, googleSheetsRange); try { request.Execute(); } catch (Exception e) { throw e; } }
private static void UpdateSheet(ref SheetsService service, string sheetName, ref IList <IList <Object> > values) { SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = new SpreadsheetsResource.ValuesResource.ClearRequest(service, new ClearValuesRequest(), _googleSheets_Spreadsheet.SpreadsheetId, range: $"{sheetName}!A:D"); //preliminary cleanse the sheet clearRequest.Execute(); SpreadsheetsResource.ValuesResource.AppendRequest updateRequest = service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, _googleSheets_Spreadsheet.SpreadsheetId, range: $"{sheetName}!A:A"); updateRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.OVERWRITE; updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW; updateRequest.Execute(); }
/// <summary> /// 清空資料表內容 /// </summary> private void ClearV4Sheets() { SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = GetCredential(), ApplicationName = "Get Google SheetData with Google Sheets API", }); // TODO: Assign values to desired properties of `requestBody`: ClearValuesRequest requestBody = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, sheetName); // To execute asynchronously in an async method, replace `request.Execute()` as shown: ClearValuesResponse response = request.Execute(); // Data.ClearValuesResponse response = await request.ExecuteAsync(); }
private void UpdateTable() { try { if (spreadSheetsService == null) { spreadSheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = applicationName, }); } IList <IList <object> > insertValues = new List <IList <object> >(); foreach (Note note in Note.NoteList) { IList <object> ROW = new List <object>(); ROW.Add(JsonConvert.SerializeObject(note, jsonSettings)); insertValues.Add(ROW); if (note.GetType() == typeof(Screenshot)) { Screenshot screen = note as Screenshot; UploadScreenshot(screen); } } ClearValuesRequest clearValuesRequest = new ClearValuesRequest(); ValueRange body = new ValueRange(); body.Values = insertValues; SpreadsheetsResource.ValuesResource.ClearRequest clearResult = spreadSheetsService.Spreadsheets.Values.Clear(clearValuesRequest, gSyncSettings.spreadshetrID, "Notesieve!A1:B"); SpreadsheetsResource.ValuesResource.UpdateRequest result = spreadSheetsService.Spreadsheets.Values.Update(body, gSyncSettings.spreadshetrID, "Notesieve!A1:B"); clearResult.Execute(); result.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; result.Execute(); } catch (System.Net.Http.HttpRequestException e) { OnSyncStateChanged?.Invoke(gSyncState.eLostConnection); } }
public static Data.ClearValuesResponse Clearspreadsheet(string spreadsheetId, string range, SheetsService service) { Console.WriteLine("Do you want to give your own range? 1. Yes\t Any other key. No"); string rangeChoice = Console.ReadLine(); if (rangeChoice == "1") { range = GetRangeFromUser(); } //Create a clear request SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(null, spreadsheetId, range); Console.WriteLine("Clearing range " + range + " from spreadsheet..."); //Execute the request Data.ClearValuesResponse response = request.Execute(); Headers.Clear(); return(response); }
private static void CreateSheet(SheetsService service, string spreadsheetId, string name) { try { var addSheetRequest = new AddSheetRequest(); addSheetRequest.Properties = new SheetProperties(); addSheetRequest.Properties.Title = name; BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest(); batchUpdateSpreadsheetRequest.Requests = new List <Request>(); batchUpdateSpreadsheetRequest.Requests.Add(new Request { AddSheet = addSheetRequest }); var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId); batchUpdateRequest.Execute(); } catch { try { ClearValuesRequest clearValuesRequest = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, name + "!$A$1:$YY"); ClearValuesResponse response = request.Execute(); Thread.Sleep(API_THROTTLE_WAIT); } catch { //sheet doesn`t exist } } finally { Thread.Sleep(API_THROTTLE_WAIT); } }
public object deleteData(string spreadsheetId, string tenBang, string phamViCell) { /*Xóa dữ liệu trong bảng * Đầu vào cần có ID bảng, Tên sheet vd: sheet 1, phạm vi cần xóa*/ UserCredential credential = GetCredential(ScopesWrite); var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = "VolunteerDTU" }); try { string range = "'" + tenBang + "'!" + phamViCell;; Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range); Data.ClearValuesResponse response = request.Execute(); return(request); } catch (Exception e) { return(e); } }
public Form1() { InitializeComponent(); GoogleCredential credential; using (var stream = new FileStream("My First Project-2dfed0050064.json", FileMode.Open, FileAccess.Read)) { credential = GoogleCredential.FromStream(stream).CreateScoped(Scopes); } service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); Google.Apis.Sheets.v4.Data.ClearValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, "Main Table!A1:Z100"); Google.Apis.Sheets.v4.Data.ClearValuesResponse response = request.Execute(); Google.Apis.Sheets.v4.Data.ClearValuesRequest requestBody2 = new Google.Apis.Sheets.v4.Data.ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request2 = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, "Data Table!A1:Z100"); Google.Apis.Sheets.v4.Data.ClearValuesResponse response2 = request2.Execute(); }
public void TesteSheet() { #region setup UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = System.Environment.GetFolderPath( System.Environment.SpecialFolder.Personal); credPath = Path.Combine(credPath, ".credentials/games.json"); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; } // Create Google Sheets API service. SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = credential, ApplicationName = "Google-SheetsSample/0.1", }); string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc"; List <GameView> lista_mock = new List <GameView>(); lista_mock.Add(new GameView { Name = "teste" }); lista_mock.Add(new GameView { Name = "teste" }); lista_mock.Add(new GameView { Name = "teste" }); lista_mock.Add(new GameView { Name = "teste" }); lista_mock.Add(new GameView { Name = "teste teste teste teste teste teste teste" }); GameRepository game = new GameRepository(); PlatformRepository plataforma = new PlatformRepository(); #endregion #region limpar, preencher e formatar abas SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id); Spreadsheet planilhas = get.Execute(); SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2; foreach (Sheet planilha in planilhas.Sheets) { var aba = planilha.Properties.Title; ClearValuesRequest clearRequest = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000"); ClearValuesResponse response = request.Execute(); List <GameView> lista = new List <GameView>(); List <int> plat = new List <int>(); List <object> cabecalho = null; switch (aba) { case "Wishlist": lista = game.ListarJogosWishlist(new List <int> { 0 }); cabecalho = new List <object>() { "", "Título", "Lançamento", "Plataformas" }; break; case "Watchlist": lista = game.ListarJogos(new List <int> { 0 }, 3); cabecalho = new List <object>() { "", "Título", "Lançamento", "Plataformas" }; break; default: int?plataformas = plataforma.GetIdBySigla(aba); plat = new List <int> { plataformas.Value }; lista = game.ListarJogos(plat, 1); cabecalho = new List <object>() { "", "Título" }; break; } string range = aba + "!A1:D" + lista.Count + 1; List <IList <object> > dados = new List <IList <object> >(); dados.Add(cabecalho); foreach (GameView jogo in lista) { if (cabecalho.Count == 2) { dados.Add(new List <object>() { "=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name }); } else { string data = null; if (jogo.ReleaseDate != null) { data = jogo.ReleaseDate.Value.ToShortDateString(); } dados.Add(new List <object>() { "=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas) }); } } ValueRange valueRange = new ValueRange(); valueRange.Values = dados; SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range); updateRequest.ValueInputOption = valueInputOption; UpdateValuesResponse resposta = updateRequest.Execute(); Request alignLeftRequest = new Request(); alignLeftRequest.RepeatCell = new RepeatCellRequest(); alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)"; alignLeftRequest.RepeatCell.Range = new GridRange { SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3 }; alignLeftRequest.RepeatCell.Cell = new CellData { UserEnteredFormat = new CellFormat { HorizontalAlignment = "LEFT" } }; Request alignCenterRequest = new Request(); alignCenterRequest.RepeatCell = new RepeatCellRequest(); alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)"; alignCenterRequest.RepeatCell.Range = new GridRange { SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1 }; alignCenterRequest.RepeatCell.Cell = new CellData { UserEnteredFormat = new CellFormat { HorizontalAlignment = "Center" } }; Request resizeRequest = new Request(); resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest(); resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange { SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count }; BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest(); batch.Requests = new List <Request>(); batch.Requests.Add(alignLeftRequest); batch.Requests.Add(alignCenterRequest); batch.Requests.Add(resizeRequest); SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id); BatchUpdateSpreadsheetResponse responseResize = u.Execute(); } #endregion #region deletar aba // A list of updates to apply to the spreadsheet. // Requests will be applied in the order they are specified. // If any request is not valid, no requests will be applied. /*List<Request> requests = new List<Request>(); // TODO: Update placeholder value. * var a = new DeleteSheetRequest(); * a.SheetId = 0; * * var t = new Request(); * t.DeleteSheet = a; * * requests.Add(t); * * // TODO: Assign values to desired properties of `requestBody`: * BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest(); * requestBody.Requests = requests; * * SpreadsheetsResource.BatchUpdateRequest req = sheetsService.Spreadsheets.BatchUpdate(requestBody, id); * * // To execute asynchronously in an async method, replace `request.Execute()` as shown: * BatchUpdateSpreadsheetResponse response = req.Execute();*/ #endregion #region criar planilha // TODO: Assign values to desired properties of `requestBody`: /*Spreadsheet requestBody3 = new Spreadsheet(); * var propriedades = new SpreadsheetProperties(); * propriedades.Title = "Games"; * requestBody3.Properties = propriedades; * * SpreadsheetsResource.CreateRequest request3 = sheetsService.Spreadsheets.Create(requestBody3); * * // To execute asynchronously in an async method, replace `request.Execute()` as shown: * Spreadsheet response3 = request3.Execute(); * // Data.Spreadsheet response = await request.ExecuteAsync(); * * // TODO: Change code below to process the `response` object: * Console.WriteLine(response3);*/ #endregion }
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(); }
static void Main(string[] args) { Console.WriteLine("Welcome to Sheets2CSV"); if (File.Exists("Settings.txt")) { Console.WriteLine("There Are Settings!!"); StreamReader infile = new StreamReader("Settings.txt"); configured = infile.ReadLine(); outputdir = infile.ReadLine(); spreadsheetID = infile.ReadLine(); headernum = infile.ReadLine(); entity = infile.ReadLine(); columns = infile.ReadLine(); infile.Close(); } else { Console.WriteLine("Program has not been configured yet! Please Run SettingsUI first to start this program."); Environment.Exit(0); } Console.WriteLine("Moving On..."); #region Configuration Level string path = outputdir; //string value = outputdir; path = path + "\\"; #endregion #region Preparation Level #region Sheets API UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = System.Environment.GetFolderPath( System.Environment.SpecialFolder.Personal); credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-Sheetstocsv.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, }); // Define request parameters. String spreadsheetId = spreadsheetID; char let = (char)(64 + Convert.ToInt32(headernum)); string range = "A2:" + let; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range); Data.ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; #endregion Boolean fileExist; //Check for File if (File.Exists(path + "Lead_Sheet.csv")) { fileExist = true; } else { fileExist = false; } //Setup File IO StreamWriter file = new StreamWriter(path + entity + "_Sheet.csv", true); StreamWriter injectionfile = new StreamWriter(path + entity + "_inject.csv"); //Set up Headers string[] headers = new string[] { /*"+new_createdon", * "firstname", * "lastname", * "address1_postalcode", * "emailaddress1", * "telephone2", * "new_isprimaryphonecellphone", * "*new_besttimetocall", * "subject"*/ "fname", "lname", "org_name", "member_role" }; //Check for exisitng records if (!fileExist) { file.Write(entity + ", " + columns + "\n"); } injectionfile.Write(entity + ", " + columns + "\n"); #endregion #region Execution Level //Handle Retrieved Data if (values != null && values.Count > 0) { foreach (var row in values) { file.Write(entity + ", "); injectionfile.Write(entity + ", "); for (int i = 0; i < row.Count - 1; i++) { if (i == row.Count - 1) { file.Write(row[i]); injectionfile.Write(row[i]); } if (headers[i] == "address1_postalcode") { string r = row[i].ToString(); if (r.Length > 5) { file.Write(r.Substring(0, 5) + ", "); injectionfile.Write(r.Substring(0, 5) + ", "); } else { file.Write(row[i] + ", "); injectionfile.Write(row[i] + ", "); } } else if (headers[i] == "telephone2") { string val = row[i].ToString(); val = val.Replace("-", string.Empty); val = val.Replace(" ", string.Empty); val = val.Replace("(", string.Empty); val = val.Replace(")", string.Empty); val = val.Replace(".", string.Empty); if (val.Length > 10) { val = val.Substring(0, 10); } file.Write(val + ", "); injectionfile.Write(val + ", "); } else if (headers[i] == "new_isprimaryphonecellphone") { if (row[i].ToString() == "Yes") { file.Write("TRUE" + ", "); injectionfile.Write("TRUE" + ", "); } else { file.Write("FALSE" + ", "); injectionfile.Write("FALSE" + ", "); } } else if (headers[i] == "*new_besttimetocall") { file.Write(callTime[row[i].ToString()] + ", "); injectionfile.Write(callTime[row[i].ToString()] + ", "); } else { file.Write(row[i] + ", "); injectionfile.Write(row[i] + ", "); } } file.Write("\n"); injectionfile.Write("\n"); } } string ClearRange = "A2:Z"; Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, ClearRange); Data.ClearValuesResponse clearResponse = clearRequest.Execute(); file.Close(); injectionfile.Close(); #endregion }
//static volatile bool exit = false; static void Main(string[] args) { UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); credPath = 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; Console.WriteLine("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, }); // Prints the data from a sample spreadsheet: // https://docs.google.com/spreadsheets/d/1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So/edit#gid=0 // Define request parameters. String spreadsheetId = "1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So"; // here is the actual data to be sent to sheet List <object> headerList = new List <object>() { "ID", "DTStamp", "DTShiftStart", "ModelNbr", "SerialNbr", "PassFail", "LineNbr", "ShiftNbr", "Computer", "Word40", "Word41", "Word42" , "Word43", "Word44", "Word45", "Word46", "Word47", "Word48", "Word49", "Word50", "Word51", "Word52", "Word53", "Word54", "Word55", "Word56" , "Word57", "Word58", "Word59", "Word60", "Word61", "Word62", "Word63", "Word64", "Word65", "Word66", "Word67", "Word68", "Word69", "Word70" , "Word71", "Word72", "Word73", "Word74", "Word75", "Word76", "Word77", "Word78", "Word79", "Word80" }; //var dataList = new List<object>(); //Write some data String writeRange = "WriteData!A1:ZZ"; ValueRange valueRange = new ValueRange { MajorDimension = "ROWS" }; Console.WriteLine("Clear the Sheet"); //API method to clear the sheet ClearValuesRequest clearValuesRequest = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest cr = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, writeRange); ClearValuesResponse clearResponse = cr.Execute(); Console.WriteLine("Delete all rows in Sheet"); //API method to batch update DimensionRange dr = new DimensionRange { Dimension = "ROWS", StartIndex = 1, SheetId = 1809337217 }; DeleteDimensionRequest ddr = new DeleteDimensionRequest() { Range = dr }; Request r = new Request { DeleteDimension = ddr }; // { "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, spreadsheetId); BatchUpdateSpreadsheetResponse busr = bRequest.Execute(); Console.WriteLine("Write the Headers to the Sheet"); //API method to update the sheet valueRange.Values = new List <IList <object> > { headerList }; SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, writeRange); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; UpdateValuesResponse result; result = update.Execute(); SqlConnection sqlConnection = new SqlConnection("Data Source=tul-mssql;Initial Catalog=Division;User ID=tqisadmin;Password=admin2k"); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandText = "SELECT TOP 1000 [ID],[DTStamp],[DTShiftStart],[ModelNbr],[SerialNbr],[PassFail],[LineNbr],[ShiftNbr],[Computer],[Word40],[Word41],[Word42]" + ",[Word43],[Word44],[Word45],[Word46],[Word47],[Word48],[Word49],[Word50],[Word51],[Word52],[Word53],[Word54],[Word55],[Word56]" + ",[Word57],[Word58],[Word59],[Word60],[Word61],[Word62],[Word63],[Word64],[Word65],[Word66],[Word67],[Word68],[Word69],[Word70]" + ",[Word71],[Word72],[Word73],[Word74],[Word75],[Word76],[Word77],[Word78],[Word79],[Word80] " + "FROM[Division].[dbo].[asyTestRecords] where LineNbr = 2 and computer = 'LN' and dtstamp > '2/1/2018 5:00' order by dtstamp desc"; cmd.CommandType = CommandType.Text; cmd.Connection = sqlConnection; Console.WriteLine("Open the SQL connection"); sqlConnection.Open(); cmd.CommandTimeout = 60; Console.WriteLine("Please wait while reading data from SQL"); reader = cmd.ExecuteReader(); // 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 }; //API to append data to sheet SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, spreadsheetId, writeRange); appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW; appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS; if (reader.HasRows) { //Console.WriteLine("{0}",reader.FieldCount); 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 500 writes per 100 sec System.Threading.Thread.Sleep(20); AppendValuesResponse appendValueResponse = appendRequest.Execute(); Console.WriteLine("Writing to Sheet: row{0}", cnt); } catch (Exception e) { Console.WriteLine("Whoa buddy slowdown {0}", throttleCount); System.Threading.Thread.Sleep(3000); throttleCount++; } cnt++; } } else { Console.WriteLine("No rows found."); } // sit here and wait for a while Console.WriteLine("Done waiting to close reader and SQL"); System.Threading.Thread.Sleep(3000); reader.Close(); sqlConnection.Close(); }
public void ExportarJogosJquery() { string[] Scopes = { SheetsService.Scope.Spreadsheets }; UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = System.Environment.GetFolderPath( System.Environment.SpecialFolder.Personal); credPath = Path.Combine(credPath, ".credentials/games.json"); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; } // Create Google Sheets API service. SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = credential, ApplicationName = "Google-SheetsSample/0.1", }); string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc"; GameRepository game = new GameRepository(); PlatformRepository plataforma = new PlatformRepository(); SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id); Spreadsheet planilhas = get.Execute(); SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2; foreach (Sheet planilha in planilhas.Sheets) { var aba = planilha.Properties.Title; ClearValuesRequest clearRequest = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000"); ClearValuesResponse response = request.Execute(); List <GameView> lista = new List <GameView>(); List <int> plat = new List <int>(); List <object> cabecalho = null; switch (aba) { case "Wishlist": lista = game.ListarJogosWishlist(new List <int> { 0 }); cabecalho = new List <object>() { "", "Título", "Lançamento", "Plataformas" }; break; case "Watchlist": lista = game.ListarJogos(new List <int> { 0 }, 3); cabecalho = new List <object>() { "", "Título", "Lançamento", "Plataformas" }; break; default: int?plataformas = plataforma.GetIdBySigla(aba); plat = new List <int> { plataformas.Value }; lista = game.ListarJogos(plat, 1); cabecalho = new List <object>() { "", "Título" }; break; } string range = aba + "!A1:D" + lista.Count + 1; List <IList <object> > dados = new List <IList <object> >(); dados.Add(cabecalho); foreach (GameView jogo in lista) { if (cabecalho.Count == 2) { dados.Add(new List <object>() { "=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name }); } else { string data = null; if (jogo.ReleaseDate != null) { data = jogo.ReleaseDate.Value.ToShortDateString(); } dados.Add(new List <object>() { "=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas) }); } } ValueRange valueRange = new ValueRange(); valueRange.Values = dados; SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range); updateRequest.ValueInputOption = valueInputOption; UpdateValuesResponse resposta = updateRequest.Execute(); Request alignLeftRequest = new Request(); alignLeftRequest.RepeatCell = new RepeatCellRequest(); alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)"; alignLeftRequest.RepeatCell.Range = new GridRange { SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3 }; alignLeftRequest.RepeatCell.Cell = new CellData { UserEnteredFormat = new CellFormat { HorizontalAlignment = "LEFT" } }; Request alignCenterRequest = new Request(); alignCenterRequest.RepeatCell = new RepeatCellRequest(); alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)"; alignCenterRequest.RepeatCell.Range = new GridRange { SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1 }; alignCenterRequest.RepeatCell.Cell = new CellData { UserEnteredFormat = new CellFormat { HorizontalAlignment = "Center" } }; Request resizeRequest = new Request(); resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest(); resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange { SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count }; BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest(); batch.Requests = new List <Request>(); batch.Requests.Add(alignLeftRequest); batch.Requests.Add(alignCenterRequest); batch.Requests.Add(resizeRequest); SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id); BatchUpdateSpreadsheetResponse responseResize = u.Execute(); } }