Forum Discussion

soda's avatar
soda
Contributor
13 years ago

Http request to download Google Doc spreadsheet

Using JavaScript, I am trying to access a Google Docs spreadsheet using XMLHttpRequest() but am having trouble.  Is this possible with TC 8?  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?  Has anyone else been able to use JavaScript in TestComplete to download data from Google Docs spreadsheet via URL?  Ideally, I'd like the spreadsheet to be either csv or Json.

Thanks,

Mark

  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    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.