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