Hi Mark,
I've found
this Python script for downloading a Google Docs spreadsheet, and here's my attempt to convert it to JScript. Hope it helps!
function Test()
{
var strEmail = "username@gmail.com";
var strPassword = "password123456";
var strKey = "0Aoc...JLS0E"; // Spreadsheet key
var strFormat = "csv";
var strFileName = "E:\\spreadsheet.csv";
var auth = GetGDocsAuthToken(strEmail, strPassword);
SaveGDocsSpreadsheet(auth, strKey, strFormat, strFileName);
}
/*
Logs in to Google Docs and returns the authentication token
Parameters:
Email - Google account e-mail, e.g. username@gmail.com
Password - Google account password.
*/
function GetGDocsAuthToken(Email, Password)
{
var oRequest = new ActiveXObject("MSXML2.XMLHTTP")
oRequest.open("POST", "https://www.google.com/accounts/ClientLogin", false);
oRequest.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
oRequest.send("accountType=HOSTED_OR_GOOGLE&Email=" + Email + "&Passwd=" + Password + "&service=wise");
var strAuth = "";
if (oRequest.Status != 200)
{
Log.Error("Failed to log in to Google Docs. Error " + oRequest.Status + ": " + oRequest.StatusText);
}
else
{
strAuth = oRequest.responseText.split("Auth=")[1];
}
return strAuth;
}
/*
Downloads a Google Docs spreadsheet to the specified file.
Parameters:
AuthToken - authentication token obtained using the GetGDocsAuthToken function
SpreadsheetKey - the spreadsheet key as seen in the browser's address bar:
https://docs.google.com/spreadsheet/ccc?key=0Aoc...JLS0E
Format - "xls", "csv", "pdf", "ods", "tsv" or "html"
FileName - The fully-qualified name of the file to create (existing files will be overwritten)
*/
function SaveGDocsSpreadsheet(AuthToken, SpreadsheetKey, Format, FileName)
{
var adTypeBinary = 1;
var adSaveCreateOverWrite = 2;
var oRequest = new ActiveXObject("MSXML2.XMLHTTP");
oRequest.open("GET", "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + SpreadsheetKey
+ "&exportFormat=" + Format, false);
oRequest.setRequestHeader("Authorization", "GoogleLogin auth=" + AuthToken);
oRequest.setRequestHeader("GData-Version", "3.0");
oRequest.send();
if (oRequest.Status != 200)
{
Log.Error("Failed to download the shreadsheet. Error " + oRequest.Status + ": " + oRequest.StatusText);
}
else
{
var oStream = new ActiveXObject("ADODB.Stream");
oStream.Type = adTypeBinary;
oStream.Open();
oStream.Write(oRequest.responseBody);
oStream.SaveToFile(FileName, adSaveCreateOverWrite);
oStream.Close();
Log.Message("Spreadsheet was saved to the \"" + FileName + "\" file.");
}
}
Notes:
* According to the
Google Docs API reference, valid formats for downloading spreadsheets are: XLS, CSV, PDF, ODS, TSV and HTML. JSON isn't supported. Also, only the first worksheet is returned in CSV and TSV by default, through you can specify the
gid parameter to request specific worksheets.
* If your Google account uses
2-step verification, you need to generate an
application-specific password for use in the script instead of the Google account password.
| I've been able to get XML from another website with our data conditions using ActiveXObject("Microsoft.XMLHTTP"), and then putting that into a DOM and parsing. I'd rather not use ActiveX. Can we use XMLHttpRequest? |
TestComplete uses
Microsoft JScript for Windows Script Host, which is a little bit different from browser JavaScript. For example, JScript doesn't have a built-in XMLHttpRequest object - it's available as the Microsoft.XMLHTTP ActiveX object instead. I'd like to note that unlike in-browser scripting, there's nothing wrong with using ActiveX objects in test automation scripts. Actually, they provide your tests with more functionality, e.g. talking to databases, automating Microsoft Office applications, or, like in the above script, sending HTTP requests.
