/// <summary>
/// Gets link send in email.
/// </summary>
/// <param name="subject">Email subject.</param>
/// <param name="apiUser">User to establish connection.</param>
/// <param name="useCurrentDate">Use current date to filter other emails with same subjects.</param>
/// <returns>Link.</returns>
public static string GetLink(string subject, GoogleApiUser apiUser, bool useCurrentDate = false)
{
var service = ConnectToGmailApi(apiUser.SecretFile, apiUser.SecretFileReworked, apiUser.ApiName);
var dataFromEmail = GetDataFromEmail(service, apiUser.Email, LinkRegex, subject, useCurrentDate);
return(dataFromEmail == string.Empty ? string.Empty : dataFromEmail.Substring(0, dataFromEmail.Length - 1));
}
/// <summary>
/// Checks the email is received.
/// </summary>
/// <param name="apiUser">User to establish connection.</param>
/// <param name="emailSubject">Email subject.</param>
public static void CheckEmailIsReceived(GoogleApiUser apiUser, string emailSubject)
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB");
var counter = 0;
while (counter < 4)
{
Thread.Sleep(30000);
Browser.GetDriver().Navigate().Refresh();
counter++;
}
var service = ConnectToGmailApi(apiUser.SecretFile, apiUser.SecretFileReworked, apiUser.ApiName);
var result = new List <Message>();
var request = service.Users.Messages.List(apiUser.Email);
var response = request.Execute();
result.AddRange(response.Messages);
var message = service.Users.Messages.Get(apiUser.Email, result[0].Id).Execute();
var subject = message.Payload.Headers[19].Value;
var date = message.Payload.Headers[18].Value.Substring(0, message.Payload.Headers[17].Value.Length - 14);
var currentDate = DateTime.Now;
var strCurrentDate = $"{currentDate:ddd, d MMM yyyy}";
Assert.IsTrue(DateTimeOffset.Parse(date.ToLower().Trim()).UtcDateTime == DateTimeOffset.Parse(strCurrentDate.ToLower().Trim()).UtcDateTime,
"Email date is incorrect. Should be: " + strCurrentDate + " but: " + date);
Assert.IsTrue(subject.ToLower().Trim() == emailSubject.ToLower().Trim());
}
/// <summary>
/// Exports spreadsheet with test data from google spreadsheet.
/// </summary>
/// <param name="apiUser">User to establish connection.</param>
/// <param name="spreadSheetId">Spreadsheet Id.</param>
public static void ExportTestDataTable(GoogleApiUser apiUser, string spreadSheetId)
{
var driveService = ConnectToDriveApi(apiUser);
var request = driveService.Files.Export(spreadSheetId, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
var stream = new MemoryStream();
// Add a handler which will be notified on progress changes.
// It will notify on each chunk download and when the
// download is completed or failed.
request.MediaDownloader.ProgressChanged +=
(progress) =>
{
switch (progress.Status)
{
case DownloadStatus.Downloading:
{
Console.WriteLine(progress.BytesDownloaded);
break;
}
case DownloadStatus.Completed:
{
Console.WriteLine("Download complete.");
break;
}
case DownloadStatus.Failed:
{
Console.WriteLine("Download failed.");
break;
}
}
};
request.Download(stream);
var filePath = $"{SettingsHelper.ResourcePath}{DataFile}";
if (File.Exists(filePath))
{
File.Delete(filePath);
}
using (var fileStream = File.Create(filePath))
{
stream.Seek(0, SeekOrigin.Begin);
stream.CopyTo(fileStream);
}
for (var i = 0; i < 30; i++)
{
if (Directory.EnumerateFiles(filePath).Any())
{
break;
}
Thread.Sleep(1000);
}
}
/// <summary>
/// Gets last or next column Id.
/// </summary>
/// <param name="spreadSheetId">Spreadsheet Id.</param>
/// <param name="sheet">Sheet name.</param>
/// <param name="isNext">Return the last column name or the next to the last.</param>
/// <param name="apiUser">User to establish connection.</param>
/// <returns>Column Id</returns>
public static string GetLastColumnId(string spreadSheetId, string sheet, GoogleApiUser apiUser, bool isNext = true)
{
var service = ConnectToSheetsApi(apiUser);
var request = service.Spreadsheets.Values.Get(spreadSheetId, sheet);
request.MajorDimension = SpreadsheetsResource.ValuesResource.GetRequest.MajorDimensionEnum.COLUMNS;
var response = request.Execute();
return(isNext ? GetExcelColumnName(response.Values.Count + 1) : GetExcelColumnName(response.Values.Count));
}
/// <summary>
/// Gets test data from google spreadsheet directly or from exported locally in .xlsx file.
/// </summary>
/// <param name="range">Range of data to get (e.g. "Tab!A3:A3").</param>
/// <param name="spreadSheetId">Spreadsheet Id.</param>
/// <param name="apiUser">User to establish connection.</param>
/// <returns>Data as string.</returns>
public static string GetData(string range, string spreadSheetId, GoogleApiUser apiUser)
{
var firstSymbol = range.IndexOf("!", StringComparison.Ordinal) + 1;
var lastSymbol = range.IndexOf(":", StringComparison.Ordinal) - firstSymbol;
var cell = range.Substring(firstSymbol, lastSymbol);
var sheetName = range.Substring(0, range.IndexOf("!", StringComparison.Ordinal));
return(SettingsHelper.GetValue(SettingsValues.UseLocalData) == "yes"
? SettingsHelper.GetXlsValue(sheetName, cell)
: GetDataDirectly(range, spreadSheetId, apiUser));
}
private static SheetsService ConnectToSheetsApi(GoogleApiUser apiUser)
{
UserCredential credential;
using (var stream = new FileStream(Path.GetFullPath(AuthPath + apiUser.SecretFile), FileMode.Open, FileAccess.Read))
{
var credPath = Path.GetFullPath(AuthPath + apiUser.SecretFileReworked);
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result;
}
var service = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential, ApplicationName = apiUser.ApiName
});
return(service);
}
/// <summary>
/// Add new empty columns.
/// </summary>
/// <param name="count">Count of columns to add.</param>
/// <param name="spreadSheetTabId">Spreadsheet tab Id number from spreadsheet url (e.g. 0 in ...gid=0).</param>
/// <param name="spreadSheetId">Spreadsheet Id.</param>
/// <param name="apiUser">User to establish connection.</param>
public static void AppendColumns(int count, string spreadSheetId, GoogleApiUser apiUser, int spreadSheetTabId = 0)
{
var service = ConnectToSheetsApi(apiUser);
var body = new BatchUpdateSpreadsheetRequest();
var requestToBody = new Request
{
AppendDimension = new AppendDimensionRequest
{
Dimension = DimensionColumnType,
Length = count,
SheetId = spreadSheetTabId
}
};
body.Requests = new List <Request> {
requestToBody
};
var request = service.Spreadsheets.BatchUpdate(body, spreadSheetId);
request.Execute();
}
/// <summary>
/// Sets cell color.
/// </summary>
/// <param name="range">Range to set (e.g. "Tab!A3:A3").</param>
/// <param name="spreadSheetTabId">Spreadsheet tab Id number from spreadsheet url (e.g. 0 in ...gid=0).</param>
/// <param name="color">Cell color.</param>
/// <param name="spreadSheetId">Spreadsheet Id.</param>
/// <param name="apiUser">User to establish connection.</param>
public static void SetCellColor(string range, Color color, string spreadSheetId, GoogleApiUser apiUser, int spreadSheetTabId = 0)
{
var regex = new Regex(ColumnRegexPattern);
var startColumnNumber = GetExcelColumnNumber(regex.Matches(range)[1].Value) - 1;
var endColumnNumber = GetExcelColumnNumber(regex.Matches(range)[2].Value);
regex = new Regex(RowRegexPattern);
var startRowNumber = int.Parse(regex.Matches(range)[0].Value) - 1;
var endRowNumber = int.Parse(regex.Matches(range)[1].Value);
var service = ConnectToSheetsApi(apiUser);
var rowData = new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredFormat = new CellFormat
{
BackgroundColor = color
}
}
}
};
var requestToBody = new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = new GridRange
{
SheetId = spreadSheetTabId,
StartRowIndex = startRowNumber,
EndRowIndex = endRowNumber,
StartColumnIndex = startColumnNumber,
EndColumnIndex = endColumnNumber
},
Rows = new List <RowData>
{
rowData
},
Fields = "*"
}
};
var body = new BatchUpdateSpreadsheetRequest {
Requests = new List <Request> {
requestToBody
}
};
service.Spreadsheets.BatchUpdate(body, spreadSheetId).Execute();
}
/// <summary>
/// Sets single value from one cell.
/// </summary>
/// <param name="spreadSheetId">Spreadsheet Id.</param>
/// <param name="range">Range to set (e.g. "Tab!A3:A3").</param>
/// <param name="apiUser">User to establish connection.</param>
/// <param name="value">Value to set.</param>
public static void SetSheetSingleValue(string spreadSheetId, string range, string value, GoogleApiUser apiUser)
{
var service = ConnectToSheetsApi(apiUser);
var valueRange = new ValueRange {
MajorDimension = DimensionColumnType
};
var valueList = new List <object> {
value
};
valueRange.Values = new List <IList <object> > {
valueList
};
var request = service.Spreadsheets.Values.Update(valueRange, spreadSheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
request.Execute();
}
/// <summary> /// Gets values from google spreadsheet from specified range. /// </summary> /// <param name="spreadsheetId">Spreadsheet Id.</param> /// <param name="range">Range to receive (e.g. "Tab!A3:D6").</param> /// <param name="apiUser">User to establish connection.</param> /// <returns>IList<IList<object>>.</returns> public static IList <IList <object> > GetValuesRange(string spreadsheetId, string range, GoogleApiUser apiUser) => ConnectToSheetsApi(apiUser).Spreadsheets.Values.Get(spreadsheetId, range).Execute().Values;
/// <summary>
/// Gets data directly from google spreadsheet
/// </summary>
/// <param name="range">Range of data to get (e.g. "Tab!A3:A3").</param>
/// <param name="spreadSheetId">Spreadsheet Id.</param>
/// <param name="apiUser">User to establish connection.</param>
/// <returns>Data as string.</returns>
public static string GetDataDirectly(string range, string spreadSheetId, GoogleApiUser apiUser = null)
{
var service = ConnectToSheetsApi(apiUser);
return(service.Spreadsheets.Values.Get(spreadSheetId, range).Execute().Values[0][0].ToString());
}
/// <summary>
/// Store range of values.
/// </summary>
/// <param name="range">Range to set (e.g. "Tab!A3:D3").</param>
/// <param name="spreadsheetId">Spreadsheet Id.</param>
/// <param name="values">Values to set.</param>
/// <param name="apiUser">User to establish connection.</param>
public static void StoreRange(string range, string spreadsheetId, IList <IList <object> > values, GoogleApiUser apiUser = null)
{
var valuesRange = new ValueRange
{
MajorDimension = DimensionColumnType,
Values = values
};
var request = ConnectToSheetsApi(apiUser).Spreadsheets.Values.Update(valuesRange, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
request.Execute();
}