Hi,
I am looking for a way to use Ahrefs API integration and this tool for the following:
I have a list of 20,000 URLs in an Excel sheet. I need a way to call Ahrefs API, check for referring domains and add them in the field next to the URL.
Is there a way to do that with this tool?
Hi and welcome to the forums,
Yes, it is possible using the Ahrefs connector and Formula mode. Do you want one refdomain per URL in your list?
Thanks for your quick reply!
No, I'd like to have it capped at 20 if that's possible. Here is an example:
There will be 20,000 URL's in the URL's column. Eventually I want to filter out specific referring domains and delete them. For example delete "example4.com" and the number in the Number column should update.
EDIT: The ReferringD column would be pulled from the Ahref API.
Thanks for your help!
Sure, it's possible to set the limit to 20. The criteria (comma separated, filtering out specific) can be done with Excel techniques.
Yeah I can figure that out in excel, but how would I pull the referring domains for a list of 20k URLs?
I figured out how I pull the referring domains for one URL, either by copy pasting the URL into the "URL" field, or by selecting a single cell in excel that contains an URL. Selecting a range of cells that contain URLs didn't work.
I still need help with that, how would I do that?
See my example blow and use the same settings:
Then change the "https://seotoolsforexcel.com" part of the formula to A2 (or whatever the first URL is in your list) and expand the formula down.
=Dump(Connector("Ahrefs.RefDomains";"https://seotoolsforexcel.com";"exact";;;"refdomain";"asc";RefDomains";TRUE;20);TRUE)
Throws an error "There is a problem with this formula."
Did you create the formula from clicking Insert using "Formula mode"?
I selected "Formula" on the right, then selected B2 and pasted this in the cell:
=Dump(Connector("Ahrefs.RefDomains";"https://seotoolsforexcel.com";"exact";;;"refdomain";"asc";RefDomains";TRUE;20);TRUE)
Is that what you meant?
You should not paste anything, just click the Insert button after selecting formula. The formula should be generated for you.
Oh okay I understand. I did that now and now the cell appears to empty but when I click on it, it shows the following at the top:
=Dump(Connector("Ahrefs.RefDomains","A2","exact",,"refdomain","asc",20,0,"RefDomains",TRUE),TRUE)
No referring domains tho.
Almost there, just got to remove the quotes around A2 
I didn't put the quotes around A2, I just put A2 in the "URL:" field on the left. However, now I removed the quotes around A2 in the field on the top and it put out the Referring Domains perfectly for A2!
Now all I need is this for the whole A column. Thanks for all the help so far!
Just expand the formula to execute the formula on all URLs in your list
I did it *and it worked! Sorry, I know this is excel support by now and not directly related to the tool. Thanks!
Two more questions:
Is there another way to expand the formula besides dragging it down? Wouldn't want to drag over 20k cells.
Is there documentation for the "Where:" field of the tool? I'd like to paste in a number of domains that shouldn't be returned. For example I'd add "example1.com" to the "Where:" field and if one of the Referring Domains is "example1.com" it will be ignored and not returned.
EDIT: I found this " !substring(url_from,"seotools")" but it throws an error when I paste it into the "Where:" field saying a column with this name couldn't be found.
You can double click on the corner of the formula cell which will expand the formula down to the last row with data in adjacent rows.
Try to add this string to the Where field and insert an updated formula. I suspect that the quotes are the issue here. May need to use double quotes:
!substring(url_from,""seotools"")
Worked perfectly, thanks!
Thanks found a way to work around it.
I now saved the finished excel sheet, opened it up again and it refreshed all the data costing me another 100k rows.... I just want to save the data what do I have to do???
Yeah, that is annoying. I recommend copying the results and pasting it back as values. I usually copy to a notepad and copy it from notepad and paste it back in Excel. This way I get rid of any Excel formatting.
Thanks this worked fine!
I noticed another issue, apparently the tool uses only "https://" and not "http://+https://" when looking up referring domains. It makes sense since the URL's all are "https://", but I'd like to see both, the referring domains for the http and the https version for each URL.
Can I change that somehow?
