How to fix excel VBA to extract google cse JSON API results.

[satish78]

Hi Friends,

I have constructed list of Google CSE JSON API urls with keywords to extract Linkedin profiles data into excel.
I have VBA script which can extract Google CSE ATOM results. But, unfortunately Google has change ATOM results to JSON API results.
So, the script is not useful anymore.


I need title, url and snippet into ColumnA, ColumnB and ColumnC in sheet2.
Here is sample Macro enabled file with urls in sheet1 and sheet3 additional urls to test run on them.

https://spaces.hightail.com/space/83i4MjL8p4

 

[John_w]

Try the JSON parser at https://github.com/VBA-tools/VBA-JSON. Copy JsonConverter.bas (except the Attribute line at the top) to a new module and name the module JsonConverter.

 

[satish78]

Try the JSON parser at https://github.com/VBA-tools/VBA-JSON. Copy JsonConverter.bas (except the Attribute line at the top) to a new module and name the module JsonConverter.

Is there any alternative one apart from JSON Parser. Because, queries with "", : etc does not work and does not skip urls which does have data to extract.

Here is sample file in which each search term or terms must be encoded to get url in ColumnB. https://spaces.hightail.com/space/83i4MjL8p4
and terms encoded has ", : etc cannot get results. Some code errors.
Please take a look.

 

[satish78]

can anyone help?

 

[John_w]

There are several other JSON parsers.

I've looked at your search URLs. These include the search query parameter q, for example:

"....&key=xxxxx&q= " & ENCODEURL(A1)
where A1 contains "moreerson-role:developer gmail.com texas"

There should be no space after the 'q='. Also, where did you get that query syntax from? Can you post a link to the API documentation showing this query syntax?

 

[satish78]

There are several other JSON parsers.

I've looked at your search URLs. These include the search query parameter q, for example:

"....&key=xxxxx&q= " & ENCODEURL(A1)
where A1 contains "moreerson-role:developer gmail.com texas"

There should be no space after the 'q='. Also, where did you get that query syntax from? Can you post a link to the API documentation showing this query syntax?

these are from "google structured data testing tool" which provides "data sets"(https://developers.google.com/search/docs/data-types/dataset) where we can search any google custom search engine with these queries like other examples
more:hcard-title:developer which gives results whose jobtitle includes developer.
more:metatags-og_title:dallas which gives results whose is located in dallas area.

 

[John_w]

Isn't that just extra markup for web site owners to add to their web pages, which Google will later use in its search results?

Where is the custom search API documentation showing examples of this search query syntax?

 

[satish78]

Google does not elaborate custom search api with examples. Here is the structured data markup tool which gives query syntax what every website uses.
We need to enter url and test from our end. https://search.google.com/structure...=devsite&utm_medium=jsonld&utm_source=dataset

 

[satish78]

Isn't that just extra markup for web site owners to add to their web pages, which Google will later use in its search results?

Where is the custom search API documentation showing examples of this search query syntax?

Did you get it, How the query syntax works with more:Person-title etc.
Could you please update the code? If you have got that issue resolved and also for no data found urls to skip.

 

[John_w]

Did you get it, How the query syntax works with more:Person-title etc.
Could you please update the code? If you have got that issue resolved and also for no data found urls to skip.

No, I haven't found any documentation showing whether 'your' query syntax is accepted by the Custom Search API. I suggest you try your queries using the API explorer at https://developers.google.com/custom-search/v1/cse/list. AFAIK the q parameter only accepts a simple search term, without any of the fancy parameters you are trying.