public SpreadsheetAccess(SheetsService service, string spreadsheetId, ILogger logger)
{
this.service = service;
this.valuesResource = service.Spreadsheets.Values;
this.SpreadsheetId = spreadsheetId;
this.logger = logger;
}
public async Task <List <DetailDataViewModel> > ReadAsync(SpreadsheetsResource.ValuesResource valuesResource)
{
Google.Apis.Sheets.v4.Data.ValueRange response = await valuesResource.Get(SpreadsheetId, ReadRange).ExecuteAsync();
IList <IList <object> > values = response.Values;
DetailData detail = new DetailData();
if (values != null || values.Any())
{
//CABEÇALHO: var header = string.Join(" ", values.First().Select(r => r.ToString()));
//foreach (var row in values.Skip(1))
for (int i = 1; i < 4; i++)
{
var row = values[i];
detail.Data.Add(new DetailDataViewModel
{
Id = i,
CharName = row[0].ToString(),
Type = row[1].ToString(),
Skins = row[2].ToString(),
Skin_Spotlight = row[3].ToString(),
});
}
}
return(detail.Data);
}
public async Task WriteAsync(SpreadsheetsResource.ValuesResource valuesResource, List <object> newData, string writeRange)
{
var valueRange = new ValueRange {
Values = new List <IList <object> > {
newData
}
};
var update = valuesResource.Update(valueRange, SpreadsheetId, writeRange);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var response = await update.ExecuteAsync();
}
private static async Task WriteAsync(SpreadsheetsResource.ValuesResource valuesResource)
{
var valueRange = new ValueRange {
Values = new List <IList <object> > {
new List <object> {
"stan", 18
}
}
};
var update = valuesResource.Update(valueRange, SpreadsheetId, WriteRange);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var response = await update.ExecuteAsync();
Console.WriteLine($"Updated rows: {response.UpdatedRows}");
}
private static async Task LerPlanilha(SpreadsheetsResource.ValuesResource valuesResource,
string range, string idPlanilha, bool planilhaDiaria)
{
var response = await valuesResource.Get(idPlanilha, range).ExecuteAsync();
var values = response.Values;
if (planilhaDiaria)
{
EscreverRangeDiario = $"A{values.Count + 1}:J{values.Count}";
}
else
{
await EscreverPlanilhaDiaria(valuesResource, values);
}
}
private static async Task ReadAsync(SpreadsheetsResource.ValuesResource valuesResource)
{
var response = await valuesResource.Get(SpreadsheetId, ReadRange).ExecuteAsync();
var values = response.Values;
if (values == null || !values.Any())
{
Console.WriteLine("No data found.");
return;
}
var header = string.Join(" ", values.First().Select(r => r.ToString()));
Console.WriteLine($"Header: {header}");
foreach (var row in values.Skip(1))
{
var res = string.Join(" ", row.Select(r => r.ToString()));
Console.WriteLine(res);
}
}
static void Main(string[] args)
{
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);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
//Write Operation
///1bQ-B-lCefPIMFHQeobUygF5xP52gI1ved3sbiOwjHrE
string sheetId = "1bQ-B-lCefPIMFHQeobUygF5xP52gI1ved3sbiOwjHrE";
string range = "Sheet1!A2:B4";
var serviceValues = service.Spreadsheets.Values;
SpreadsheetsResource.ValuesResource valuesResource = serviceValues;
List <IList <object> > data = new List <IList <object> >();
var valueRange = new ValueRange
{
Values = new List <IList <object> >
{
new List <object> {
"ram1", "PR1"
},
new List <object> {
"ram2", "PR2"
},
new List <object> {
"ram3", "PR3"
}
}
};
var update = valuesResource.Update(valueRange, sheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var response = update.ExecuteAsync();
Console.WriteLine("Write Completed");
//Reading the values
// Define request parameters.
//11U_7wlwRBpESWV-n8AQ77i7PmUSJy0xCT5-hSdexapI
//String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
//String range = "Class Data!A2:E";
//SpreadsheetsResource.ValuesResource.GetRequest request =
// service.Spreadsheets.Values.Get(spreadsheetId, range);
//// Prints the names and majors of students in a sample spreadsheet:
//// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
//ValueRange response = request.Execute();
//IList<IList<Object>> values = response.Values;
//if (values != null && values.Count > 0)
//{
// Console.WriteLine("Name, Major");
// foreach (var row in values)
// {
// // Print columns A and E, which correspond to indices 0 and 4.
// Console.WriteLine("{0}, {1}", row[0], row[4]);
// }
//}
//else
//{
// Console.WriteLine("No data found.");
//}
Console.Read();
}
private static async Task EscreverPlanilhaDiaria(SpreadsheetsResource.ValuesResource valuesResource, IList <IList <object> > values)
{
await LerPlanilha(valuesResource, lerRangeDiario, idPlanilhaDiaria, true);
var linha = string.Join("----", values.First().Select(r => r.ToString()));
linhaSplit = linha.Split("----");
string dividir = "N";
string minutosIndividual;
string pessoaIndividual;
string[] minutosPessoa;
string[] pessoa;
List <IList <object> > lista = new List <IList <object> >();
if (linhaSplit[1].Contains("/"))
{
Console.WriteLine("Dividir apontamento? (S/N)");
dividir = Console.ReadLine();
if (dividir.Equals("S"))
{
pessoa = linhaSplit[1].Split("/");
minutosPessoa = new string[pessoa.Length];
for (var i = 0; i < pessoa.Length; i++)
{
Console.WriteLine("Minutos da Pessoa: " + pessoa[i]);
minutosPessoa[i] = Console.ReadLine();
lista.Add(new List <object> {
linhaSplit[6], pessoa[i], linhaSplit[7], int.Parse(minutosPessoa[i])
});
apontamento = apontamento + pessoa[i] + " - " + minutosPessoa[i] + " minutos <br/>";
}
string[] range = EscreverRangeDiario.Split(":");
int rangeMax = int.Parse(range[1].Split("J")[1]) + pessoa.Length;
EscreverRangeDiario = range[0] + ":J" + rangeMax;
}
else
{
Console.WriteLine("Nome: ");
pessoaIndividual = Console.ReadLine();
Console.WriteLine("Minutos adicionados: ");
minutosIndividual = Console.ReadLine();
lista.Add(new List <object> {
linhaSplit[6], pessoaIndividual, linhaSplit[7], int.Parse(minutosIndividual)
});
apontamento = pessoaIndividual + " - " + minutosIndividual + " minutos <br/>";
}
}
else
{
lista.Add(new List <object> {
linhaSplit[6], linhaSplit[1], linhaSplit[7], int.Parse(linhaSplit[9])
});
apontamento = linhaSplit[1] + " - " + linhaSplit[9] + " minutos <br/>";
}
if (dividir.Equals("N"))
{
string[] range = EscreverRangeDiario.Split(":");
int rangeMax = int.Parse(range[1].Split("J")[1]) + 1;
EscreverRangeDiario = range[0] + ":J" + rangeMax;
}
var valueRange = new ValueRange
{
Values = lista
};
var update = valuesResource.Update(valueRange, idPlanilhaDiaria, EscreverRangeDiario);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var response = await update.ExecuteAsync();
Console.WriteLine($"Update row: {response.UpdatedRows}");
}
