• lyndsey369

    Thank you!!! I’m doing a site audit right now and this is going to make my work so much easier!!

  • Pandiyarajan

    Thanks for your wonderful post, Can you tell me is there any possibility of checking the Google analytics and other datas by searching through the document.

    Please clarify

  • roxanne bridger

    Why do I keep getting an ERROR message when I enter =COUNTA(importXML(A1,”//a”)) & ” links”?

  • http://www.erieinsurance.com Michael Smith

    Is there any way to do this in Excel? Google Docs is, sadly, blocked for me.

  • http://GodwinPlumbing.com Juliette

    I get en error when I enter =importXML(A1, “(//title|//TITLE)”)

  • http://www.whatshakin.com Loganiii

    THANK YOU THANK YOU THANK YOU!!!

    will use this immediately.

    P.S. I watch the Make movies and KipKay movies with my kids too.

  • http://www.toddnemet.com Todd Nemet

    Lyndsey and Logan, you are very welcome. I’m glad that you found it useful. I will try to think up similar projects for future columns.

  • http://www.toddnemet.com Todd Nemet

    Pandiyarajan, that is a very interesting question. My initial thought is that this isn’t possible because the Google Analytics API requires sending authentication along with the request. Google Docs doesn’t allow that level of control in their requests.

    One possible hack to try is setting up automatic emailing of reports from Google Analytics to Google Docs. If that worked, then the data would automatically be in Google Docs, where you could pull it from another sheet using importRange().

    It wouldn’t be completely automatic, but it would be interesting to play around with.

  • http://www.toddnemet.com Todd Nemet

    roxanne bridger, it’s hard to tell why you are getting the error without seeing the spreadsheet. (If you send me a link to @nemet, I’ll look at it.) My guess is that your page has links with an uppercase A, as in and your query is looking for lowercase a. This is causing no results to come back, which is causing COUNTA to return an error.

  • http://www.toddnemet.com Todd Nemet

    Michael, it is definitely possible to do this in Excel. I originally considered writing the article for Excel, but I decided it would be too hard to share — and because I’m too lazy to test it across different platforms, versions, etc… :-)

    Microsoft has a note on how to import XML data at this link and there appears to be Xpath support, though I’m not sure what version is supported.

  • http://www.toddnemet.com Todd Nemet

    Juliette, can you send me (to @nemet if you are on Twitter or in a comment here) a link to your spreadsheet? The syntax looks fine to me, assuming that the URL is in A1.

  • http://www.toddnemet.com Todd Nemet

    Loganiii, KipKay is awesome. Thanks for the tip. Have you made any of the projects? We haven’t had time to make any, though we did round up some parts for the robot cockroach project.

  • http://www.toddnemet.com Todd Nemet

    Update: On the second spreadsheet, I removed the formula that checks to see if the URL is indexed by Google because it was triggering a CAPTCHA, which made it ineffective.

    That’s probably due to a bunch of requests from people checking out this spreadsheet. I have used similar techniques on other spreadsheets without hitting the CAPTCHA, which is why I believe it is related to the number of requests.

    Thanks to the great Julio Fernandez (@SocialJulio) for bringing this to my attention.

  • http://www.bruce-townsend.co.uk Bruce Townsend

    Great post, but unfortunately the formulae don’t work as published because they contain smart quotes. If you paste them into the spreadsheet, then replace the smart quotes manually with plain quotes, they work fine.

    Just one question – for the page I’m examining, the links formulae count 37 links but list 53. The formula =importXML(A1,”(//a/text()|//a/img/@alt)”) seems to contain a reference to image alt attributes as well as link anchor text. Is that the reason? Is the formula correct?

  • http://www.toddnemet.com Todd Nemet

    Bruce, thanks for your comment. I was typing them in rather that using copy and paste. The query (//a/text()|//a/img/@alt) is looking for both anchor text, //a/text(), and alt attributes in img tags contained in hyperlinks, //a/img/@alt. The discrepancy may be due to case sensitivity — looking at capital A vs. lowercase a — or maybe there are other tags within the A like a div or span that aren’t being picked up. I’d have to try your page to be certain.

  • http://www.marketingdiy.com M.D.

    This looks really useful, but it doesn’t seem to work anymore, formulas are missing?

    Not able to create a copy, that option is not give in Google docs when viewing these spreadsheets, so downloaded as Ecel and re-uploaded to my own Google docs — still not working. :-(

  • http://alexjuel.com Alex Juel

    Awesome post Todd.

    Also, for anyone getting errors, make sure you are converting the text to ascii if you’re copying the code straight from the page. Or at least retype the quotation marks. That’s what was happening to me.

  • http://www.perfectmarket.com GeekGirl

    Todd, seriously? You’re awesome.

  • aliseselezneva

    Todd, thank you for tutorial. I am in the process of creating my own SEO tool in Google docs and your article was very handy. I am also looking for other data, such as backlinks to webpage and page rank, and domain age. Do you happen to know them?