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); }
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); }
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); }
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); } }
/// <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); } }
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); } } }
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."); }