Office

Importing a Sitemap XML into Google 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. ...

Snippet: Modifying Excel Files, Adding Formulas with Apache POI

Recently I needed to modify some excel files and to add some aggregated formula fields to a sheet and the following snippet did the work for me. Figure 1. Modified Excel File with Formulas Dependencies Just two dependencies needed here: One for Apache POI and one for the Office Open XML Documents API. <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.10.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.10.1</version> </dependency> Sample Excel File Our sample excel file is quite empty just some empty customer information and a chart that is bound to the data grid. ...

Google Docs Snippets

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" ...