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