//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();*/
}
