private static string GetTicketCategory(TicketRowOnSpredSheet valueFromSheet)
{
if (valueFromSheet.VIP == 1)
{
return("VIP");
}
else if (valueFromSheet.GeneralAdult == 1)
{
return("GEN");
}
else if (valueFromSheet.GeneralChild == 1)
{
return("KID");
}
else if (valueFromSheet.SeatedAdult == 1)
{
return("SIA");
}
else if (valueFromSheet.SeatedChild == 1)
{
return("SCH");
}
else if (valueFromSheet.Under5 == 1)
{
return("UN5");
}
return("USP");
}
public static List <TicketRowOnSpredSheet> ReadGoogleSheet()
{
List <TicketRowOnSpredSheet> ticketOwners = new List <TicketRowOnSpredSheet>();
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;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Define request parameters.
//https://docs.google.com/spreadsheets/d/1RIJFHsgYNEMWqC_C2lrI0NbEBQ58lOk2dVTEPW3oNjs/edit#gid=242709292
//String spreadsheetId = "1RIJFHsgYNEMWqC_C2lrI0NbEBQ58lOk2dVTEPW3oNjs";
//String spreadsheetId = "1QfOzAuPG8zmmDWhpRRQUzne9d8YtaoTfM7YDcIggxl4";
//String spreadsheetId = "1zpOqmJRCF6E3-lNMlJqyt3LZRRDMNy4PkIAlmAc1S40";
String spreadsheetId = new AppSettingsReader().GetValue("SpredsheetId", typeof(string)).ToString();
String range = "Sheet1!A2:P";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
request.ValueRenderOption = SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum.UNFORMATTEDVALUE;
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
if (values != null && values.Count > 0)
{
Console.WriteLine(string.Format("Totl Number of lines {0}", values.Count));
Console.WriteLine("Name, Major");
var rowNumber = 1;
var tableNumber = 0;
foreach (var row in values)
{
if (row.Count > 0 && row[0] != null)
{
if (row[0].ToString().Equals("Total"))
{
break;
}
List <object> listOfItems = row.ToList();
var t = new TicketRowOnSpredSheet
{
RowNumber = rowNumber,
Owner = row[0].ToString(),
VIP = string.IsNullOrEmpty(row[1].ToString()) ? 0 : 1,
SeatedAdult = string.IsNullOrEmpty(row[2].ToString()) ? 0 : 1,
SeatedChild = string.IsNullOrEmpty(row[3].ToString()) ? 0 : 1,
GeneralAdult = string.IsNullOrEmpty(row[4].ToString()) ? 0 : 1,
GeneralChild = string.IsNullOrEmpty(row[5].ToString()) ? 0 : 1,
Under5 = string.IsNullOrEmpty(row[6].ToString()) ? 0 : 1
};
if (string.IsNullOrEmpty(listOfItems[7].ToString()))
{
t.TableNumber = 0;
}
else
{
t.TableNumber = int.Parse(listOfItems[7].ToString());
}
t.City = listOfItems[8].ToString();
t.Notes = string.Format("Booked By:" + listOfItems[9].ToString());
t.BookedBy = GetBookedPerson(listOfItems[9].ToString().Trim());
t.Paid = (listOfItems[10].ToString().Trim() == "Yes");
ticketOwners.Add(t);
rowNumber++;
}
}
// Print columns A and E, which correspond to indices 0 and 4.
}
else
{
Console.WriteLine("No data found.");
}
return(ticketOwners);
}
