Beispiel #1
0
        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();
        }
Beispiel #3
0
        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;
        }
Beispiel #5
0
        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();
        }
Beispiel #6
0
        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!");
        }
Beispiel #7
0
        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);
        }
Beispiel #9
0
        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);
            }
        }
Beispiel #10
0
        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)));
        }
Beispiel #11
0
        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);
        }
Beispiel #12
0
    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));
    }
Beispiel #13
0
        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>());
 }
Beispiel #16
0
        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();
        }
Beispiel #17
0
        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();
    }
Beispiel #21
0
        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);
        }
Beispiel #23
0
        // 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;
                }
                ;
            }
        }
Beispiel #25
0
        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("#"));
        }
Beispiel #26
0
        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);
            }
        }
Beispiel #28
0
        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));
        }
Beispiel #29
0
        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();
        }