private async Task LogToTable(string listname, string message, string user = null, MessageType?messageType = null) { var service = await _sheetServiceProvider.GetService(); var range = $"{listname}!A:D"; SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW; SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS; ValueRange valueRange = new ValueRange(); var oblist = new List <object>(); if (messageType != null) { oblist.Add( messageType == MessageType.Outgoing ? "Исходящее" : messageType == MessageType.Incoming ? "Входящее" : "Системное" ); } oblist.AddRange(new List <object>() { string.Format(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")), message, user }); valueRange.Values = new List <IList <object> > { oblist }; SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, _configService.Config.SpreadsheetLog.Id, range); request.ValueInputOption = valueInputOption; request.InsertDataOption = insertDataOption; request.ResponseDateTimeRenderOption = SpreadsheetsResource.ValuesResource.AppendRequest.ResponseDateTimeRenderOptionEnum.FORMATTEDSTRING; AppendValuesResponse response = await request.ExecuteAsync(); }
public async Task <IEnumerable <INeedSend> > GetMessages(Config config) { var service = await _sheetServiceProvider.GetService(); var result = new List <GoogleTableValueRow>(); foreach (var spreadsheet in config.Spreadsheets) { var spreadsheetId = spreadsheet.Id; foreach (var list in spreadsheet.Lists) { var allColumns = new string[] { list.Date, list.Status, list.IsSendedColumn, list.MessageText, list.TgUser }; var range = _cellService.GetFullRange(allColumns); var dateIndex = _cellService.GetCellIndex(range, list.Date); var statusIndex = _cellService.GetCellIndex(range, list.Status); var sendedIndex = _cellService.GetCellIndex(range, list.IsSendedColumn); var textIndex = _cellService.GetCellIndex(range, list.MessageText); var tgUserIndex = _cellService.GetCellIndex(range, list.TgUser); SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, $"{list.ListName}!{range}"); request.MajorDimension = SpreadsheetsResource.ValuesResource.GetRequest.MajorDimensionEnum.ROWS; request.DateTimeRenderOption = SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum.FORMATTEDSTRING; ValueRange response = request.Execute(); var rowNum = 0; foreach (var row in response.Values) { rowNum++; if (rowNum == 1) { continue; } var strDate = (dateIndex < row.Count()) ? row[dateIndex] : null; var status = (statusIndex < row.Count()) ? row[statusIndex]?.ToString().ToLower() : null; var messageAlreadySended = (sendedIndex < row.Count()) ? row[sendedIndex]?.ToString().ToLower() : null; var text = (textIndex < row.Count()) ? row[textIndex]?.ToString() : null; var tg = (tgUserIndex < row.Count()) ? row[tgUserIndex]?.ToString() : null; DateTime dt; //Get date var formats = new[] { "dd/MM/yyyy", "dd/MM/yyyy HH:mm:ss", "yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss" }; DateTime?date = strDate != null && DateTime.TryParseExact(strDate.ToString(), formats, System.Globalization.DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None, out dt) ? dt : (DateTime?)null; if ((status != "надо отправить" && status != "надо отправить sms") || messageAlreadySended == "да") { continue; } var item = new GoogleTableValueRow() { LastModifiedDate = date == null ? DateTime.MinValue : (DateTime)date, Text = text, To = tg, Table = spreadsheetId, CellForUpdate = $"{list.ListName}!{list.IsSendedColumn}{rowNum}", SenderType = status == "надо отправить" ? SenderType.Telegram : SenderType.Sms }; result.Add(item); } } } return(result); }