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