public ITable <T> Rename(string newName)
{
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}:batchUpdate", this._spreadsheetId);
var request = this._connector.CreateRequest(uri);
var payload = JsonConvert.SerializeObject(new
{
requests = new
{
updateSheetProperties = new
{
properties = new
{
sheetId = this._worksheetId,
title = newName
},
fields = "title"
}
}
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Post, payload));
response
.Status(HttpStatusCode.OK);
return(this);
}
public List <IRow <T> > GetAll()
{
var countFields = Utils.GetFields <T>().Count();
var fieldIndex = ((char)('A' + countFields)).ToString();
var range = string.Format("{0}!A2:{1}", this._name, fieldIndex);
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}/values/{1}?fields=values&majorDimension=ROWS", this._spreadsheetId, range);
var request = this._connector.CreateRequest(uri);
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Get));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
JArray registros = data.values;
return(registros.Select((a, i) => new Row <T>(
this._connector,
this.DeserializeElement((JArray)a),
this._spreadsheetId,
this._worksheetId,
string.Format("{0}!A{1}:{2}{3}", this._name, i + 1, fieldIndex, i + 1))).ToList() as List <IRow <T> >);
}
public IDatabase CreateDatabase(string name)
{
var database = this.GetDatabase(name);
if (database != null)
{
throw new Exception("Exists a database with the same name");
}
var uri = "https://sheets.googleapis.com/v4/spreadsheets";
var request = this._connector.CreateRequest(uri);
var payload = JsonConvert.SerializeObject(new
{
properties = new
{
title = name
}
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Post, payload));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
var spreadsheetId = (string)data.spreadsheetId;
return(new Database(this._connector, spreadsheetId));
}
public void Delete()
{
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}:batchUpdate", this._spreadsheetId);
var request = this._connector.CreateRequest(uri);
var index = Utils.A1ToRowIndex(this._range);
var payload = JsonConvert.SerializeObject(new
{
requests = new
{
deleteDimension = new
{
range = new
{
sheetId = this._worksheetId,
dimension = "ROWS",
startIndex = index - 1,
endIndex = index,
}
}
}
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Post, payload));
response
.Status(HttpStatusCode.OK);
}
public ITable <T> GetTable <T>(string name) where T : new()
{
var fields = Encode.UrlEncode("sheetId,title");
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}?includeGridData=false&fields=sheets.properties({1})", this._spreadsheetId, fields);
var request = this._connector.CreateRequest(uri);
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Get));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
var sheets = data.sheets;
if (sheets.Count > 0)
{
foreach (var sheet in sheets)
{
if (sheet.properties.title == name)
{
return(new Table <T>(this._connector, name, this._spreadsheetId, (string)sheet.properties.sheetId));
}
}
}
return(null);
}
public IDatabasePermission GetPermission(string email)
{
var fields = Encode.UrlEncode("emailAddress,id,role,type");
var uri = string.Format("https://www.googleapis.com/drive/v3/files/{0}/permissions?fields=permissions({1})", this._spreadsheetId, fields);
var request = this._connector.CreateRequest(uri);
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Get));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
var permissions = data.permissions;
if (permissions.Count > 0)
{
foreach (var permission in permissions)
{
if (permission.emailAddress == email)
{
var role = (Role)System.Enum.Parse(typeof(Role), (string)permission.role, true);
var type = (Type)System.Enum.Parse(typeof(Type), (string)permission.type, true);
return(new DatabasePermission(this._connector, new Permission((string)permission.emailAddress, role, type), this._spreadsheetId, (string)permission.id));
}
}
}
return(null);
}
public IRow <T> Add(T record)
{
var queryParameters = "insertDataOption=INSERT_ROWS&valueInputOption=USER_ENTERED";
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}/values/{1}:append?{2}", this._spreadsheetId, this._name, queryParameters);
var request = this._connector.CreateRequest(uri);
var fields = Utils.GetFields <T>();
var payload = JsonConvert.SerializeObject(new
{
values = new[] {
fields.Select(a => a.GetValue(record, null))
}
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Post, payload));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
var range = (string)data.updates.updatedRange;
return(new Row <T>(this._connector, record, this._spreadsheetId, this._worksheetId, range));
}
public IRow <T> Update(T record)
{
var queryParameters = "valueInputOption=RAW";
var a1Notation = Utils.A1Notation(this._range);
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}/values/{1}?{2}", this._spreadsheetId, a1Notation, queryParameters);
var request = this._connector.CreateRequest(uri);
var fields = Utils.GetFields <T>();
var payload = JsonConvert.SerializeObject(new
{
values = new[] {
fields.Select(a => a.GetValue(record, null).ToString())
}
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Put, payload));
response
.Status(HttpStatusCode.OK);
this.Element = record;
return(this);
}
public void Delete()
{
var uri = string.Format("https://www.googleapis.com/drive/v3/files/{0}", this._spreadsheetId);
var request = this._connector.CreateRequest(uri);
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Delete));
response
.Status(HttpStatusCode.NoContent);
}
public ITable <T> CreateTable <T>(string name) where T : new()
{
var table = this.GetTable <T>(name);
if (table != null)
{
throw new System.Exception("Exists a table with the same name");
}
var field = Encode.UrlEncode("replies.addSheet.properties.sheetId");
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}:batchUpdate?fields={1}", this._spreadsheetId, field);
var fields = Utils.GetFields <T>();
var request = this._connector.CreateRequest(uri);
var payload = JsonConvert.SerializeObject(new
{
requests = new
{
addSheet = new
{
properties = new
{
title = name,
sheetType = "GRID",
gridProperties = new
{
columnCount = fields.Count(),
frozenRowCount = 1,
rowCount = 2
}
}
}
}
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Post, payload));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
var sheetId = (string)data.replies.First.addSheet.properties.sheetId;
this.AddHeader <T>(sheetId);
return(new Table <T>(this._connector, name, this._spreadsheetId, sheetId));
}
public IEnumerable <IDatabase> GetAllDatabases()
{
var uri = string.Format("https://www.googleapis.com/drive/v3/files?q={0}", Encode.UrlEncode(string.Format("mimeType = \"{0}\"", "application/vnd.google-apps.spreadsheet")));
var request = this._connector.CreateRequest(uri);
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Get));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
var documents = data.items;
foreach (var document in documents)
{
yield return(new Database(this._connector, (string)document.id));
}
}
public IDatabase AddPermission(string email, Role role, SheetDB.Enum.Type type)
{
var uri = string.Format("https://www.googleapis.com/drive/v3/files/{0}/permissions", this._spreadsheetId);
var request = this._connector.CreateRequest(uri);
var payload = JsonConvert.SerializeObject(new
{
role = System.Enum.GetName(typeof(SheetDB.Enum.Role), role),
type = System.Enum.GetName(typeof(SheetDB.Enum.Type), type),
emailAddress = email
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Post, payload));
response
.Status(HttpStatusCode.OK);
return(this);
}
public void Delete()
{
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}:batchUpdate", this._spreadsheetId);
var request = this._connector.CreateRequest(uri);
var payload = JsonConvert.SerializeObject(new
{
requests = new
{
deleteSheet = new
{
sheetId = this._worksheetId
}
}
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Post, payload));
response
.Status(HttpStatusCode.OK);
}
private void AddHeader <T>(string sheetId)
{
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}:batchUpdate", this._spreadsheetId);
var fields = Utils.GetFields <T>();
var request = this._connector.CreateRequest(uri);
var payload = JsonConvert.SerializeObject(new
{
requests = new
{
updateCells = new
{
start = new
{
sheetId = sheetId,
rowIndex = 0
},
rows = new[]
{
new {
values = fields.Select(a => new {
userEnteredValue = new {
stringValue = a.Name.ToLowerInvariant()
}
})
}
},
fields = "userEnteredValue.stringValue"
}
}
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Post, payload));
response
.Status(HttpStatusCode.OK);
}
public IDatabase GetDatabase(string name)
{
var uri = string.Format("https://www.googleapis.com/drive/v3/files?q={0}", Encode.UrlEncode(string.Format("name = \"{0}\"", name)));
var request = this._connector.CreateRequest(uri);
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Get));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
var documents = data.files;
if (documents.Count == 0)
{
return(null);
}
var spreadsheetId = (string)documents.First.id;
return(new Database(this._connector, spreadsheetId));
}
public IDatabasePermission Update(Role role)
{
var fields = Encode.UrlEncode("role");
var uri = string.Format("https://www.googleapis.com/drive/v3/files/{0}/permissions/{1}?fields={2}", this._spreadsheetId, this._permissionId, fields);
var request = this._connector.CreateRequest(uri);
var payload = JsonConvert.SerializeObject(new
{
role = System.Enum.GetName(typeof(SheetDB.Enum.Role), role)
});
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Patch, payload));
var data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
this.Permission.Role = (Role)System.Enum.Parse(typeof(Role), (string)data.role, true);
return(this);
}
public IRow <T> GetByIndex(int rowNumber)
{
rowNumber = rowNumber + 1;
var countFields = Utils.GetFields <T>().Count();
var fieldIndex = ((char)('A' + countFields)).ToString();
var range = string.Format("{0}!A{1}:{2}{3}", this._name, rowNumber, fieldIndex, rowNumber);
var uri = string.Format("https://sheets.googleapis.com/v4/spreadsheets/{0}/values/{1}?fields=values&majorDimension=ROWS", this._spreadsheetId, range);
var request = this._connector.CreateRequest(uri);
var response = new ResponseValidator(this._connector.Send(request, HttpMethod.Get));
dynamic data = response
.Status(HttpStatusCode.OK)
.Response.Data <dynamic>();
var registro = this.DeserializeElement(data.values.First);
return(new Row <T>(this._connector, registro, this._spreadsheetId, this._worksheetId, range));
}