public static void AtualizarPlanilha()
{
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(idPlanilha);
planilhas = get.Execute();
valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, idPlanilha, "!A1:Z1000");
request.Execute();
for (int i = 0; i <= 5; i++)
{
GerarTabelaMesSheet(i);
}
for (int i = 0; i <= 5; i++)
{
GerarTabelaSavingsSheet(i);
}
Layout();
}
}
public void UpdateSheet(string Range, IList <IList <Object> > UpdateValue)
{
this.Range = Range;
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
ValueRange requestBody = new ValueRange();
requestBody.Values = UpdateValue;
SpreadsheetsResource.ValuesResource.UpdateRequest requestUpdate = ShService.Spreadsheets.Values.Update(requestBody, SpreadSheetID, Range);
requestUpdate.ValueInputOption = valueInputOption;
requestUpdate.Execute();
}
public void updateData()
{
UserCredential credential;
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// The ID of the spreadsheet to update.
string spreadsheetId = "YOUR SPREADSHEET ID"; // TODO: Update placeholder value.
// The A1 notation of the values to update.
string range = "YOUR SPREADSHEET RANGE"; // TODO: Update placeholder value.
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
// TODO: Assign values to desired properties of `requestBody`. All existing
// properties will be replaced:
ValueRange requestBody = new ValueRange();
var oblist = new List <object>()
{
"YOUR SPREADSHEET VALUE"
};
requestBody.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest request = sheetsService.Spreadsheets.Values.Update(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
UpdateValuesResponse response = request.Execute();
}
private SpreadsheetsResource.ValuesResource.UpdateRequest CreateUpdateRequest(object value,
string spreadSheetIdd,
string range,
string majorDimension,
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum option)
{
var valueRange = new ValueRange();
valueRange.Values = new List <IList <object> >();
var values = new List <object>();
if (value is IEnumerable <object> )
{
foreach (var item in value as IEnumerable <object> )
{
values.Add(item);
}
}
else
{
values.Add(value);
}
valueRange.Values.Add(values);
valueRange.Range = range;
valueRange.MajorDimension = majorDimension;
var updateRequest = _service.Spreadsheets.Values.Update(valueRange, spreadSheetIdd, valueRange.Range);
updateRequest.ValueInputOption = option;
return(updateRequest);
}
protected static bool WriteSingleCellToSheet(string strDataToSave, string strCellToSaveData)
{
try
{
Thread.Sleep(1000); // Sleep for a second so we don't go over the Google allotted requests.
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
// TODO: Assign values to desired properties of `requestBody`. All existing
// properties will be replaced:
ValueRange requestBody = new ValueRange
{
MajorDimension = "COLUMNS" // "ROWS" / "COLUMNS"
};
var oblist = new List <object>()
{
strDataToSave
};
requestBody.Values = new List <IList <object> > {
oblist
};
UserCredential credential;
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
SCOPES,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
SpreadsheetsResource.ValuesResource.UpdateRequest request = sheetsService.Spreadsheets.Values.Update(requestBody, SPREADSHEET_ID, strCellToSaveData);
request.ValueInputOption = valueInputOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
UpdateValuesResponse response = request.Execute();
// Data.UpdateValuesResponse response = await request.ExecuteAsync();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
} // End WriteSingleCellToSheet
public async Task <int?> UpdateNotaAsync(int qtdVotosAtual, int nota, double notaAtual, int rowNumber)
{
int?resultUpdate = await Task.Run(() =>
{
qtdVotosAtual += 1;
var mediaNota = (nota + notaAtual) / qtdVotosAtual;
var body = GenerateValuesBody(mediaNota);
SpreadsheetsResource.ValuesResource.UpdateRequest updataReq = service.Spreadsheets.Values.Update(body, spreadSheetId, $"{tabName}!{COLUNA_MEDIANOTA}{rowNumber}");
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
updataReq.ValueInputOption = valueInputOption;
var responseUpadate = updataReq.Execute();
return(responseUpadate.UpdatedCells);
});
return(resultUpdate);
}
public async Task <int?> UpdateVotosAsync(int qtdVotosAtual, int rowNumber)
{
int?resultUpdate = await Task.Run(() =>
{
var body = GenerateValuesBody(qtdVotosAtual + 1);
SpreadsheetsResource.ValuesResource.UpdateRequest updataReq = service.Spreadsheets.Values.Update(body, spreadSheetId, $"{tabName}!{COLUNA_VOTOS}{rowNumber}");
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
updataReq.ValueInputOption = valueInputOption;
var responseUpadate = updataReq.Execute();
return(responseUpadate.UpdatedCells);
});
return(resultUpdate);
}
public async Task <int?> UpdateUltimaVezTocadaAsync(DateTime dataTocada, int rowNumber)
{
int?resultUpdate = await Task.Run(() =>
{
var body = GenerateValuesBody(dataTocada);
SpreadsheetsResource.ValuesResource.UpdateRequest updataReq = service.Spreadsheets.Values.Update(body, spreadSheetId, $"{tabName}!{COLUNA_ULTIMAVEZTOCADA}{rowNumber}");
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
updataReq.ValueInputOption = valueInputOption;
var responseUpadate = updataReq.Execute();
return(responseUpadate.UpdatedCells);
});
return(resultUpdate);
}
public void ExportarJogosJquery()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) {
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/games.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc";
GameRepository game = new GameRepository();
PlatformRepository plataforma = new PlatformRepository();
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id);
Spreadsheet planilhas = get.Execute();
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
var aba = planilha.Properties.Title;
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000");
ClearValuesResponse response = request.Execute();
List <GameView> lista = new List <GameView>();
List <int> plat = new List <int>();
List <object> cabecalho = null;
switch (aba)
{
case "Wishlist":
lista = game.ListarJogosWishlist(new List <int> {
0
});
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
case "Watchlist":
lista = game.ListarJogos(new List <int> {
0
}, 3);
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
default:
int?plataformas = plataforma.GetIdBySigla(aba);
plat = new List <int> {
plataformas.Value
};
lista = game.ListarJogos(plat, 1);
cabecalho = new List <object>()
{
"", "Título"
};
break;
}
string range = aba + "!A1:D" + lista.Count + 1;
List <IList <object> > dados = new List <IList <object> >();
dados.Add(cabecalho);
foreach (GameView jogo in lista)
{
if (cabecalho.Count == 2)
{
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name
});
}
else
{
string data = null;
if (jogo.ReleaseDate != null)
{
data = jogo.ReleaseDate.Value.ToShortDateString();
}
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas)
});
}
}
ValueRange valueRange = new ValueRange();
valueRange.Values = dados;
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range);
updateRequest.ValueInputOption = valueInputOption;
UpdateValuesResponse resposta = updateRequest.Execute();
Request alignLeftRequest = new Request();
alignLeftRequest.RepeatCell = new RepeatCellRequest();
alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignLeftRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3
};
alignLeftRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "LEFT"
}
};
Request alignCenterRequest = new Request();
alignCenterRequest.RepeatCell = new RepeatCellRequest();
alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignCenterRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1
};
alignCenterRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "Center"
}
};
Request resizeRequest = new Request();
resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest();
resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange {
SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(alignLeftRequest);
batch.Requests.Add(alignCenterRequest);
batch.Requests.Add(resizeRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
}
public void TesteSheet()
{
#region setup
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) {
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/games.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc";
List <GameView> lista_mock = new List <GameView>();
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste teste teste teste teste teste teste"
});
GameRepository game = new GameRepository();
PlatformRepository plataforma = new PlatformRepository();
#endregion
#region limpar, preencher e formatar abas
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id);
Spreadsheet planilhas = get.Execute();
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
var aba = planilha.Properties.Title;
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000");
ClearValuesResponse response = request.Execute();
List <GameView> lista = new List <GameView>();
List <int> plat = new List <int>();
List <object> cabecalho = null;
switch (aba)
{
case "Wishlist":
lista = game.ListarJogosWishlist(new List <int> {
0
});
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
case "Watchlist":
lista = game.ListarJogos(new List <int> {
0
}, 3);
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
default:
int?plataformas = plataforma.GetIdBySigla(aba);
plat = new List <int> {
plataformas.Value
};
lista = game.ListarJogos(plat, 1);
cabecalho = new List <object>()
{
"", "Título"
};
break;
}
string range = aba + "!A1:D" + lista.Count + 1;
List <IList <object> > dados = new List <IList <object> >();
dados.Add(cabecalho);
foreach (GameView jogo in lista)
{
if (cabecalho.Count == 2)
{
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name
});
}
else
{
string data = null;
if (jogo.ReleaseDate != null)
{
data = jogo.ReleaseDate.Value.ToShortDateString();
}
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas)
});
}
}
ValueRange valueRange = new ValueRange();
valueRange.Values = dados;
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range);
updateRequest.ValueInputOption = valueInputOption;
UpdateValuesResponse resposta = updateRequest.Execute();
Request alignLeftRequest = new Request();
alignLeftRequest.RepeatCell = new RepeatCellRequest();
alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignLeftRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3
};
alignLeftRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "LEFT"
}
};
Request alignCenterRequest = new Request();
alignCenterRequest.RepeatCell = new RepeatCellRequest();
alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignCenterRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1
};
alignCenterRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "Center"
}
};
Request resizeRequest = new Request();
resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest();
resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange {
SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(alignLeftRequest);
batch.Requests.Add(alignCenterRequest);
batch.Requests.Add(resizeRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
#endregion
#region deletar aba
// A list of updates to apply to the spreadsheet.
// Requests will be applied in the order they are specified.
// If any request is not valid, no requests will be applied.
/*List<Request> requests = new List<Request>(); // TODO: Update placeholder value.
* var a = new DeleteSheetRequest();
* a.SheetId = 0;
*
* var t = new Request();
* t.DeleteSheet = a;
*
* requests.Add(t);
*
* // TODO: Assign values to desired properties of `requestBody`:
* BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();
* requestBody.Requests = requests;
*
* SpreadsheetsResource.BatchUpdateRequest req = sheetsService.Spreadsheets.BatchUpdate(requestBody, id);
*
* // To execute asynchronously in an async method, replace `request.Execute()` as shown:
* BatchUpdateSpreadsheetResponse response = req.Execute();*/
#endregion
#region criar planilha
// TODO: Assign values to desired properties of `requestBody`:
/*Spreadsheet requestBody3 = new Spreadsheet();
* var propriedades = new SpreadsheetProperties();
* propriedades.Title = "Games";
* requestBody3.Properties = propriedades;
*
* SpreadsheetsResource.CreateRequest request3 = sheetsService.Spreadsheets.Create(requestBody3);
*
* // To execute asynchronously in an async method, replace `request.Execute()` as shown:
* Spreadsheet response3 = request3.Execute();
* // Data.Spreadsheet response = await request.ExecuteAsync();
*
* // TODO: Change code below to process the `response` object:
* Console.WriteLine(response3);*/
#endregion
}
