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); } }
public GSheet(ICtrlSheet settings) { data = settings; InitAttributesReaderCommands(); credential = GetSheetCredentials(); var service = GetService(credential); if (service == null) { return; } data.MyPage = new MyPage(service); request = data.MyPage.Service.Spreadsheets.Values.BatchGet(data.MyPage.SpreadsheetId); CreateSheets(); CreateComands(); CheckList(); lastIdLine.lineId = UpdateLastidCommand(); data.UpdateDataAction += OnUpdateDataAction; data.MinerActivityAction += OnMinerActivityAction; data.AlarmAction += OnAlarmAction; Console.WriteLine("Telegram token:"); data.UserToKen.ForEach(i => RigEx.WriteLineColors($"{i.Name} - {i.Id}", ConsoleColor.DarkCyan)); RigEx.WriteLineColors($"my name {data.MyPage.MyServerSheetId.Name} ", ConsoleColor.Yellow); Console.WriteLine("\nOther Rigs:"); if (!data.ServersSheetId.Any()) { Console.WriteLine("other ServSheetId == null"); } else { foreach (var s in data.ServersSheetId) { RigEx.WriteLineColors($"server name:{s.Name}", ConsoleColor.Yellow); } } }
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); } } }
private void readGoogleSheet() { // Define request parameters. List <string> ranges = new List <string>(); foreach (var monthString in monthStrings) { ranges.Add(monthString); } // choose input format between 0: FORMATED_VALUE, 1: UNFORMATED_VALUE and 2: FORMULA SpreadsheetsResource.ValuesResource.BatchGetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.BatchGetRequest.ValueRenderOptionEnum) 1; SpreadsheetsResource.ValuesResource.BatchGetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.BatchGetRequest.DateTimeRenderOptionEnum) 0; // Alternative use fileId from constructor param here SpreadsheetsResource.ValuesResource.BatchGetRequest request = Program.SpreadsheetService.Spreadsheets.Values.BatchGet(spreadsheetId); request.Ranges = ranges; request.ValueRenderOption = valueRenderOption; request.DateTimeRenderOption = dateTimeRenderOption; Data.BatchGetValuesResponse response = request.Execute(); string columnName = ""; for (int month = 0; month < response.ValueRanges.Count; month++) { // we read the 6 first columns from a month set from google sheet for (int i = 0; i < 6; i++) { switch (i) { case 0: columnName = "Rubrik"; break; case 4: columnName = "Kategori"; break; case 5: columnName = "Summa"; break; default: columnName = ""; break; } var columnSpec = new DataColumn { DataType = typeof(string), ColumnName = columnName }; yearSet.Tables[month].Columns.Add(columnSpec); } if (response.ValueRanges[month].Values != null && response.ValueRanges[month].Values.Count > 0) { for (int row = 0; row < response.ValueRanges[month].Values.Count; row++) { DataRow newRow = yearSet.Tables[month].NewRow(); yearSet.Tables[month].Rows.Add(newRow); for (int col = 0; col < response.ValueRanges[month].Values[row].Count; col++) { yearSet.Tables[month].Rows[row][col] = response.ValueRanges[month].Values[row][col]; } } } } }
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 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."); }