Trying to login into Google in order to download Google Trends data

Question

I am trying to:

Login to Google
Download CSV data from Google Trends

I am succeeding in (1) but not in (2). I get returned an authorization-token from Google, and am sending it with the subsequent request to Trends, but nevertheless Google then returns an error: "You must be signed in to export data from Google Trends":

// http://code.google.com/apis/accounts/docs/AuthForInstalledApps.html
$data = array(
  'accountType' => 'GOOGLE',
  'Email'       => 'my.email@gmail.com',
  'Passwd'      => 'my.password',
  'service'     => 'trendspro',
  'source'      => 'company-application-1.0'
);

$ch = curl_init();
  curl_setopt($ch, CURLOPT_URL, "https://www.google.com/accounts/ClientLogin");
  curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
  curl_setopt($ch, CURLOPT_HTTPAUTH, false);
  curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
  $response = curl_exec($ch);

  preg_match("/Auth=([a-z0-9_\-]+)/i", $response, $matches);

  // We now have an authorization-token
  $headers = array(
    "Authorization: GoogleLogin auth=" . $matches[1],
    "GData-Version: 3.0"
  );

  curl_setopt($ch, CURLOPT_URL, "http://www.google.com/trends/viz?q=MSFT&date=2011-2&geo=all&graph=all_csv&sort=0&sa=N");
  curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
  curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
  curl_setopt($ch, CURLOPT_HEADER, false);
  curl_setopt($ch, CURLOPT_POST, false);
  $csv = curl_exec($ch);
curl_close($ch);

// Returns : "You must be signed in to export data from Google Trends"
// Expected: CSV data stream
print_r($csv);

For some reason, the auth-tokens I am sending to Google Trends, is not being accepted or ignored. I don't know exactly what happens, since no additional error-information is given.

Does anyone see what I am doing wrong? If you can get it to work, meaning that Google is returning the CSV data, then the bounty is yours and we both have a late Christmas present :-)

So I figured out the problem has nothing to do with cURL. What I did was:

Go to https://www.google.com/accounts/ClientLogin?accountType=GOOGLE&Email=my.email@gmail.com&Passwd=my.password&service=trendspro&source=ding-dang-1. The return is:

SID=DQAAAMUAAADMqt...aYPaYniC_iW
LSID=DQAAAMcAAACI5...YDTBDt_xZC9
Auth=DQAAAMgAAABm8...trXgqNv-g0H

I copy the returned Auth token: DQAAAMgAAABm8...trXgqNv-g0H
I send a GET request using the POSTman Chrome extension to http://www.google.com/trends/viz?q=MSFT&date=2011-2&geo=all&graph=all_csv&sort=0&sa=N using the header:

GData-Version: 3.0     
Authorization: GoogleLogin auth=DQAAAMgAAABm8...trXgqNv-g0H

I get returned:

headers:

Date: Tue, 27 Dec 2011 00:17:20 GMT
Content-Encoding: gzip
Content-Disposition: filename=trends.csv
Content-Length: 97
X-XSS-Protection: 1; mode=block
Server: Google Trends
X-Frame-Options: SAMEORIGIN
Content-Type: text/csv; charset=UTF-8
Cache-Control: private

data:

You must be signed in to export data from Google Trends

In other words, I'm sending headers as defined by Google on http://code.google.com/apis/accounts/docs/AuthForInstalledApps.html but no luck getting a proper return. An there is about * no * info on the Interwebs concerning this. Who knows what the problem is here?

