-1

I am creating a table in Google Sheets that will allow me to aggregate various metrics that are relevant for a web-based company. The website has about 3000 pages, most of which are 1000+ word blogs. So far, I am able to pull Analytics data just fine into Google Sheets and organize pages by KPIs like pageviews and pages per session.

However, a critical area that I need to import for the client are WP tags. The function "ImportXML" seemed simple enough, but fails under the stress of over 3,000 pages and is throttled to the point of being unusable. Because this solution will be implemented by my company in the future for other clients, it is not viable to divy up the data between 100 and 500 sheets merely to avoid the ImportXML throttling, as this would have to be done across potentially hundreds of websites and create unwieldy chains of imported data. For clarification from my last post, this is a website for which we have backend access, and all this information is available as WordPress fields.

The only other solution I have found thus far is to use RegexMatch. This had success with the page title as it has a relatively simple open title tag and close title tag to work with. The far more complicated array of WordPress tags (each a meta property with its own content field) does not play nice with RegexMatch here and returns errors no matter the structure.

A Google Apps Script or a creative use of functions is preferred, though I am open to any suggestions. I appreciate the help.

3
  • 1
    Welcome. There are many ways (tools, languages, methods) to scrape metadata from a website; some depend on the level of access to the website. Would you please explain your assumptions, the other tools and/or other methodologies that you have considered. Do you have access to the website (is a client web-site?) or are you scaping from an unrelated site? Google sheets is not the only game in town even if your goal is to have the meta data residing in a Google Sheet. – Tedinoz Mar 24 at 0:53
  • Congratulations on finding your own solution. I suggest that you post it an answer to your own question so that the question will be tagged as having been answered and others may benefit from your work. – Tedinoz Mar 25 at 1:54
  • Since you have access to the website backend, an alternative approach (which I offer as a suggestion rather than a fully blown answer) would be to use Google Sheets as the repository of the metadata but to source the metadata by accessing the WordPress database remotely by using the JBDC App Script tool and appropriate sql queries. I can recall have done this myself in the past and there is plenty of help on these sites and elsewhere on the net. – Tedinoz Mar 25 at 2:09
0

I solved this on my own. Using RegexMatch to parse the HTML as noted here gives the ability to match a regular expression, though not globally. I imported the entirety of the "single-tags" p class and then filtered out the HTML. Then, I split the tags by commas, finally adding an IFERROR formula. See below.

=IFERROR(SPLIT(REGEXREPLACE(importRegex($A7,"(<p class=.single-tags.>(.*)</p>)"),"<[^>]*>",""),","),"(none)")

Now you can put this into a pivot table and organize by tags, looking at key metrics by your WP tags. No thanks to anyone here, had to develop this on my own.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.