private void button2_Click(object sender, EventArgs e)
{
NotMain();
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest request = sheetsService.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
Data.ValueRange response = request.Execute();
//characters = new Dictionary<string, Character>();
foreach (var character in response.Values)
{
string player = character[0] as string;
string name = character[1] as string;
string race = character[2] as string;
int str = Convert.ToInt32(character[3]);
int dex = Convert.ToInt32(character[4]);
int intellegence = Convert.ToInt32(character[5]);
int level = Convert.ToInt32(character[6]);
int exp = Convert.ToInt32(character[7]);
characters.Add(name, new Character(name, player, race, str, dex, intellegence, level, exp));
}
int numChars = characters.Count - 1;
Random rand = new Random();
int r = rand.Next(0, numChars);
int r2 = rand.Next(0, numChars);
}
/// <summary>
/// Метод реализует проверку на наличие пустой строки для удобства записи
/// </summary>
/// <param name="service"></param>
/// <param name="spreadsheetId"></param>
/// <returns></returns>
private static int FindFreeRow(SheetsService service, string spreadsheetId)
{
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = 0;
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = 0;
bool emptyRow = false;
int rowNumber = 1;
while (emptyRow == false)
{
string range = $"A{rowNumber}:D{rowNumber}";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
ValueRange response = request.Execute();
var jsonobj = JsonConvert.SerializeObject(response);
dynamic obj = JsonConvert.DeserializeObject(jsonobj);
var values = obj.values;
if (values != null)
{
rowNumber++;
continue;
}
emptyRow = true;
}
return(rowNumber - 1);
}
/// <summary>
/// Метод реализует проверку на наличие пустой строки для удобства записи
/// </summary>
/// <param name="service"></param>
/// <param name="spreadsheetId"></param>
/// <param name="tenner"></param>
/// <returns></returns>
private static async Task <int> FindFreeRow(SheetsService service, string spreadsheetId, int tenner)
{
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = 0;
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = 0;
int rowNumber = 0;
int firstcount = 1;
if (tenner < 10)
{
firstcount = tenner;
}
else
{
firstcount = tenner - 10;
}
int secondcount = firstcount + 10;
for (rowNumber = firstcount; rowNumber < secondcount; rowNumber++)
{
string range = $"A{rowNumber}";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
try
{
ValueRange response = await request.ExecuteAsync();
var jsonobj = JsonConvert.SerializeObject(response);
dynamic obj = JsonConvert.DeserializeObject(jsonobj);
var values = obj.values;
if (values != null)
{
if (rowNumber == secondcount - 1)
{
firstcount += 10;
secondcount += 10;
}
}
if (values == null)
{
return(rowNumber);
}
}
catch
{
continue;
}
}
return(rowNumber);
}
public async Task <IList <IList <object> > > GetSheetData(GoogleSheetSettings sheetSettings)
{
// How values should be represented in the output.
// The default render option is ValueRenderOption.FORMATTED_VALUE.
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0; // TODO: Update placeholder value.
// How dates, times, and durations should be represented in the output.
// This is ignored if value_render_option is
// FORMATTED_VALUE.
// The default dateTime render option is [DateTimeRenderOption.SERIAL_NUMBER].
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest request = _sheetsService.Spreadsheets.Values.Get(sheetSettings.SheetId, sheetSettings.Range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
var response = await request.ExecuteAsync();
return(response.Values);
}
static IList <IList <Object> > ConnectToSheets()
{
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/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes, "user", CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
String spreadsheetId = "1ZhMmd8DAGt28MWlmy1QrWHIm3d0ln3Zi4x66Qs4bmJY";
String sheet = DateTime.Now.ToString("dddd");
String range = DateTime.Now.ToString("dddd") + "!A1:M10";
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
Google.Apis.Sheets.v4.Data.ValueRange response = request.Execute();
Console.WriteLine(JsonConvert.SerializeObject(response));
IList <IList <Object> > values = response.Values;
return(values);
}
public string ConsultaGoogleSheets()
{
try
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "Google-SheetsSample/0.1",
});
string spreadsheetId = "1_iIbyZZAzO02HOA-3l38FCJxq9Mm_MHaZ8l7NvFtw44";
string range = "A1:FZ3000";
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest request = sheetsService.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
Google.Apis.Sheets.v4.Data.ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
IList <Object> listaParametros = values[0];
Diferencia(listaParametros);
InsertShopify(values, listaParametros);
return(JsonConvert.SerializeObject(response));
}
catch (Exception e)
{
Conexion.Conexiones c = new Conexiones();
c.EjecutarLog(0, e.ToString(), "ERROR CONSULTA DATA SHOPIFY");
return(e.ToString());
}
}
public static List <SpotifyInfo> GetAllSongsFromStaticSheet()
{
try
{
ServiceAccountCredential credential1;
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string serviceAccountEmail = "*****@*****.**";
string jsonfile = "trackingNewData.json";
string spreadsheetID = "1XsrVqD-Fz1ggj2VX6wEbpt_FO0qguTMJR5YWnytYXV4";
string range = "All";
using (Stream stream = new FileStream(@jsonfile, FileMode.Open, FileAccess.Read, FileShare.Read))
{
credential1 = (ServiceAccountCredential)
GoogleCredential.FromStream(stream).UnderlyingCredential;
var initializer = new ServiceAccountCredential.Initializer(credential1.Id)
{
User = serviceAccountEmail,
Key = credential1.Key,
Scopes = Scopes
};
credential1 = new ServiceAccountCredential(initializer);
}
var serices = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential1,
ApplicationName = ApplicationName,
});
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest request = serices.Spreadsheets.Values.Get(spreadsheetID, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
values.RemoveAt(0);
List <SpotifyInfo> listSongs = new List <SpotifyInfo>();
foreach (var item in values)
{
if (item.Count != 0)
{
SpotifyInfo song = new SpotifyInfo();
for (int i = 0; i < item.Count; i++)
{
if (i == 1)
{
if (item[i] != null)
{
song.TrackTitle = item[i].ToString();
}
}
else if (i == 2)
{
song.Code = item[i].ToString();
}
else if (i == 3)
{
if (item[i] != null)
{
song.Artists = item[i].ToString();
}
}
else if (i == 4)
{
if (!string.IsNullOrEmpty(item[i].ToString()))
{
song.LinkSpotify = item[i].ToString();
song.TrackId = item[i].ToString().Split(new string[] { "=" }, StringSplitOptions.None)[1].Split(new string[] { ":" }, StringSplitOptions.None)[2];
song.AlbumId = item[i].ToString().Split(new string[] { "?" }, StringSplitOptions.None)[0].Split(new string[] { "https://open.spotify.com/album/" }, StringSplitOptions.None)[1];
}
}
else if (i == 5)
{
if (item[i] != null)
{
song.Genres = item[i].ToString();
}
}
else if (i == 6)
{
if (item[i] != null)
{
song.Country = item[i].ToString();
}
}
else if (i == 7)
{
if (item[i] != null)
{
song.ReleaseDate = item[i].ToString();
}
}
else if (i == 8)
{
if (item[i] != null)
{
song.Popularity = item[i].ToString();
}
}
else if (i == 9)
{
if (item[i] != null)
{
if (item[i].ToString() != "")
{
song.StreamCount = long.Parse(item[i].ToString());
}
}
}
}
listSongs.Add(song);
}
}
return(listSongs);
}
catch (Exception ex)
{
return(null);
}
}
public static List <SpotifyInfo> GetAllSongsFromStaticSheetVN()
{
try
{
ServiceAccountCredential credential1;
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string serviceAccountEmail = "*****@*****.**";
string jsonfile = "trackingNewData.json";
string spreadsheetID = "1k0G4J_HXLzOvaOvoUPHt8m7S-ogMxaeF53SE6ZfgXfo";
string range = "Danh sách nhạc tổng!A2:K";
using (Stream stream = new FileStream(@jsonfile, FileMode.Open, FileAccess.Read, FileShare.Read))
{
credential1 = (ServiceAccountCredential)
GoogleCredential.FromStream(stream).UnderlyingCredential;
var initializer = new ServiceAccountCredential.Initializer(credential1.Id)
{
User = serviceAccountEmail,
Key = credential1.Key,
Scopes = Scopes
};
credential1 = new ServiceAccountCredential(initializer);
}
var serices = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential1,
ApplicationName = ApplicationName,
});
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0;
SpreadsheetsResource.ValuesResource.GetRequest request = serices.Spreadsheets.Values.Get(spreadsheetID, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
List <SpotifyInfo> listSongs = new List <SpotifyInfo>();
foreach (var item in values)
{
if (item.Count >= 6)
{
SpotifyInfo song = new SpotifyInfo();
song.TrackTitle = item[4].ToString();
song.Artists = item[5].ToString();
song.Range = "I" + (values.IndexOf(item) + 2).ToString() + ":" + "J" + (values.IndexOf(item) + 2).ToString();
listSongs.Add(song);
}
}
return(listSongs);
}
catch (Exception ex)
{
return(null);
}
}
private static ValueRange GetDataByColumnFromGoogleSheet()
{
try
{
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);
}
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential
});
SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0; // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = valueRenderOption;
request.DateTimeRenderOption = dateTimeRenderOption;
request.MajorDimension = MajorDimensionEnum.ROWS;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.ValueRange response = request.Execute();
return(response);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