Have you checked here http://stackoverflow.com/questions/4986758/oauth-google-trends-download-csv-file — Pateman, Dec 26 '11 at 17:34
Thanks for the tip. Your link brought me to http://stackoverflow.com/questions/1656446/download-csv-from-google-insight-for-search, but this also doesn't fix the problem. I'm doing exactly as explained over there but no luck, and I can't see what I'm doing wrong... — Pr0no, Dec 26 '11 at 19:47
Not sure if this helps, but one guy had a problem with accessing HTTPS websites using cURL and his problem was solved here: http://stackoverflow.com/questions/316099/cant-connect-to-https-site-using-curl-returns-0-length-content-instead-what-c — Pateman, Dec 26 '11 at 20:00
As said, curl_error() doesn't return anything and adding curl_setopt($ch, CURLOPT_CAINFO, dirname(__FILE__)."/cacert.pem"); doesn't make any difference either: "You must be signed in to export data from Google Trends". This is driving me insane... — Pr0no, Dec 26 '11 at 20:11
curl_getinfo returns: Array ( [url] => http://www.google.com/trends/viz?q=MSFT&date=2011-2&geo=all&graph=all_csv&sort=0&sa=N [content_type] => text/csv; charset=UTF-8 [http_code] => 200 [header_size] => 274 [request_size] => 458 [filetime] => -1 [ssl_verify_result] => 20 [redirect_count] => 0 [total_time] => 0.39 [namelookup_time] => 0 [connect_time] => 0.047 [pretransfer_time] => 0.047 [size_upload] => 0 [size_download] => 112 [speed_download] => 287 [speed_upload] => 0 [download_content_length] => 881 [upload_content_length] => -1 [starttransfer_time] => 0.39 [redirect_time] => 0 ) — Pr0no, Dec 26 '11 at 21:56

Arvin · Accepted Answer · 2012-04-26T15:44:38.357

After checking your code, the problem is that Google Trends needs the SID key and not Auth. Here's the code I wrote to download the csv's

<?php

header('content-type: text/plain');

// Set account login info
$data['post'] = array(
  'accountType' => 'HOSTED_OR_GOOGLE',  // indicates a Google account
  'Email'       => '',  // full email address
  'Passwd'      => '',
  'service'     => 'trendspro', // Name of the Google service
  'source'      => 'codecri.me-example-1.0' // Application's name, e.g. companyName-applicationName-versionID
);

$response = xhttp::fetch('https://www.google.com/accounts/ClientLogin', $data);

// Test if unsuccessful
if(!$response['successful']) {
    echo 'response: '; print_r($response);
    die();
}

// Extract SID
preg_match('/SID=(.+)/', $response['body'], $matches);
$sid = $matches[1];

// Erase POST variables used on the previous xhttp call
$data = array();

// Set the SID in cookies
$data['cookies'] = array(
    'SID' => $sid
);

This uses my xhttp class, a cURL wrapper.

Your xhttp class link redirects to a random ad page – Jones03 Apr 02 '15 at 03:10 — Jones03, Apr 02 '15 at 03:10

score 2 · Answer 2 · answered Apr 04 '12 at 14:35

2

Right tool for the right job, have you considered PhantomJS?

It could be even more readable.

answered Apr 04 '12 at 14:35

Kamil Tomšík

2,415
2
28
32

score 0 · Answer 3 · answered Dec 29 '11 at 05:08

Hmm, I haven't worked w/ Google APIs yet, but wanted to get into the Google Apps API for an upcoming project so started digging around. My guess is that since Trends isn't in the list of services that implement the Google Data Protocol even though you're authenticating correctly / successfully (verified by the Auth section of the response) Google won't honor the auth token for Trends (again this is a best guess!).

My thought then is that you would need to use a traditional method to login to Google Trends and download the CSV, which is to say, act like a browser from your client rather than an app. Not sure on that, but I found an older python client on github that claims to be able to download CSVs from Google Trends. There's also a blog post on the client. You may be able to reverse engineer it into a PHP equivalent, best of luck!

score 0 · Answer 4 · answered Dec 29 '11 at 08:11

It appears that Google doesn't officially condone any script-based usage of trends. This would explain why your auth is failing as it's not accepting api connections. Try using a webclient library to capture a cookie and use it to harvest data. This was the solution used by that previously linked python client on git.

On a potentially unrelated note, you use service trendspro but this is the service name for google insights. Try just service => 'trends'

Trying to login into Google in order to download Google Trends data

4 Answers4