Home > Software design >  Trying to use XML in Google Sheets to get data from last instance of a class
Trying to use XML in Google Sheets to get data from last instance of a class

Time:12-17

Apologies if this is formatted poorly; I've never had to ask a question here before. Usually Stack Overflow has the answer!

Anyway, I need a spreadsheet that tracks the comments in a threaded discussion. Specifically, I need it to give me the username of the last person who commented on the thread.

Here is the spreadsheet enter image description here

CodePudding user response:

In the HTML I can see that all posters have their username been captured in b-nodes. Therefor I tried the following:

=QUERY(IMPORTXML(B2,"//b"),"limit 1 offset "&COUNTA(IMPORTXML(B2,"//b"))-1)

I was more keen on using =IMPORTXML(B2,"//b[last()]"), however somehow GS keeps returning all b-nodes instead of the last one. Not sure if this is GS-specific (or the XPath can be better written?), but in Excel it would have worked fine.

  • Related