public static void Read(string rawText, GSTU_Search search, UnityAction <GstuSpreadSheet> callback)
{
ValueRange rawData = LitJson.JsonMapper.ToObject <ValueRange>(rawText);
// JSON.Load(request.downloadHandler.text).Make<ValueRange>();
var response = new GSTU_SpreadsheetResponse(rawData);
if (callback != null)
{
var sheet = new GstuSpreadSheet(response, search.titleColumn, search.titleRow);
sheet.rawText = rawText;
callback(sheet);
}
}
/* public GstuSpreadSheet(GSTU_SpreadsheetResponce data)
* {
* string startColumn = Regex.Replace(data.StartCell(), "[^a-zA-Z]", "");
* int startRow = int.Parse(Regex.Replace(data.StartCell(), "[^0-9]", ""));
*
* int startColumnAsInt = GoogleSheetsToUnityUtilities.NumberFromExcelColumn(startColumn);
* int currentRow = startRow;
*
* foreach (List<string> dataValue in data.valueRange.values)
* {
* int currentColumn = startColumnAsInt;
*
* foreach (string entry in dataValue)
* {
* string realColumn = GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(currentColumn);
* GSTU_Cell cell = new GSTU_Cell(entry, realColumn, currentRow);
*
* Cells.Add(realColumn + currentRow, cell);
*
* if (!rows.ContainsKey(currentRow))
* {
* rows.Add(currentRow, new List<GSTU_Cell>());
* }
*
* rows[currentRow].Add(cell);
*
* if (!columns.ContainsPrimaryKey(realColumn))
* {
* columns.Add(realColumn, new List<GSTU_Cell>());
* }
*
* columns[realColumn].Add(cell);
*
* currentColumn++;
* }
*
* currentRow++;
* }
*
* if(data.sheetInfo != null)
* {
* foreach(var merge in data.sheetInfo.merges)
* {
* Debug.Log("Merge starts at : " + merge.startRowIndex + " " + GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(merge.startColumnIndex));
* }
* }
* }*/
public GstuSpreadSheet(GSTU_SpreadsheetResponse data, string titleColumn, int titleRow)
{
string startColumn = Regex.Replace(data.StartCell(), "[^a-zA-Z]", "");
int startRow = int.Parse(Regex.Replace(data.StartCell(), "[^0-9]", ""));
int startColumnAsInt = GoogleSheetsToUnityUtilities.NumberFromExcelColumn(startColumn);
int currentRow = startRow;
Dictionary <string, string> mergeCellRedirect = new Dictionary <string, string>();
if (data.sheetInfo != null)
{
foreach (var merge in data.sheetInfo.merges)
{
string cell = GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(merge.startColumnIndex + 1) + (merge.startRowIndex + 1);
for (int r = merge.startRowIndex; r < merge.endRowIndex; r++)
{
for (int c = merge.startColumnIndex; c < merge.endColumnIndex; c++)
{
string mergeCell = GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(c + 1) + (r + 1);
mergeCellRedirect.Add(mergeCell, cell);
}
}
}
}
foreach (List <string> dataValue in data.valueRange.values)
{
int currentColumn = startColumnAsInt;
foreach (string entry in dataValue)
{
string realColumn = GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(currentColumn);
string cellID = realColumn + currentRow;
GSTU_Cell cell = null;
if (mergeCellRedirect.ContainsKey(cellID) && Cells.ContainsKey(mergeCellRedirect[cellID]))
{
cell = Cells[mergeCellRedirect[cellID]];
}
else
{
cell = new GSTU_Cell(entry, realColumn, currentRow);
//check the title row and column exist, if not create them
if (!rows.ContainsKey(currentRow))
{
rows.Add(currentRow, new List <GSTU_Cell>());
}
if (!columns.ContainsPrimaryKey(realColumn))
{
columns.Add(realColumn, new List <GSTU_Cell>());
}
rows[currentRow].Add(cell);
columns[realColumn].Add(cell);
//build a series of seconard keys for the rows and columns
if (realColumn == titleColumn)
{
rows.LinkSecondaryKey(currentRow, cell.value);
}
if (currentRow == titleRow)
{
columns.LinkSecondaryKey(realColumn, cell.value);
}
}
Cells.Add(cellID, cell);
currentColumn++;
}
currentRow++;
}
//build the column and row string Id's from titles
foreach (GSTU_Cell cell in Cells.Values)
{
cell.columnId = Cells[cell.Column() + titleRow].value;
cell.rowId = Cells[titleColumn + cell.Row()].value;
}
//build all links to row and columns for cells that are handled by merged title fields.
foreach (GSTU_Cell cell in Cells.Values)
{
foreach (KeyValuePair <string, GSTU_Cell> cell2 in Cells)
{
if (cell.columnId == cell2.Value.columnId && cell.rowId == cell2.Value.rowId)
{
if (!cell.titleConnectedCells.Contains(cell2.Key))
{
cell.titleConnectedCells.Add(cell2.Key);
}
}
}
}
}
/// <summary>
/// Reads the spread sheet and callback with the results
/// </summary>
/// <param name="request"></param>
/// <param name="search"></param>
/// <param name="containsMergedCells"></param>
/// <param name="callback"></param>
/// <returns></returns>
static IEnumerator Read(UnityWebRequest request, GSTU_Search search, bool containsMergedCells, UnityAction <GstuSpreadSheet> callback)
{
var apiKey = Config.gdr.access_token;
if (Application.isPlaying)
{
// yield return new Task(CheckForRefreshToken());
yield return(CheckForRefreshToken());
}
#if UNITY_EDITOR
else
{
yield return(EditorCoroutineRunner.StartCoroutine(CheckForRefreshToken()));
}
#endif
var newApiKey = Config.gdr.access_token;
if (!apiKey.Equals(newApiKey))
{
request = UnityWebRequest.Get(CreateReadRequestURI(search));
}
using (request)
{
yield return(request.SendWebRequest());
if (string.IsNullOrEmpty(request.downloadHandler.text) || request.downloadHandler.text == "{}")
{
Debug.LogWarning("Unable to Retreive data from google sheets");
yield break;
}
if (request.downloadHandler.text.Contains("UNAUTHENTICATED"))
{
Debug.LogWarning("Unable to Retreive data from google sheets:UNAUTHENTICATED");
yield break;
}
ValueRange rawData = LitJson.JsonMapper.ToObject <ValueRange>(request.downloadHandler.text);
// JSON.Load(request.downloadHandler.text).Make<ValueRange>();
var response = new GSTU_SpreadsheetResponse(rawData);
//if it contains merged cells then process a second set of json data to know what these cells are
if (containsMergedCells)
{
StringBuilder sb = new StringBuilder();
sb.Append("https://sheets.googleapis.com/v4/spreadsheets");
sb.Append("/" + search.sheetId);
sb.Append("?access_token=" + Config.gdr.access_token);
UnityWebRequest request2 = UnityWebRequest.Get(sb.ToString());
yield return(request2.SendWebRequest());
SheetsRootObject root = LitJson.JsonMapper.ToObject <SheetsRootObject>(request2.downloadHandler.text);
//JSON.Load(request2.downloadHandler.text).Make<SheetsRootObject>();
response.sheetInfo = root.sheets.FirstOrDefault(x => x.properties.title == search.worksheetName);
}
if (callback != null)
{
var sheet = new GstuSpreadSheet(response, search.titleColumn, search.titleRow);
sheet.rawText = request.downloadHandler.text;
callback(sheet);
}
}
}