Beispiel #1
0
        private List <TmpObject> Sort(BatchGetValuesResponse googleResponse, int course)
        {
            var subjectsResponse = googleResponse.ValueRanges[1].Values == null
                ? new List <IList <object> >()
                : googleResponse.ValueRanges[1].Values;
            var unsortedObjects = googleResponse.ValueRanges[0].Values
                                  ?.Zip(subjectsResponse, (x, y) => new { Time = x, Subjects = y })
                                  ?.Where(x => x.Subjects.Count > 0)
                                  .ToList();
            var sortedSubjects = new List <TmpObject>();

            for (var i = 0; i < unsortedObjects.Count; i++)
            {
                for (var j = 0; j < unsortedObjects[i].Subjects.Count; j++)
                {
                    if (unsortedObjects[i].Subjects[j].ToString().Length > 1)
                    {
                        sortedSubjects.Add(new TmpObject
                        {
                            Content = unsortedObjects[i].Subjects[j].ToString(),
                            Group   = $"11-{Converter.NormalizeGroupNumber(course)}0{j + 1}",
                            Time    = unsortedObjects[i].Time.FirstOrDefault().ToString(),
                            Course  = course
                        });
                    }
                }
            }
            return(sortedSubjects);
        }
Beispiel #2
0
        private static Schedule ConversionToSchedule(BatchGetValuesResponse response)
        {
            IList <ValueRange> values = response.ValueRanges;

            var temp = new Schedule();

            int j = 0;

            foreach (var row in values)
            {
                temp.WeekSchedule.Add(new DailySchedule(j));
                for (int i = 0; i < row.Values.Count; i++)
                {
                    if (row.Values[i].Count != 0)
                    {
                        temp.WeekSchedule[j].Lessons.Add(new Lesson(StaticScheduleInfo.TimeLessons[i], row.Values[i][0].ToString()));
                    }
                    else
                    {
                        temp.WeekSchedule[j].Lessons.Add(new Lesson(StaticScheduleInfo.TimeLessons[i], " "));
                    }
                }
                j++;
            }
            return(temp);
        }
Beispiel #3
0
        public IList <ValueRange> getWorksheets(string SpreadsheetId, List <string> Ranges)
        {
            SpreadsheetsResource.ValuesResource.BatchGetRequest Request = Service.Spreadsheets.Values.BatchGet(SpreadsheetId);
            Request.Ranges = Ranges;
            BatchGetValuesResponse Response = Request.Execute();

            return(Response.ValueRanges);
        }
        public BatchGetValuesResponse GetValue(string cell, string sheetName = "")
        {
            string[] cells    = cell.Split('&');
            string[] rangMult = new string[cells.Length];
            for (int i = 0; i < cells.Length; i++)
            {
                rangMult[i] = sheetName + "!" + cells[i];
            }
            //
            SpreadsheetsResource.ValuesResource.BatchGetRequest BatchGetRequest = service.Spreadsheets.Values.BatchGet(spreadSheetId);
            BatchGetRequest.Ranges = rangMult;
            BatchGetValuesResponse response = BatchGetRequest.Execute();

            return(response);
        }
Beispiel #5
0
        private IList <IList <object> > GetRange(params string[] range)
        {
            request        = data.MyPage.Service.Spreadsheets.Values.BatchGet(data.MyPage.SpreadsheetId);
            request.Ranges = new Repeatable <string>(range);
            IList <IList <object> > result;

            try
            {
                response = request.Execute();
                result   = response.ValueRanges.SelectMany(x => x.Values).ToList();
                return(result);
            }
            catch (Exception)
            {
                Console.WriteLine($"Google Sheet read error from {range[0]}");
                return(null);
            }
        }
Beispiel #6
0
        /// <summary>
        /// Using the set of <c>Query</c> objects, executes a batch query on the Google Sheets API and stores the returned values in the <c>Query</c>'s Data attributes.
        /// </summary>
        /// <param name="service"></param>
        /// <param name="workbookID"></param>
        /// <param name="queries">The set of queries to be executed. All are expected to have the same <c>Orientation</c> value as <paramref name="dimension"/>.</param>
        /// <returns></returns>
        /// <exception cref="GoogleSheetsQueryFailedException"></exception>
        private void ExecuteBatchQuery(SheetsService service, string workbookID, MajorDimensionEnum dimension, IList <Query> queries)
        {
            try
            {
                if (queries.Count == 0)
                {
                    return;
                }

                BatchGetRequest request = service.Spreadsheets.Values.BatchGet(workbookID);
                request.Ranges         = queries.Select(q => q.ToString()).ToList();
                request.MajorDimension = dimension;

                BatchGetValuesResponse response = request.Execute();

                int i = 0;
                foreach (Query query in queries)
                {
                    if (response.ValueRanges.ElementAtOrDefault(i).Values == null)
                    {
                        if (query.AllowNullData)
                        {
                            query.Data = new List <IList <object> >();
                        }
                        else
                        {
                            throw new GoogleSheetsQueryReturnedNullException(query.Sheet);
                        }
                    }
                    else
                    {
                        query.Data = response.ValueRanges.ElementAtOrDefault(i).Values;
                    }

                    i++;
                }
            }
            catch (Exception ex)
            {
                throw new GoogleSheetsQueryFailedException(string.Join(", ", queries.Select(q => q.Sheet)), ex);
            }
        }
