Issue adding cell data to URL while using =Importxml in Google Sheets

0
votes

I'm trying to set up a translation guide in Google Sheets (Norwegian to English). I want it to pull info on parts of speech for each word from Dictionary.com so I can filter by that category (nouns, adjectives, verbs). However, I can't seem to get the XPath to display proper results. I tried just copying the Xpath from Google dev console via right-click options, but nothing would work. I eventually got what I have now to kinda work but sometimes it gives #REF! and other times adds words to the cell below. In the image, cell E5 has no formula but E4 is adding in that text to the cell. If I delete E5 then E4 turns to #REF!.

I'm not a coder, just trying to make a tool to help learn a new language with Duolingo. Any help would be great.

enter image description here

1
xpathgoogle-sheetsgoogle-sheets-importxml
share a copy of your sheet - player0

1 Answers

1
votes

This is because the formula is getting multiple values and is posting them in the rows below.

For example, for "Name", the xpath query delivers three elements that match the query:

noun
Idioms
noun

To restrict the output to just one result, you could use the array_constrain() function like this:

=array_constrain(importxml("http://www.dictionary.com/browse/"&F2, "//h3[1]/span/span[@class='luna-pos'][1]"),1,1)