/// <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)
{
if (Application.isPlaying)
{
yield return(new Task(CheckForRefreshToken()));
}
#if UNITY_EDITOR
else
{
yield return(EditorCoroutineRunner.StartCoroutine(CheckForRefreshToken()));
}
#endif
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;
}
ValueRange rawData = JSON.Load(request.downloadHandler.text).Make <ValueRange>();
LoadSheet.rawData = JSON.Load(request.downloadHandler.text).Make <ValueRange>();
GSTU_SpreadsheetResponce responce = new GSTU_SpreadsheetResponce(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 = JSON.Load(request2.downloadHandler.text).Make <SheetsRootObject>();
responce.sheetInfo = root.sheets.FirstOrDefault(x => x.properties.title == search.worksheetName);
}
if (callback != null)
{
callback(new GstuSpreadSheet(responce, search.titleColumn, search.titleRow));
}
}
}
/// <summary>
/// Wait for the Web request to complete and then process the results
/// </summary>
/// <param name="www"></param>
/// <param name="titleColumn"></param>
/// <param name="titleRow"></param>
/// <param name="callback"></param>
/// <returns></returns>
static IEnumerator Read(WWW www, string titleColumn, int titleRow, OnSpreedSheetLoaded callback)
{
yield return(www);
ValueRange rawData = JSON.Load(www.text).Make <ValueRange>();
GSTU_SpreadsheetResponce responce = new GSTU_SpreadsheetResponce(rawData);
GstuSpreadSheet spreadSheet = new GstuSpreadSheet(responce, titleColumn, titleRow);
if (callback != null)
{
callback(spreadSheet);
}
}
/* 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_SpreadsheetResponce 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++;
}
// TODO: MCPGNZ DISABLED FOR FASTNESSSS
//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.
// TODO: MCPGNZ DISABLED FOR FASTNESSSS
//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);
// }
// }
// }
//}
}