Beispiel #7
0
        private static int GetNextEmptyRow(Sheet sheet)
        {
            Dictionary <string, string> columnLetters = GetTicketColumnLetters(sheet.Properties.SheetId ?? -1);

            var request = service.Spreadsheets.Values.BatchGet(Config.spreadsheetID);

            request.Ranges = new []
            {
                $"{sheet.Properties.Title}!{columnLetters["ticketNumber"]}:{columnLetters["ticketNumber"]}",
                $"{sheet.Properties.Title}!{columnLetters["channel"]}:{columnLetters["channel"]}",
                $"{sheet.Properties.Title}!{columnLetters["user"]}:{columnLetters["user"]}",
                $"{sheet.Properties.Title}!{columnLetters["timeCreated"]}:{columnLetters["timeCreated"]}",
                $"{sheet.Properties.Title}!{columnLetters["lastMessage"]}:{columnLetters["lastMessage"]}",
                $"{sheet.Properties.Title}!{columnLetters["summary"]}:{columnLetters["summary"]}"
            };
            request.MajorDimension = SpreadsheetsResource.ValuesResource.BatchGetRequest.MajorDimensionEnum.ROWS;
            BatchGetValuesResponse response = request.Execute();

            return(response.ValueRanges.Select(x => x.Values.Count).ToArray().Max() + 1);
        }
    private static void Retrieving(string id, string sheetName, Action <IDictionary <int, Dictionary <int, string> > > callBack)
    {
        Spreadsheet   spreadSheetData = service.Spreadsheets.Get(id).Execute();
        IList <Sheet> sheets          = spreadSheetData.Sheets;

        if ((sheets == null) || (sheets.Count <= 0))
        {
            Debug.LogError("Not found any data!");
            return;
        }

        List <string> ranges = new List <string>();

        ranges.Add(sheetName);

        SpreadsheetsResource.ValuesResource.BatchGetRequest request = service.Spreadsheets.Values.BatchGet(id);
        request.Ranges = ranges;
        BatchGetValuesResponse response = request.Execute();

        foreach (ValueRange valueRange in response.ValueRanges)
        {
            string n = valueRange.Range.Split('!')[0];

            if (n[0] == "'"[0])
            {
                n = n.Remove(0, 1);
                n = n.Remove(n.Length - 1);
            }
            //Debug.Log(n);
            //Create json file
            if (n.Equals(sheetName))
            {
                GetValue(valueRange, callBack);
            }
        }
    }
