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); }