Category: Spreadsheets

  • How to run a website metadata audit in Google Sheets

    Google Sheets has a formula that lets you scrape values from a website: =importxml(). Get a list of your web pages (you can do this by importing your xml sitemap into Google Sheets) and then use the =importxml() formula to scrape values for things like metadata title and description, or on-page text like your Heading 1. Importxml(URL,Xpath)…

  • 3 XML Sitemap tricks for Google Sheets

    In the last post I described how I eventually managed to import a Google XML sitemap file into Google sheets. In short, this: =importxml(“http://example.com/sitemap.xml”,”//*[local-name() = ‘url’]”) I’ve been building on that recently and thought I’d share a few ways I’ve been using it. Filtering the sitemap by path Filtering the sitemap by date Counting pages

  • Import a Google Sitemap XML file into Google Sheets

    I wanted to get my sitemap data into Google Sheets for a few content audit and statistics dashboards. The IMPORTXML() function in Google Sheets sounded pretty self explanatory and the XPATH syntax seemed almost graspable. =IMPORTXML(“https://www.example.com/sitemap.xml”, “//url/loc”) But it just wouldn’t sodding work.