Beispiel #9
0
        public async Task StoreAutoAsync(bool verbose = false)
        {
            int       _purchases = 0;
            bool      _purchased = false;
            var       storeWatch = System.Diagnostics.Stopwatch.StartNew();
            GridRange range      = Program.GetTabRange(Program.EDKPTabs.Main);

            while (true)
            {
                List <Request> sortRequest = new List <Request>
                {
                    new Request
                    {
                        SortRange = new SortRangeRequest
                        {
                            Range     = range,
                            SortSpecs = new List <SortSpec>
                            {
                                new SortSpec
                                {
                                    DimensionIndex = 1,
                                    SortOrder      = "DESCENDING"
                                }
                            }
                        }
                    }
                };
                await GoogleSheetsHelper.Instance.TransactionAsync(sortRequest);

                string _name;
                _purchased = false;
                List <string> ranges = new List <string>
                {
                    Config.Global.DKPMainTab,
                    Config.Global.DKPRequestsTab,
                    Config.Global.DKPStoreTab
                };

                BatchGetValuesResponse response = await GoogleSheetsHelper.Instance.BatchGetAsync(ranges);

                if (response.ValueRanges == null || response.ValueRanges.Count < ranges.Count)
                {
                    await ReplyAsync("Error reading spreadsheet, please restart.");

                    break;
                }
                ValueRange dkpResult  = response.ValueRanges[0];
                ValueRange reqResult  = response.ValueRanges[1];
                ValueRange itemResult = response.ValueRanges[2];
                if (dkpResult.Values != null && dkpResult.Values.Count > 0 && reqResult.Values != null && reqResult.Values.Count > 0)
                {
                    foreach (IList <object> player in dkpResult.Values)
                    {
                        _name = player[0].ToString();
                        foreach (IList <object> request in reqResult.Values)
                        {
                            if (request[0].ToString().Equals(_name))
                            {
                                var result = await Program.ProcessPurchase(request[1].ToString(), _name, 1, itemResult, dkpResult, reqResult);

                                if (result.Item1)
                                {
                                    await ReplyAsync(result.Item2);

                                    _purchased = true;
                                    ++_purchases;
                                    break;
                                }
                                else if (verbose)
                                {
                                    await ReplyAsync(result.Item2);
                                }
                            }
                        }
                        if (_purchased)
                        {
                            break;
                        }
                    }
                }

                if (!_purchased)
                {
                    break;
                }
                await Task.Delay(Config.Global.Commands.Store.DelayMS);
            }
            storeWatch.Stop();

            await ReplyAsync($"Loot distribution has been processed with a total of {_purchases} purchases made over {storeWatch.ElapsedMilliseconds/1000} seconds.");
        }
    private void DownloadToJson()
    {
        //Validate input
        if (string.IsNullOrEmpty(spreadSheetKey))
        {
            Debug.LogError("spreadSheetKey can not be null!");
            return;
        }

        Debug.Log("Start downloading from key: " + spreadSheetKey);

        //Authenticate
        progressMessage = "Authenticating...";
        var service = new SheetsService(new BaseClientService.Initializer()
        {
            HttpClientInitializer = GetCredential(),
            ApplicationName       = appName,
        });

        progress = 5;
        EditorUtility.DisplayCancelableProgressBar("Processing", progressMessage, progress / 100);
        progressMessage = "Get list of spreadsheets...";
        EditorUtility.DisplayCancelableProgressBar("Processing", progressMessage, progress / 100);

        Spreadsheet   spreadSheetData = service.Spreadsheets.Get(spreadSheetKey).Execute();
        IList <Sheet> sheets          = spreadSheetData.Sheets;


        //if((feed == null)||(feed.Entries.Count <= 0))
        if ((sheets == null) || (sheets.Count <= 0))
        {
            Debug.LogError("Not found any data!");
            progress = 100;
            EditorUtility.ClearProgressBar();
            return;
        }

        progress = 15;

        //For each sheet in received data, check the sheet name. If that sheet is the wanted sheet, add it into the ranges.
        List <string> ranges = new List <string>();

        foreach (Sheet sheet in sheets)
        {
            if ((wantedSheetNames.Count <= 0) || (wantedSheetNames.Contains(sheet.Properties.Title)))
            {
                ranges.Add(sheet.Properties.Title);
            }
        }

        SpreadsheetsResource.ValuesResource.BatchGetRequest request = service.Spreadsheets.Values.BatchGet(spreadSheetKey);
        request.Ranges = ranges;
        BatchGetValuesResponse response = request.Execute();

        //For each wanted sheet, create a json file
        foreach (ValueRange valueRange in response.ValueRanges)
        {
            string Sheetname = valueRange.Range.Split('!')[0];
            progressMessage = string.Format("Processing {0}...", Sheetname);
            EditorUtility.DisplayCancelableProgressBar("Processing", progressMessage, progress / 100);
            //Create json file
            CreateJsonFile(Sheetname, outputDir, valueRange);
            if (wantedSheetNames.Count <= 0)
            {
                progress += 85 / (response.ValueRanges.Count);
            }
            else
            {
                progress += 85 / wantedSheetNames.Count;
            }
        }
        progress = 100;
        AssetDatabase.Refresh();

        Debug.Log("Download completed.");
    }
        public async Task <(DataPool DataPool, Dictionary <string, Dictionary <string, string> > AvailableSheetDictionary)> LoadAll()
        {
            var sw = new System.Diagnostics.Stopwatch();

            sw.Start();
            service = await OpenSheet();

            Debug.Log($"OpenSheet() Elapsed: {sw.ElapsedMilliseconds} ms");


            var availableSheetsDict    = GetAvailableSheetProperties(service).ToDictionary(e => e["Title"]);
            MasterBuilderConfig config = fsWrapper.LoadConfigClass(configClassName);
            List <Type>         availableTableTypes = config.MasterTables()
                                                      .Where(e =>
            {
                var contains = availableSheetsDict.ContainsKey(e.Name);
                if (!contains)
                {
                    Debug.LogError($"Error: SheetNotFound. SheetName: {e.Name} URL:{GetSheetURL()}");
                }
                return(contains);
            }).ToList();
            List <string> availableSheets = availableTableTypes
                                            .Select(e => e.Name)
                                            .ToList();
            List <string> loadRanges = availableSheets
                                       .Select(e => $"{e}!A1:Z").ToList();

            ValuesResource.BatchGetRequest request = service.Spreadsheets.Values.BatchGet(spreadSheetId);
            request.Ranges = loadRanges;

            Debug.Log($"Send request to Google... Elapsed: {sw.ElapsedMilliseconds} ms");
            BatchGetValuesResponse response = request.Execute();

            Debug.Log($"Send request to Google... done. Elapsed: {sw.ElapsedMilliseconds} ms");
            var masterData = new MasterData();

            if (response != null && response.ValueRanges.Count > 0)
            {
                var tableCount = availableSheets.Count;
                for (var i = 0; i < tableCount; i++)
                {
                    var table     = response.ValueRanges[i];
                    var tableName = availableSheets[i];
                    var dataType  = availableTableTypes[i];

                    Debug.Log($"deserialize table({i + 1} / {tableCount}) tableName: {tableName} start. Elapsed: {sw.ElapsedMilliseconds} ms");

                    var            recordCount = table.Values.Count - 1;
                    IList <string> columns     = table.Values.FirstOrDefault().Select(e => e.ToString()).ToList();
                    if (recordCount < 1 || columns == null || columns.Count < 1)
                    {
                        continue;
                    }
                    var propertyDict = BuildPropertyDictionary(dataType);
                    var fieldDict    = BuildFieldAttributesDictionary(dataType);


                    var tasks = table.Values.Skip(1).Select((e, i) => Task.Run(() => {
                        var recordIndex = i - 1;
                        return(BuildRecord(columns, e.Select(e2 => e2.ToString()).ToList(), propertyDict, fieldDict, dataType, recordIndex));
                    }));


                    var rawRecords = await Task.WhenAll(tasks.ToArray());

                    masterData.Set(dataType, rawRecords.AsEnumerable());
                    Debug.Log($"deserialize table({i + 1} / {tableCount}) tableName: {tableName} done. Elapsed: {sw.ElapsedMilliseconds} ms");
                }
            }

            return(DataPool : masterData, AvailableSheetDictionary : availableSheetsDict);
        }
        public void DownloadToJson()
        {
            //Validate input
            if (string.IsNullOrEmpty(spreadSheetKey))
            {
                Debug.LogError("spreadSheetKey can not be null!");
                return;
            }

            Debug.Log("Start downloading from key: " + spreadSheetKey);

            //Authenticate
            Debug.Log("Authenticating...");
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = GetCredential(),
                ApplicationName       = appName,
            });

            Spreadsheet   spreadSheetData = service.Spreadsheets.Get(spreadSheetKey).Execute();
            IList <Sheet> sheets          = spreadSheetData.Sheets;


            //if((feed == null)||(feed.Entries.Count <= 0))
            if ((sheets == null) || (sheets.Count <= 0))
            {
                Debug.LogError("Not found any data!");
                return;
            }

            //For each sheet in received data, check the sheet name. If that sheet is the wanted sheet, add it into the ranges.
            List <string> ranges = new List <string>();

            foreach (Sheet sheet in sheets)
            {
                if ((wantedSheetNames.Count <= 0) || (wantedSheetNames.Contains(sheet.Properties.Title)))
                {
                    // PABLO201901: We dont want to download the template sheet
                    if (!sheet.Properties.Title.ToUpper().Equals("TEMPLATE"))
                    {
                        ranges.Add(sheet.Properties.Title);
                    }
                }
            }

            SpreadsheetsResource.ValuesResource.BatchGetRequest request = service.Spreadsheets.Values.BatchGet(spreadSheetKey);
            request.Ranges = ranges;
            BatchGetValuesResponse response = request.Execute();

            //For each wanted sheet, create a json file
            foreach (ValueRange valueRange in response.ValueRanges)
            {
                // PABLO201901: Quiza deberiamos asignar el nombrecito a mano, en vez de usar el sheetname
                // Vale, una suma de los dos. Primero el nombrecito a mano outputFileName, y luego el sheetname
                //Create json file
                string Sheetname = "";
                if (valueRange.Range.Split('!')[0] == outputFileName)
                {
                    Sheetname = valueRange.Range.Split('!')[0];
                }
                else
                {
                    Sheetname = outputFileName + "_" + valueRange.Range.Split('!')[0];
                }
                CreateJsonFile(Sheetname, outputDir, valueRange);
            }

            AssetDatabase.Refresh();
            Debug.Log("Download completed.");
        }