Sheets

The Goal In the following short article, we want to import data from an existing sitemap XML file into a new Google Sheet document. The sheet must pull the sitemap via HTTP protocol and extract all the URLs from the sitemap and insert them. into the sheet. Implementation Now let’s implement it .. it only takes 1 minute …​ For demonstration purpose, I’m going to use the sitemap from my old blog, to be found at https://www.hascode.com/sitemap.xml. ...

Importing XML Data in Google Sheets Use the XMLIMPORT function The syntax is XMLIMPORT("url", "xpath") e.g. for importing an XML sitemap =IMPORTXML("https://www.hascode.com/sitemap.xml", "//\*[local-name()='url']/*[local-name()='loc']") Select the preceding cell in Google Sheets =INDIRECT(ADDRESS(ROW()-1,COLUMN()) Create a Todo List Counter in Google Sheets Each empty cell is a todo, each filled cell is done. That allows us the following output, that counts all preceding cells of each type: ="Done: "&(COUNTA(B3:INDIRECT(ADDRESS(ROW()-1,COLUMN())))&" Todo: " &COUNTIF(B3:INDIRECT(ADDRESS(ROW()-1,COLUMN())),"")) This outputs a label like "Done: 35 Todo: 142" ...