/// <summary>
/// Adds additional columns and rows to a worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="columnsToAdd"></param>
/// <param name="rowsToAdd"></param>
public static void AddRowsAndColumns(this GS2U_Worksheet worksheet, int columnsToAdd, int rowsToAdd)
{
int rows = (int)worksheet.worksheetEntry.Rows;
int colums = (int)worksheet.worksheetEntry.Cols;
worksheet.SetWorksheetSize(colums + columnsToAdd, rows + rowsToAdd);
}
/// <summary>
/// updates a rows information based on the instance of dataname found in the worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="rowNumber">The number of the row to be modified</param>
/// <param name="newData"></param>
public static void ModifyRowData(this GS2U_Worksheet worksheet, int rowNumber, Dictionary <string, string> newData)
{
if (worksheet.GetWorksheetSize().y < rowNumber)
{
Debug.Log("Worksheet is smaller than rowNumber to edit");
return;
}
ListFeed feed = worksheet.LoadListFeedWorksheet();
ListEntry row = (ListEntry)feed.Entries[rowNumber - 1];
foreach (var entry in newData)
{
bool found = false;
foreach (ListEntry.Custom element in row.Elements)
{
if (element.LocalName.ToLower() == entry.Key.ToLower())
{
element.Value = entry.Value;
found = true;
break;
}
}
if (!found)
{
Debug.LogError("No field found for " + entry.Key);
}
}
row.Update();
}
///// <summary>
///// Get Data from a collection of cells
///// </summary>
///// <param name="worksheet"></param>
///// <param name="columStart"></param>
///// <param name="columEnd"></param>
///// <param name="rowStart"></param>
///// <param name="rowEnd"></param>
///// <returns></returns>
//public static List<CellEntry> GetCellDataCollection(this GS2U_Worksheet worksheet, string columStart, string columEnd, int rowStart, int rowEnd)
//{
// CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
// cellQuery.MinimumRow = (uint)rowStart;
// cellQuery.MaximumRow = (uint)rowEnd;
// cellQuery.MinimumColumn = (uint)Util.GetIndexInAlphabet(columStart);
// cellQuery.MaximumColumn = (uint)Util.GetIndexInAlphabet(columEnd);
// CellFeed cellFeed = SpreadSheetManager.service.Query(cellQuery) as CellFeed;
// List<CellEntry> entries = new List<CellEntry>();
// for (int i = 0; i < cellFeed.Entries.Count; i++)
// {
// entries.Add((CellEntry)cellFeed.Entries[i]);
// }
// return entries;
//}
//public static void ModifyCellData(this CellEntry cell, string newData)
//{
// cell.InputValue = newData;
// cell.Update();
//}
public static void ModifyCellData(this GS2U_Worksheet worksheet, string colum, int row, string newData)
{
CellEntry cell = worksheet.GetCellEntry(colum, row);
cell.InputValue = newData;
cell.Update();
}
/// <summary>
/// Load worksheet as listfeed
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
private static ListFeed LoadListFeedWorksheet(this GS2U_Worksheet worksheet)
{
AtomLink listFeedLink = worksheet.worksheetEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
return(SpreadSheetManager.service.Query(listQuery) as ListFeed);
}
/// <summary>
/// get the cell entry
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
private static CellEntry GetCellEntry(this GS2U_Worksheet worksheet, int column, int row)
{
CellQuery cellQuery = new CellQuery(worksheet.worksheetEntry.CellFeedLink);
cellQuery.MinimumRow = (uint)row;
cellQuery.MaximumRow = (uint)row;
cellQuery.MinimumColumn = (uint)column;
cellQuery.MaximumColumn = (uint)column;
CellFeed cellFeed = SpreadSheetManager.service.Query(cellQuery) as CellFeed;
return((CellEntry)cellFeed.Entries[0]);
}
/// <summary>
/// Gets the data from an exact cell reference
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
public static CellData GetCellData(this GS2U_Worksheet worksheet, string column, int row)
{
int colInt = GoogleSheetsToUnityUtilities.GetIndexInAlphabet(column);
CellEntry entry = worksheet.GetCellEntry(colInt, row);
List <string> rows = worksheet.GetRowTitles();
List <string> cols = worksheet.GetColumnTitles();
CellData cellData = new CellData(entry.InputValue, rows[row - 1], cols[colInt - 1]);
return(cellData);
}
/// <summary>
/// Get the cell entry
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
private static CellEntry GetCellEntry(this GS2U_Worksheet worksheet, string column, int row)
{
CellQuery cellQuery = new CellQuery(worksheet.worksheetEntry.CellFeedLink);
int colInt = GoogleSheetsToUnityUtilities.GetIndexInAlphabet(column);
cellQuery.MinimumRow = (uint)row;
cellQuery.MaximumRow = (uint)row;
cellQuery.MinimumColumn = (uint)colInt;
cellQuery.MaximumColumn = (uint)colInt;
CellFeed cellFeed = SpreadSheetManager.service.Query(cellQuery) as CellFeed;
return((CellEntry)cellFeed.Entries[0]);
}
/// <summary>
/// Deletes the first row found using nameID
/// </summary>
/// <param name="worksheet"></param>
/// <param name="dataNameID"></param>
public static void DeleteRowData(this GS2U_Worksheet worksheet, string dataNameID)
{
List <string> ids = worksheet.GetRowTitles();
int index = ids.IndexOf(dataNameID);
if (index > -1)
{
worksheet.DeleteRowData(index);
}
else
{
Debug.Log("no data found for entry " + dataNameID);
}
}
/// <summary>
/// updates a rows information based on the instance of dataname found in the worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="dataNameID"> the current name in the spreedsheet using the first colum as the identifier</param>
/// <param name="newData"></param>
public static void ModifyRowData(this GS2U_Worksheet worksheet, string dataNameID, Dictionary <string, string> newData)
{
List <string> ids = worksheet.GetRowTitles();
int index = ids.IndexOf(dataNameID);
if (index > -1)
{
worksheet.ModifyRowData(index, newData);
}
else
{
Debug.Log("no data found for entry " + dataNameID);
}
}
/// <summary>
/// Add a new row of data to the bottom of the worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="newData"></param>
public static void AddRowData(this GS2U_Worksheet worksheet, Dictionary <string, string> newData)
{
ListFeed feed = worksheet.LoadListFeedWorksheet();
ListEntry newRow = new ListEntry();
foreach (var entry in newData)
{
newRow.Elements.Add(new ListEntry.Custom()
{
LocalName = entry.Key.ToLower(), Value = entry.Value.ToLower()
});
}
SpreadSheetManager.service.Insert(feed, newRow);
}
/// <summary>
/// Returns a list of all row titles
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
public static List <string> GetRowTitles(this GS2U_Worksheet worksheet)
{
CellQuery cellQuery = new CellQuery(worksheet.worksheetEntry.CellFeedLink);
cellQuery.MinimumColumn = 1;
cellQuery.MaximumColumn = 1;
CellFeed cellFeed = SpreadSheetManager.service.Query(cellQuery) as CellFeed;
List <string> list = new List <string>();
foreach (CellEntry cell in cellFeed.Entries)
{
list.Add(cell.Value);
}
return(list);
}
/// <summary>
/// Loads all the information from the selected worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
///
/// <summary>
/// Loads all the information from the selected worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
public static WorksheetData LoadAllWorksheetInformation(this GS2U_Worksheet worksheet)
{
ListFeed feed = LoadListFeedWorksheet(worksheet);
WorksheetData returnData = new WorksheetData();
foreach (ListEntry row in feed.Entries)
{
string rowTitle = row.Title.Text;
RowData rowData = new RowData(rowTitle);
foreach (ListEntry.Custom element in row.Elements)
{
rowData.cells.Add(new CellData(element.Value, element.LocalName, rowTitle));
}
returnData.rows.Add(rowData);
}
return(returnData);
}
/// <summary>
/// Deletes the first row found using nameID
/// </summary>
/// <param name="worksheet"></param>
/// <param name="rowNumber">Row number to be removed</param>
public static void DeleteRowData(this GS2U_Worksheet worksheet, int rowNumber)
{
if (worksheet.GetWorksheetSize().y < rowNumber)
{
Debug.Log("Worksheet is smaller than rowNumber to edit");
return;
}
if (rowNumber > 0)
{
ListFeed feed = worksheet.LoadListFeedWorksheet();
ListEntry row = (ListEntry)feed.Entries[rowNumber - 1];
row.Delete();
}
else
{
Debug.Log("Invalid Row Number");
}
}
public static void UpdateWorksheetName(this GS2U_Worksheet worksheet, string newName)
{
worksheet.worksheetEntry.Title.Text = newName;
worksheet.worksheetEntry.Update();
}
/// <summary>
/// Get the current columns and row count for a worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
public static Vector2 GetWorksheetSize(this GS2U_Worksheet worksheet)
{
Vector2 returnValue = new Vector2(worksheet.worksheetEntry.Cols, worksheet.worksheetEntry.Rows);
return(returnValue);
}
/// <summary>
/// premently deletes the worksheet from Google sheets
/// </summary>
/// <param name="worksheet"></param>
public static void DeleteWorksheet(this GS2U_Worksheet worksheet)
{
worksheet.worksheetEntry.Delete();
}
/// <summary>
/// Set the size of a worksheet, WARNING!!! setting the size smaller than the orginal will delete the cells and its contents that do not exist in the new bounds
/// </summary>
/// <param name="worksheet"></param>
/// <param name="newColums"></param>
/// <param name="newRows"></param>
public static void SetWorksheetSize(this GS2U_Worksheet worksheet, int newColums, int newRows)
{
worksheet.worksheetEntry.Cols = (uint)newColums;
worksheet.worksheetEntry.Rows = (uint)newRows;
worksheet.worksheetEntry.Update();
}
