Google API Keyword Spreadsheet

14 comments
Source Title:
Google Queries and Backlink Checking to Excel Spreadsheets
Story Text:

Tara spots an interesting tool for retrieving keyword/backlink data from Google and plonking into Excel. Now, we know the Google backlink data is close to useless, so how about someone technically minded have a bash at porting that to the Yahoo! API? (arguably equally useless since the index upgrade, but hey, still better than Google..)

Comments

just got it to work

It wasn't easy as it involved installing additional stuff from microsoft.com. Cools stuff btw. but unfortunately I can't use it for client work.

I didn't have that stuff on my development machine precisely because I do such stuff. I can't expect that my customers will go through the hassle of installing all kinds of extras just to get a spreadsheet to work. Most of them aren't even allowed to as they're on corporate PC's.

Anyway, apparently it sends a standard query off to Google and then 10 "link:" requests, so that's 11 queries per push of the OK button (limit is 1.000 per day, so that's 90 queries).

Done

Only took so long because there are some differences between the result count on yahoo.com and the count you get with the API. The web results seem to be larger in general.

I've checked, and double-checked, and triple-checked, and the numbers are just not the same. Here's an example with the query "madonna":

Got the code claus? That was

Got the code claus?

That was pretty quick! heh..

Quick it was

Yeah, it was pretty quick, but now I've more or less changed the whole thing.

Btw. some of the Google counts are also different from the web counts, just like Yahoo (total number of results, mainly). And sometimes they change from query to query. It's probably got to do with different datacenters handling the queries, I think.

Anyway, I'll just put up a page to host it, so that people can download from there if that's okay? Or should I find some geocities page in stead or what?

No, if you want to do it

No, if you want to do it here, go right ahead claus, just send me the file (nick - threadwatch.org) and i'll upload it to the server - then we'll make it available

or if you'd prefer to host it, or post it somewhere else, let me know and we'll just link to it!

link

- here's a link. I think I may like to update it sometime, so it makes most sense to have it nearby ;-)

Anyway, feel free to mirror the file here at TW, that's perfectly fine with me. Oh, if you can't make it work, try installing both of the Microsoft files I mention on that page.

windows XP

Doesn't work on office XP keeps asking me to install office 2003.

It's made on XP :-)

It's made on XP, so it's not the OS. It should work on Win 2000 as well, as long as you have the right Excel version. (Possibly also on mac? Dunno)

It will not work with Excel versions / MS Office versions earlier than 2003 (version 11). That is the most recent version.

The reason for this is that previous versions of Excel/Office could not handle XML.

Hey how about a little feedback?

Is it useful or is it b0rken? Would it be better if "such and such"? Is something weird or don't look right, or is everything just perfect?

I don't keep track of how many of you download this stuff, but some of you must have. I would really appreciate a little feedback, even if it's just "Hey cool, but why don't you GNU it" or whatever.

It's free after all and fully functional - it only takes two minutes to do a test drive (at most). Comments will help me decide if I would like to offer some downloads another time, or perhaps develop it into something even more useful.

It's no fun for me if I don't get any feedback, and -- as you see -- I'm not charging for a toy as simple as that, so tell me about it ;-)

I don't think my problem is

I don't think my problem is the OS it's the version of office (running office XP). If you want I'll shoot you a screen shot of the error.

I'll give it a post of it's

I'll give it a post of it's own a little later claus :)

A little Help

I know there's no "official" support for this spreadsheet but wonder if anyone could help as this could save me so much time if I could only get it working.

Currently getting an run-time error reading:

"No Deserializer found to deserialize a ':key' using encoding style 'http://schemes.xmlsoap/encoding/'

Can anyone help or give me any idea of what I may need to do to get this running properly? Im using Excell 2003 v 11.6355......

Any help greatly appreciated.

Everyone gets that error now, sorry about that

It worked nicely for two full days and then everyone started getting that exact error. Me included. Here's a quote from some web page suggesting that it's even normal:

When processing inbound messages generated by Microsoft's SOAP toolkits, you'll likely encounter the following error:

No Deserializer found to deserialize a ':Result' using encoding style 'yyy'

This classic interoperability problem is caused by the absence of the xsi:type attributes in the accessors. (link, but not a very helpful one)

Sounds greek to me too, but it's really as simple as Google and Excel speaking two different flavours of the SOAP "language". What I don't get about all this is that it all worked nicely for two full days and then suddently everyone gets this error.

To my understanding sofar, the essence is that Google, or Excel, or both, don't quite agree on what kind of variable the variable "key" is, as one of them don't define it as the other party expects. So, they toss the variable around between them accusing each other of swearing, until they finally give up.

Oh, and yes, it's probably Bill Gates' fault anyway, but the dang Googlites did understand the lingo anyway and responded completely naturally at first, as if nothing was wrong at all. In fact - as far as my understanding goes - they must have told Excel wat kind of variable the key was at first (otherwise it wouldn't have worked), and then they decided to refrain from doing it any more.

Anyway, when I get a round tuit I'll make a new version without this problem. Ironically it will be far easier just scraping the Google results than fixing the Microsoft/Google implementation of SOAP.

Here's one Microsoft Knowledge Base Workaround that you - unfortunately - will not be able to use as that requires that you edit the code in the Excel sheet, which I have not made public (I should have, I know, but alas I didn't do it).

Note this quote. Nice one, eh?

STATUS
This behavior is by design.

I do see those messages in Microsoft Knowledge Base articles sometimes, as I tend to navigate very close to the borders of what MS Office was intended for (The Internet was only discovered a few years ago in Redmond). Usually you can just use another tool, but Google insists on SOAP so the only alernative seems to be scraping. Well, I'll see if I can avoid it somehow, of course, even if scraping would be a ten minute job in stead of a ten hour search for a possible fix.

I'm terribly sorry about this and I really feel that I have to make a new version, as this does nothing good for my reputation.

So, I've decided that I won't blame Google and I won't blame MS. In stead I'll just make a new version as soon as I get the time for it. I can't say when, it may be a month or so as I'm extremely busy at the moment, but I'll announce it at an appropriate place of course (and/or tell Nick)

Got a new version working

Btw. this thread is really about the sheet from AutomateExcel.com. My sheet has another thread here.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.