private void button2_Click(object sender, EventArgs e)
{
NotMain();
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest request = sheetsService.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
Data.ValueRange response = request.Execute();
//characters = new Dictionary<string, Character>();
foreach (var character in response.Values)
{
string player = character[0] as string;
string name = character[1] as string;
string race = character[2] as string;
int str = Convert.ToInt32(character[3]);
int dex = Convert.ToInt32(character[4]);
int intellegence = Convert.ToInt32(character[5]);
int level = Convert.ToInt32(character[6]);
int exp = Convert.ToInt32(character[7]);
characters.Add(name, new Character(name, player, race, str, dex, intellegence, level, exp));
}
int numChars = characters.Count - 1;
Random rand = new Random();
int r = rand.Next(0, numChars);
int r2 = rand.Next(0, numChars);
}
//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();
}
public async Task iLvl(string data)
{
bool valid = true;
if (data.Length > 3 || !data.All(char.IsDigit))
{
valid = false;
}
if (valid == false)
{
await ReplyAsync("Invalid parameter: **" + data + "**. Please type !halp to see a list of supported parameters."); return;
}
if (Utils.GetRowByDiscord(Program.context.User.ToString()) == 0)
{
await ReplyAsync("No user found, be sure you've added yourself with the !add *<Player Name>* command"); return;
}
range = "E" + Utils.GetRowByDiscord(Program.context.User.ToString());
Console.WriteLine(range);
requestbody = new Data.ValueRange();
requestbody.MajorDimension = "ROWS";
var info = new List <object>()
{
data
};
requestbody.Values = new List <IList <object> > {
info
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(requestbody, sheetid, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
await ReplyAsync("Your item level has been set to: **" + data + "**.");
}
//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 void UpdateWholesalerQueriesSheet(WholesalersRequestModel request,
List <StateTaxRegistrationInternal> internals,
string feinfileurl)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
var range = string.Empty;
range = @"WholesalerEnquiriesOne!A:CZ";
var valueRange = new ValueRange();
var oblist = new List <object>();
oblist.Add(DateTime.Now.ToShortDateString());
oblist.Add(String.IsNullOrEmpty(request.BusinessName) ? "": request.BusinessName);
oblist.Add(String.IsNullOrEmpty(request.ContactName) ? "": request.ContactName);
oblist.Add(String.IsNullOrEmpty(request.AddressLine1) ? "": request.AddressLine1);
oblist.Add(String.IsNullOrEmpty(request.AddressLine2) ? "": request.AddressLine2);
oblist.Add(String.IsNullOrEmpty(request.City) ? "": request.City);
oblist.Add(String.IsNullOrEmpty(request.State) ? "": request.State);
oblist.Add(String.IsNullOrEmpty(request.ZipCode) ? "": request.ZipCode);
oblist.Add(String.IsNullOrEmpty(request.BusinessPhoneNumber) ? "": request.BusinessPhoneNumber);
oblist.Add(String.IsNullOrEmpty(request.EmailAddress) ? "": request.EmailAddress);
oblist.Add(String.IsNullOrEmpty(request.ContactName) ? "": request.ContactName);
oblist.Add(String.IsNullOrEmpty(request.Subject) ? "": request.Subject);
oblist.Add(String.IsNullOrEmpty(request.Message) ? "": request.Message);
oblist.Add(String.IsNullOrEmpty(request.FederalBusinessTaxId) ? "": request.FederalBusinessTaxId);
oblist.Add(String.IsNullOrEmpty(feinfileurl) ? "": feinfileurl);
foreach (var item in internals)
{
oblist.Add(String.IsNullOrEmpty(item.StateSalesTaxId) ? "": item.StateSalesTaxId);
oblist.Add(String.IsNullOrEmpty(item.StateSelected) ? "": item.StateSelected);
oblist.Add(String.IsNullOrEmpty(item.UploadedUrl) ? "": item.UploadedUrl);
}
valueRange.Values = new List <IList <object> > {
oblist
};
Google.Apis.Sheets.v4.Data.ValueRange requestBody =
new Google.Apis.Sheets.v4.Data.ValueRange();
var appendRequest =
sheetsService.Spreadsheets.Values.Append(valueRange, _registrationsheetid, range);
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response = appendRequest.Execute();
}
public async Task Add(string name)
{
if (Utils.GetRowByDiscord(Program.context.User.ToString()) != 0)
{
await ReplyAsync("You have already added yourself."); return;
}
IList <IList <object> > data = new List <IList <object> >()
{
new List <object> {
Utils.UpperCaseIt(name.ToLower()), Program.context.User.ToString()
}
};
range = "B5";
requestbody = new Data.ValueRange();
requestbody.Values = data;
SpreadsheetsResource.ValuesResource.AppendRequest r = service.Spreadsheets.Values.Append(requestbody, sheetid, range);
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueinputoption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED);
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertdataoption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS);
r.ValueInputOption = valueinputoption;
r.InsertDataOption = insertdataoption;
r.AccessToken = credential.Token.AccessToken;
Data.AppendValuesResponse response = await r.ExecuteAsync();
await ReplyAsync("Successfully added!");
}
public IActionResult SpreadEdit(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "sa_spread_edit")] HttpRequest req, ILogger log)
{
_sheetsService = ConnectSpreadSheet();
String range = "シート1!B2";
string valueInputOption = "USER_ENTERED";
List <Data.ValueRange> updateData = new List <Data.ValueRange>();
var dataValueRange = new Data.ValueRange();
List <IList <object> > data = new List <IList <object> >();
data.Add(GetRandomList());
data.Add(GetRandomList());
data.Add(GetRandomList());
data.Add(GetRandomList());
data.Add(GetRandomList());
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
var request = _sheetsService.Spreadsheets.Values.BatchUpdate(requestBody, _editSpreadsheetId);
Data.BatchUpdateValuesResponse response = request.Execute();
return(new ObjectResult(JsonConvert.SerializeObject(response)));
}
public static string getColumnData(string range, 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.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "RaveReportAutomationUtility",
});
SpreadsheetsResource.ValuesResource.GetRequest request =
sheetsService.Spreadsheets.Values.Get(spreadsheetId, range);
Data.ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
if (values != null && values.Count > 0)
{
// System.Threading.Thread.Sleep(800);
foreach (var row in values) //Gets all the tags of the ROW A
{
var name = row.Select(p => p.ToString()).ToList(); // Converting generic type to string
string colData = string.Join("", name); // // Converting generic type to string
// Print columns A and E, which correspond to indices 0 and 4.
return(colData);
}
}
return(null);
}
public object writeData(string spreadsheetId, string tenBang, string phamViCell, IList <IList <object> > dataIn)
{
checkConnectServer chk = new checkConnectServer();
if (chk.checkConnectGoogle())
{
/*ĐÂY LÀ HÀM GHI DỮ LIỆU VÀO GOOGLE SHEET
* Hàm này cần đầu vào là ID bảng, Tên bảng, phạm vi điền (VD: A52 hoặc A5:A8 (Hàng ngang)) và mảng dữ liệu đầu vào.*/
UserCredential credential = GetCredential(ScopesWrite);
var sheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "VolunteerDTU"
});
Data.ValueRange requestBody = new Data.ValueRange();
requestBody.Values = dataIn;
string range = "'" + tenBang + "'!" + phamViCell;
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
return(response);
}
else
{
MessageBox.Show("Không có kết nối internet");
return(null);
}
}
public IActionResult SpreadAppend(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "sa_spread_append")] HttpRequest req, ILogger log)
{
_sheetsService = ConnectSpreadSheet();
string range = "シート1!B2";
var valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 2;
var insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 1;
Data.ValueRange requestBody = new Data.ValueRange();
List <IList <object> > data = new List <IList <object> >();
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
requestBody.Values = data;
SpreadsheetsResource.ValuesResource.AppendRequest request = _sheetsService.Spreadsheets.Values.Append(requestBody, _editSpreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
Data.AppendValuesResponse response = request.Execute();
return(new ObjectResult(JsonConvert.SerializeObject(response)));
}
public async Task <List <DetailDataViewModel> > ReadAsync(SpreadsheetsResource.ValuesResource valuesResource)
{
Google.Apis.Sheets.v4.Data.ValueRange response = await valuesResource.Get(SpreadsheetId, ReadRange).ExecuteAsync();
IList <IList <object> > values = response.Values;
DetailData detail = new DetailData();
if (values != null || values.Any())
{
//CABEÇALHO: var header = string.Join(" ", values.First().Select(r => r.ToString()));
//foreach (var row in values.Skip(1))
for (int i = 1; i < 4; i++)
{
var row = values[i];
detail.Data.Add(new DetailDataViewModel
{
Id = i,
CharName = row[0].ToString(),
Type = row[1].ToString(),
Skins = row[2].ToString(),
Skin_Spotlight = row[3].ToString(),
});
}
}
return(detail.Data);
}
private void PostToGoogle(IList <IList <object> > data)
{
// The ID of the spreadsheet to update.
string spreadsheetId = "14CQv-KsLjUweeZS9Am-dftJBlaGAiFGpwRJb8CK-tJQ";
// 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 = "GameData!A1:Y";
// How the input data should be interpreted.
GAppendRequest.ValueInputOptionEnum valueInputOption = GAppendRequest.ValueInputOptionEnum.USERENTERED;
// How the input data should be inserted.
GAppendRequest.InsertDataOptionEnum insertDataOption = GAppendRequest.InsertDataOptionEnum.INSERTROWS;
// TODO: Assign values to desired properties of `requestBody`:
GData.ValueRange requestBody = new GData.ValueRange();
requestBody.Values = data;
requestBody.MajorDimension = "COLUMNS";
GAppendRequest request = _sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
GData.AppendValuesResponse response = request.Execute();
// GData.AppendValuesResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
Console.WriteLine(JsonConvert.SerializeObject(response));
}
private static void test()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string ApplicationName = "Brawlhalla Stats";
UserCredential credential;
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.
string credPath = "token.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 = "1MwKJaBCy4xRbT0hKpKGOr6vfZHmzhy4uJ76nfD78YWA";
String range = "Ark1!A2:E";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
range = "Ark1!A2:Y";
string valueInputOption = "USER_ENTERED";
List <IList <object> > data = new List <IList <object> >();
// The new values to apply to the spreadsheet.
List <Data.ValueRange> updateData = new List <Data.ValueRange>();
var dataValueRange = new Data.ValueRange();
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
//request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
service.Spreadsheets.Values.Append(null, spreadsheetId, range);
Console.Read();
}
public static List <SpotifyInfo> GetAllSongsFromStaticSheetVN()
{
try
{
ServiceAccountCredential credential1;
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string serviceAccountEmail = "*****@*****.**";
string jsonfile = "trackingNewData.json";
string spreadsheetID = "1k0G4J_HXLzOvaOvoUPHt8m7S-ogMxaeF53SE6ZfgXfo";
string range = "Danh sách nhạc tổng!A2:K";
using (Stream stream = new FileStream(@jsonfile, FileMode.Open, FileAccess.Read, FileShare.Read))
{
credential1 = (ServiceAccountCredential)
GoogleCredential.FromStream(stream).UnderlyingCredential;
var initializer = new ServiceAccountCredential.Initializer(credential1.Id)
{
User = serviceAccountEmail,
Key = credential1.Key,
Scopes = Scopes
};
credential1 = new ServiceAccountCredential(initializer);
}
var serices = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential1,
ApplicationName = ApplicationName,
});
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest request = serices.Spreadsheets.Values.Get(spreadsheetID, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
List <SpotifyInfo> listSongs = new List <SpotifyInfo>();
foreach (var item in values)
{
if (item.Count >= 6)
{
SpotifyInfo song = new SpotifyInfo();
song.TrackTitle = item[4].ToString();
song.Artists = item[5].ToString();
song.Range = "I" + (values.IndexOf(item) + 2).ToString() + ":" + "J" + (values.IndexOf(item) + 2).ToString();
listSongs.Add(song);
}
}
return(listSongs);
}
catch (Exception ex)
{
return(null);
}
}
public static List <Object> ReadHeaders(string spreadsheetId, SheetsService service)
{
Data.ValueRange response = ReadSpreadsheet(spreadsheetId, "Sheet1!1:1", service);
if (response.Values == null)
{
return(new List <Object> ());
}
return(response.Values[0].ToList <Object>());
}
private void WriteToSheet(SheetsService service, string range, IList <IList <object> > toReturn)
{
Data.ValueRange valueRange = new Data.ValueRange();
valueRange.Values = toReturn;
// Append the above record...
var appendRequest = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = appendRequest.Execute();
}
public void UpdateQueriesSheet(string name,
string emailaddress,
string phonenumber,
string subject,
string message,
string requesttype)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
var range = string.Empty;
if (requesttype.Equals("Wholesalers"))
{
range = @"WholesalerEnquiries!A:F";
}
else
{
range = @"CustomerQueries!A:F";
}
var valueRange = new ValueRange();
var oblist = new List <object>()
{
String.IsNullOrEmpty(name) ? "": name,
String.IsNullOrEmpty(emailaddress)? "" : emailaddress,
String.IsNullOrEmpty(phonenumber)? "" : phonenumber,
String.IsNullOrEmpty(subject)? "" : subject,
String.IsNullOrEmpty(message)? "" : message,
DateTime.Now.ToShortDateString()
};
valueRange.Values = new List <IList <object> > {
oblist
};
Google.Apis.Sheets.v4.Data.ValueRange requestBody =
new Google.Apis.Sheets.v4.Data.ValueRange();
var appendRequest =
sheetsService.Spreadsheets.Values.Append(valueRange, _registrationsheetid, range);
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response = appendRequest.Execute();
}
public static Data.ValueRange ReadSpreadsheet(string spreadsheetId, string range, SheetsService service)
{
//Create read request
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
Console.WriteLine("Reading range " + range + " from spreadsheet...");
//Execute the request
Data.ValueRange response = request.Execute();
return(response);
}
/// <summary>
/// This method changes entries on the spreadsheet. (untested)
/// </summary>
public static void UpdateEntry(string cell, List <object> inputs, int index)
{
var range = $"{sheets[index]}!{cell}";
var valueRange = new Google.Apis.Sheets.v4.Data.ValueRange();
valueRange.Values = new List <IList <object> > {
inputs
};
var updateRequest = service.Spreadsheets.Values.Update(valueRange, SpreadsheetID, range);
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var updateResponse = updateRequest.Execute();
}
/// <summary>
/// This method creates entries on the spreadsheet (will not overwrite entries).
/// </summary>
public static void CreateEntry(string leftColumn, string rightColumn, List <object> inputs, int index)
{
var sheet = sheets[index];
var range = $"{sheet}!{leftColumn}:{rightColumn}";
var valueRange = new Google.Apis.Sheets.v4.Data.ValueRange();
valueRange.Values = new List <IList <object> > {
inputs
};
var appendRequest = service.Spreadsheets.Values.Append(valueRange, SpreadsheetID, range);
appendRequest.ValueInputOption = Google.Apis.Sheets.v4.SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendResponse = appendRequest.Execute();
}
public async Task Alt(string name, string ilvl)
{
range = "H" + Utils.GetRowByDiscord(Program.context.User.ToString());
if (Utils.GetRowByDiscord(Program.context.User.ToString()) == 0)
{
await ReplyAsync("No user found, be sure you've added a main character with the !add *<Player Name>* command"); return;
}
if (Utils.GetRowByAlt(1, name) != 0 || Utils.GetRowByAlt(2, name) != 0)
{
await ReplyAsync("You've already added this alt."); return;
}
if (ilvl.All(char.IsDigit) == false || ilvl.Length > 3)
{
await ReplyAsync("Invalid item level."); return;
}
if (Utils.CellEmpty("H" + Utils.GetRowByDiscord(Program.context.User.ToString())) == false && Utils.CellEmpty("J" + Utils.GetRowByDiscord(Program.context.User.ToString())) == false)
{
await ReplyAsync("You can only have up to two alt's, if you need an alt removed contact an Officer."); return;
}
if (Utils.GetRowByAlt(1, name) == 0)
{
if (Utils.CellEmpty(range) == true)
{
range = "H" + Utils.GetRowByDiscord(Program.context.User.ToString());
}
else
{
if (Utils.GetRowByAlt(2, name) == 0)
{
range = "J" + Utils.GetRowByDiscord(Program.context.User.ToString());
}
}
}
IList <IList <object> > data = new List <IList <object> >()
{
new List <object> {
Utils.UpperCaseIt(name.ToLower()), ilvl
}
};
requestbody = new Data.ValueRange();
requestbody.MajorDimension = "ROWS";
requestbody.Values = data;
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(requestbody, sheetid, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result = update.Execute();
await ReplyAsync("Successfully added **" + Utils.UpperCaseIt(name.ToLower()) + "** as an alt.");
}
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);
}
// Button 1 "Confirm"
private void button1_Click(object sender, EventArgs e) //TODO: have this send input to spreadsheet
{
NotMain();
string player = txt_playerName.Text as string;
string name = txt_characterName.Text as string;
string race = txt_race.Text as string;
int str = Convert.ToInt32(txt_strength.Text);
int dex = Convert.ToInt32(txt_dexterity.Text);
int intellegence = Convert.ToInt32(txt_intelligence.Text);
int level = 1;
int exp = 0;
characters.Add(name, new Character(name, player, race, str, dex, intellegence, level, exp));
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 0; // TODO: Update placeholder value.
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 0; // TODO: Update placeholder value.
// TODO: Assign values to desired properties of `requestBody`:
Data.ValueRange requestBody = new Data.ValueRange();
var characterResponses = new List <IList <Object> >();
foreach (var character in characters)
{
var characterResponse = new List <object>();
characterResponse.Add(character.Value.charName);
characterResponse.Add(character.Value.plyrName);
characterResponse.Add(character.Value.strength.ToString());
characterResponse.Add(character.Value.intelligence.ToString());
characterResponse.Add(character.Value.dexterity.ToString());
characterResponse.Add(character.Value.level.ToString());
characterResponse.Add(character.Value.xp.ToString());
characterResponses.Add(characterResponse);
}
requestBody.Values = characterResponses;
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.AppendValuesResponse response = request.Execute();
}
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 bool Search(string productid)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
var range = @"ProductIdLookup!A1";
var valueRange = new ValueRange();
string searchstring = "=MATCH(\"#-#$\",ProductIds!A:A, 0)";
searchstring = searchstring.Replace("#-#$", productid.Trim());
var oblist = new List <object>()
{
searchstring
};
valueRange.Values = new List <IList <object> > {
oblist
};
Google.Apis.Sheets.v4.Data.ValueRange requestBody =
new Google.Apis.Sheets.v4.Data.ValueRange();
var appendRequest =
sheetsService.Spreadsheets.Values.Append(valueRange, _registrationsheetid, range);
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response =
appendRequest.Execute();
string query = response.Updates.UpdatedRange;
SpreadsheetsResource.ValuesResource.GetRequest getrequest =
sheetsService.Spreadsheets.Values.Get(_registrationsheetid, query);
ValueRange r = getrequest.Execute();
object s = r.Values[0][0];
return(!(s as string).Contains("#"));
}
public async Task Spec([Remainder, Summary("Set your main specialization ingame.")] string data)
{
bool valid = false;
List <string> specs = new List <string>()
{
"Arms", "Fury", "Prot", "Protection", "Arcane", "Fire", "Frost", "Discipline", "Holy", "Shadow", "Beast Mastery", "Marskman", "Survival", "Affliction", "Demonology", "Destruction", "Brewmaster", "Mistweaver", "Windwalker", "Havoc", "Vengeance", "Blood", "Unholy", "Retribution", "Balance", "Feral", "Guardian", "Restoration", "Assassination", "Outlaw", "Subtlety", "Elemental", "Enhancement"
};
foreach (string spec in specs)
{
if (data.ToLower() == "prot")
{
valid = true; data = "Protection"; return;
}
if (data.ToLower() == spec.ToLower())
{
valid = true; data = spec;
}
}
if (valid == false)
{
await ReplyAsync("Invalid parameter: **" + data + "**. Please type !halp to see a list of supported parameters."); return;
}
if (Utils.GetRowByDiscord(Program.context.User.ToString()) == 0)
{
await ReplyAsync("No user found, be sure you've added yourself with the !add *<Player Name>* command"); return;
}
range = "G" + Utils.GetRowByDiscord(Program.context.User.ToString());
requestbody = new Data.ValueRange();
requestbody.MajorDimension = "ROWS";
var info = new List <object>()
{
data
};
requestbody.Values = new List <IList <object> > {
info
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(requestbody, sheetid, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
await ReplyAsync("Your spec has been set to **" + data + "**.");
}
public static void AppendNewSongs(List <SpotifyInfo> listSongs, int countRows)
{
try
{
var service = UserCredential();
IList <SpotifyInfo> dataList = listSongs;
List <Google.Apis.Sheets.v4.Data.ValueRange> data = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
ValueRange valueDataRange = new ValueRange()
{
MajorDimension = "ROWS"
};
valueDataRange.Values = new List <IList <object> >();
valueDataRange.Range = range;
for (int i = 0; i < dataList.Count; i++)
{
IList <object> list = new List <object> {
countRows + (i + 1), dataList[i].TrackTitle, dataList[i].Code,
dataList[i].Artists, dataList[i].LinkSpotify, dataList[i].Genres, dataList[i].Country, dataList[i].ReleaseDate, dataList[i].Popularity, dataList[i].StreamCount
};
valueDataRange.Values.Add(list);
}
data.Add(valueDataRange);
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 1; // TODO: Update placeholder value.
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 1; // TODO: Update placeholder value.
// TODO: Assign values to desired properties of `requestBody`:
Google.Apis.Sheets.v4.Data.ValueRange requestBody = data[0];
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response = request.Execute();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void Update(String range, ValueRange Content)
{
Google.Apis.Sheets.v4.Data.ValueRange requestBody = new Google.Apis.Sheets.v4.Data.ValueRange();
var values = new List <IList <object> >();
values.Add(new List <object> {
"Jacek", "Pawel"
});
requestBody.Values = values;
requestBody.Range = "A2:A3";
var request = connection.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
Console.WriteLine(JsonConvert.SerializeObject(response));
}
static IList <IList <Object> > ConnectToSheets()
{
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;
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
String spreadsheetId = "1ZhMmd8DAGt28MWlmy1QrWHIm3d0ln3Zi4x66Qs4bmJY";
String sheet = DateTime.Now.ToString("dddd");
String range = DateTime.Now.ToString("dddd") + "!A1:M10";
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
Google.Apis.Sheets.v4.Data.ValueRange response = request.Execute();
Console.WriteLine(JsonConvert.SerializeObject(response));
IList <IList <Object> > values = response.Values;
return(values);
}
/// Inserts the data into specific sheet name at row # provided
private static void InsertData(SheetsService service, string sheetName, int row, List <IList <object> > data)
{
String range = sheetName + "!A" + row + ":Y";
string valueInputOption = "USER_ENTERED";
// The new values to apply to the spreadsheet.
List <Google.Apis.Sheets.v4.Data.ValueRange> updateData = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
var dataValueRange = new Google.Apis.Sheets.v4.Data.ValueRange();
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
var request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse response = request.Execute();
}
