//Insert the initial Setup Information: public static void insertSetupInfoColumn(string Range, string transactionName = "Transaction Name", string scriptName = "Script Name", string Min = "Min", string Avg = "Avg", string Max = "Max", string Std = "Std", string Count = "Count", string Bound1 = "Bound1", string Bound2 = "Bound2", string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE") { // The A1 notation of a range to search for a logical table of data. // Values will be appended after the last row of the table. //string range = "1 - Performance Test Results"; // TODO: Update placeholder value. // string spreadsheetId = spreadsheetIDTextbox.Text; //Impliment this for the input entered in the textbox SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = GetCredential(), ApplicationName = "RaveReportAutomationUtility", }); Data.ValueRange valueRange = new Data.ValueRange(); valueRange.MajorDimension = "ROWS"; // var output = Math.Round(Convert.ToDouble(getOverallWorkflowMetricsData(nameTag, "Response time[s]", "Avg")), 3); var oblist = new List <Object>() { transactionName, scriptName, Min, Avg, Max, Std, Count, Bound1, Bound2 }; //Try to make valueRange.Values = new List <IList <object> > { oblist }; SpreadsheetsResource.ValuesResource.UpdateRequest update = sheetsService.Spreadsheets.Values.Update(valueRange, spreadsheetId, Range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED; Data.UpdateValuesResponse result = update.Execute(); }
//Update headers (i.e add extra headers if any in the appending object) public static void UpdateHeadersInSpreadsheet(string spreadsheetId, SheetsService service, List <Object> headers) { Data.ValueRange requestBody = new Data.ValueRange(); List <Object> headersInSpreadsheet = ReadHeaders(spreadsheetId, service); //Check if Headers list is equal to headers if (headers.All(i => headersInSpreadsheet.Contains(i))) { return; } //Update the headers headersInSpreadsheet = headersInSpreadsheet.Union(headers).ToList(); requestBody.Values = new List <IList <Object> > { headersInSpreadsheet }; //Create update request SpreadsheetsResource.ValuesResource.UpdateRequest request = service.Spreadsheets.Values.Update(requestBody, spreadsheetId, "Sheet1!1:1"); request.ValueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2; Console.WriteLine("Updating headers..."); //Execute request Data.UpdateValuesResponse response = request.Execute(); return; }
public static Data.UpdateValuesResponse UpdateSpreadsheet(string spreadsheetId, string range, SheetsService service, MyData dataObject) { Data.ValueRange requestBody = new Data.ValueRange(); requestBody.Values = ConvertObjectToList(dataObject); //Create update request SpreadsheetsResource.ValuesResource.UpdateRequest request = service.Spreadsheets.Values.Update(requestBody, spreadsheetId, range); request.ValueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2; Console.WriteLine("Updating range " + range + " in spreadsheet..."); //Execute the request Data.UpdateValuesResponse response = request.Execute(); return(response); }
public static void Main(string[] args) { string x = "0"; while (x != "6") { Console.WriteLine("Press: \n1. Reading\n2. Appending\n3. Updating\n4. Clearing\n5. Add/Update Headers\n6. Exit"); x = Console.ReadLine(); switch (x) { case "1": Data.ValueRange readResponse = ReadSpreadsheet(spreadsheetId, range, service); if (readResponse.Values == null) { Console.WriteLine("Sorry! No data read from this range."); break; } Console.WriteLine("Read " + readResponse.Values.Count + " rows."); break; case "2": Data.AppendValuesResponse appendResponse = AppendSpreadsheet(spreadsheetId, range, service, GetDataAsObject()); Console.WriteLine("Appended! Updated " + appendResponse.Updates.UpdatedCells + " cells."); break; case "3": Data.UpdateValuesResponse updateResponse = UpdateSpreadsheet(spreadsheetId, range, service, GetDataAsObject()); Console.WriteLine("Updated " + updateResponse.UpdatedRows + " rows and " + updateResponse.UpdatedColumns + " columns."); break; case "4": Data.ClearValuesResponse clearResponse = Clearspreadsheet(spreadsheetId, range, service); Console.WriteLine("Cleared range: " + clearResponse.ClearedRange); break; case "5": UpdateHeadersInSpreadsheet(spreadsheetId, service, GetHeaders()); Console.WriteLine("Updated the headers!"); break; default: Console.WriteLine("Please enter a valid key."); break; } ; } }
public void SetValue(string spreadsheetId, string range, IList <IList <object> > values) { var sheetsService = GetSheetsService(); // TODO: Assign values to desired properties of `requestBody`. All existing // properties will be replaced: Google.Apis.Sheets.v4.Data.ValueRange requestBody = new Google.Apis.Sheets.v4.Data.ValueRange { Values = values, }; SpreadsheetsResource.ValuesResource.UpdateRequest request = sheetsService.Spreadsheets.Values.Update(requestBody, spreadsheetId, range); request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED; // To execute asynchronously in an async method, replace `request.Execute()` as shown: Google.Apis.Sheets.v4.Data.UpdateValuesResponse response = request.Execute(); // Data.UpdateValuesResponse response = await request.ExecuteAsync(); // TODO: Change code below to process the `response` object: //Console.WriteLine(JsonSerializer.Serialize(response)); }
//Writing Data Func public static void SendDataPlayerSub(ValueRange Data, String Range) { SpreadsheetsResource.ValuesResource.UpdateRequest request = ServiceCred().Spreadsheets.Values.Update(Data, spreadsheetId, Range); request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; Data.UpdateValuesResponse response = request.Execute(); }
public static void rowHeadingTags(int rowIndex, string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE", string sheetName = "Sheet1") { // The A1 notation of a range to search for a logical table of data. // Values will be appended after the last row of the table. //string range = "1 - Performance Test Results"; // TODO: Update placeholder value. // string spreadsheetId = spreadsheetIDTextbox.Text; //Impliment this for the input entered in the textbox SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = GetCredential(), ApplicationName = "RaveReportAutomationUtility", }); Data.ValueRange valueRange = new Data.ValueRange(); valueRange.MajorDimension = "ROWS"; // var output = Math.Round(Convert.ToDouble(getOverallWorkflowMetricsData(nameTag, "Response time[s]", "Avg")), 3); var oblist = new List <Object>() { "Min\n(Sec)" }; oblist.Add("Avg\n(Sec)"); oblist.Add("Max\n(Sec)"); oblist.Add("Std"); oblist.Add("Count"); oblist.Add("%Request\n(<2 sec)"); valueRange.Values = new List <IList <object> > { oblist }; bool loop = true; char rangeConcat = 'B'; do { // Gets the index of the current row where the Metrics is inserted! string nullColumnIndexRange = string.Concat(sheetName, "!"); //Range to be concatinated with string rangeStr = rangeConcat.ToString(); string finalRange = string.Concat(nullColumnIndexRange, rangeStr, rowIndex + 1);//Concatinates the string to produce the range string for the right column to enter the data string colData = getColumnData(finalRange);//Loops through the google docs sheet to find the emprty box to store the metrics if (colData == null) { loop = false; //If an empty column is found the Number is entered in that field. SpreadsheetsResource.ValuesResource.UpdateRequest update = sheetsService.Spreadsheets.Values.Update(valueRange, spreadsheetId, finalRange); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED; Data.UpdateValuesResponse result = update.Execute(); } rangeConcat++; } while (loop == true); /*SpreadsheetsResource.ValuesResource.AppendRequest append = sheetsService.Spreadsheets.Values.Append(valueRange, spreadsheetId, range); * append.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED; * Data.AppendValuesResponse result2 = append.Execute();*/ }