google search from google sheets

[pannells]

Hi All
I have been playing with xmlimport in google sheets to extract H3 urls.
I am using search site parameter with a keyword.
Steps I am trying to put together are:
search google
using site:www.mywebsite.com
with keyword in that site - "my keyword"
return to sheet with top 5 H3 list
-------------------------------------------------------------
What I have done so far that is not working is:
see 2nd image attached for formula and workings.
I have added text strings together
google search with site: function site: - Google Search
+ website to search on freespinsgratis.com
+ keyword pokie+place
Full formula looks like this: site:freespinsgratis.com pokie place - Google Search

Then I want to only grab the top 5 urls in returned search in a list.
PROBLEM: formula does not work
when working - how do I get a results into a list format
Any advise would be great or possible another way of doing this as I am not to educated in this topic

 

[Sufiyan97]

Try

Book1
	A	B	C	D
3	freespinsgratis.com	Pokie Place		https://www.google.com/search?q=site%3Afreespinsgratis.com+Pokie+Place
4	casinogy.com	all online casinos		https://www.google.com/search?q=site%3Acasinogy.com+all+online+casinos
5	freespingames.com			https://www.google.com/search?q=site%3Afreespingames.com+
6	freespinsbet.com			https://www.google.com/search?q=site%3Afreespinsbet.com+
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=HYPERLINK(CONCATENATE("https://www.google.com/search?q=site%3A",A3,"+",SUBSTITUTE(B3," ","+")))

Edit:

***IGNOR*** Actually at first glance I did not understand what you wanted, but now I understand but I don't know about IMPORTXML formula..

 

[pannells]

Any guidance with this would be great, been hammering at this for a day or two now and what I thought would be a simple thing has turned into a rather challenging process with little tweaks done resulting in nothing positive.
Read so much and all looks simple until I actually do this myself.

So Further to my previous post, I have tried a simpler way to to do this but still getting a "could not fetch url" error
=ImportXML("freespinsgratis.com - Google Search pokie place&num=5", "//h3")

Broken down thinking.
> search google serps
> search with site name (freespinsgratis.com)
> search on that site - keyword "pokie place")
> import h3 tags - top 5 SERPS results into sheet

Ideally I want to replace typing in the site and keyword with a cell holding this text string.
Something like this =ImportXML("CELL A5" &num=5", "//h3") where by CELL 5A would hold text string of site and key word

Ideally I would like to use the google "site:" function to get results from that site specifically.
Something like this =ImportXML("site"CELL A5" &num=5", "//h3")

Even better have a way to search that will ONLY result in serps that relate to url searched and must have keyword.
As I am getting SERPS that have either pokie or place in them as well which is not what I want

 

[pannells]

Try

Book1
	A	B	C	D
3	freespinsgratis.com	Pokie Place		https://www.google.com/search?q=site%3Afreespinsgratis.com+Pokie+Place
4	casinogy.com	all online casinos		https://www.google.com/search?q=site%3Acasinogy.com+all+online+casinos
5	freespingames.com			https://www.google.com/search?q=site%3Afreespingames.com+
6	freespinsbet.com			https://www.google.com/search?q=site%3Afreespinsbet.com+
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=HYPERLINK(CONCATENATE("https://www.google.com/search?q=site%3A",A3,"+",SUBSTITUTE(B3," ","+")))

Edit:

***IGNOR*** Actually at first glance I did not understand what you wanted, but now I understand but I don't know about IMPORTXML formula..

Thanks

Try

Book1
	A	B	C	D
3	freespinsgratis.com	Pokie Place		https://www.google.com/search?q=site%3Afreespinsgratis.com+Pokie+Place
4	casinogy.com	all online casinos		https://www.google.com/search?q=site%3Acasinogy.com+all+online+casinos
5	freespingames.com			https://www.google.com/search?q=site%3Afreespingames.com+
6	freespinsbet.com			https://www.google.com/search?q=site%3Afreespinsbet.com+
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=HYPERLINK(CONCATENATE("https://www.google.com/search?q=site%3A",A3,"+",SUBSTITUTE(B3," ","+")))

Edit:

***IGNOR*** Actually at first glance I did not understand what you wanted, but now I understand but I don't know about IMPORTXML formula..

Thanks Durfani for your response.
Not sure what you gave me and how that helps me in the bigger picture of what I am trying to acheive.
Possibly the addition thread I added will clear my intentions and end goal is.