Kicking Ass With API Cheat Sheet

by Jaco
5 comments
Original article contributed by: Sean Malseed
Get in touch should you wish to contribute content to RankTank.

Kicking ass with API webinar + cheat sheet

For your own API key, use our exclusive SEMrush API Account Promo for a free trial courtesy of SEMrush

Multi-page change monitor tool

Let’s take a look at the tool in the webinar!

Get this SEO tool for free!

Click the "Free Download" button below...


Instantly receive a link to access this free SEO tool.

FREE DOWNLOAD

Send download link to:

Subscribe to get free SEO tool updates.

  • Bitcoin
Scan to Donate Bitcoin to 19gHVYrSygFuUKXhZ2fDcfmk1WD2t1Yoym

Donate Bitcoin to support RankTank

Scan the QR code or copy the address below into your wallet to send some Bitcoin to keep RankTank free forever!

Get this SEO tool for free!
You can also Make a Copy of this Google Sheet SEO tool, help fix and improve it for others.

Column B’s formula:

1. Initial formula for column B: =importdata(A2)
2. Adding concatenate: =concatenate(importdata(A2))
3. Removing line breaks: =substitute(concatenate(importdata(A2)), char(10), "")
4. Check for the URL: =if(A2="","", substitute(concatenate(importdata(A2)), char(10), ""))

Column D’s formula:

1. Initial formula for column D: =if(B2=C2, "Nope", "Yep!")
2. Add check for baseline data (column C) first: =if(C2="","", if(B2=C2,"Nope","Yep!"))

Bulk keyword position tool using SEMrush

Google Sheet

Column B’s formula:

1. Initial formula for column B: =importdata("http://us.api.semrush.com/?action=report&type=phrase_this&key=YOUR_API_KEY&display_limit=1&export=api&export_columns=Nq&phrase=" & A2)
2. Adding QUERY to remove the extra header line: =query(importdata("http://us.api.semrush.com/?action=report&type=phrase_this&key=YOUR_API_KEY&display_limit=1&export=api&export_columns=Nq&phrase=" & A2),"SELECT Col1 LABEL Col1 ''")
3. Use cell E2 to change country on the fly: =query(importdata("http://" & $E$2 & ".api.semrush.com/?action=report&type=phrase_this&key=YOUR_API_KEY&display_limit=1&export=api&export_columns=Nq&phrase=" & A2),"SELECT Col1 LABEL Col1 ''")

Meta and page title checker

Google Sheet

Column B’s formula (XPath code is in SWEET SWEET MAGENTA!):

1. The whole formula for column B: =substitute(importXML(A2, "//meta[@name='description']/@content"),char(10),"")

Column C’s formula:

1. The whole formula for column C: =importXML(A2, "//title")

Examples of finished tools

Find business address by domain

Give the tool a list of domains, and it tries to figure out the business addresses and phone numbers for each one by looking for the Google+ page and parsing it.

Unlinked brand mentions finder

A tool that opens ranking URL for a phrase, and parses that ranking page to check for a link back to the domain. Yep, the whole tool is on a single sheet!

Leave a Comment

5 comments

Nine lesser-known SEO resources and blogs you might not follow, but should | Search Engine Watch 18 January 2017 - 12:46 pm

[…] I think a lot of SEO professionals don’t realize that they can build incredibly useful tools with Google Sheets for absolutely free, and RankTank can help them do that. […]

George 7 December 2016 - 11:27 am

Hi

Very informative webinar, enjoyed it. Is there a way of getting over the 50000 character cell limit in Google sheets. My website pages HTML are more than 50000 characters, if not the whole page then taking the first 25000 characters as I only need the top part of the page. I have tried many different things I can’t make it work.

Thanks in advance.

George

Pojda 20 October 2015 - 9:02 pm

This is probably old, but I guess it’s worth noting that you can use the index() formula instead of query() on Bulk Keyword Position Tool. I didnt’t try it, but I don’t see why it would work.

Jaco 30 November 2016 - 10:51 am

Thanks Pojda.

Abu Nosor 22 May 2022 - 5:23 am

I dont know using this app but i want working..

Free SEO Tools

RankTank uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More