CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
user14915635
user14915635

Reputation: 396

Automatically Generating Word Definition from Merriam-Webster Dictionary

I'm trying to modify this formula to automatically provide the definition in Column B when a word is entered in Column A. Currently, the formula looks like this...

=(index(split(concatenate(importxml("https://www.merriam-webster.com/dictionary/"&$A2,"//span[@class='dtText']")),": ",FALSE,TRUE),1,1))

This works, but it doesn't automatically provide the definition when adding new words. I tried modifying the formula using an arrayformula (seen below), but it repeats the definition for the word in A2.

={"Definition";arrayformula(if(A2:A="",,index(split(concatenate(importxml("https://www.merriam-webster.com/dictionary/"&$A2:A,"//span[@class='dtText']")),": ",FALSE,TRUE),1,1)))}

Any ideas on how to fix this? Thanks for your help. Here's the link to the Google Sheet below so you can edit it.

https://docs.google.com/spreadsheets/d/1_OrJwlOHxVXZBKA0GFaiXnRS1x5OCszrR9m3UlMuWRY/edit?usp=sharing

Thanks for your help!

Upvotes: 0

Views: 1848

Answers (1)

SputnikDrunk2
SputnikDrunk2

Reputation: 4048

According to this similar article about Use importXML from column of URLs with arrayformula returning same duplicated result in google sheet:

unfortunately, that won't do because ARRAYFORMULA does not support IMOPRTXML formulas. you could try to look up some scripted workaround for this task or give up on one cell solution

Alternative Solution

Perhaps you can use this sample onEdit(e) simple trigger script below instead via Apps Script that is bound to your Spreadsheet file:

Follow this guide on how to add the script as a bound script in your Spreadsheet file:

Sample Script:

function onEdit(e){
  var sh = e.range.getSheet();
  if(e.range.getColumn() != 1 || e.range.getRow() == 1) return; //ignore if you have not put the word on column A or Col #1 & on row 1
  sh.getRange("B"+e.range.getRow()).setFormula("=(index(split(concatenate(importxml(\"https://www.merriam-webster.com/dictionary/"+e.range.getValue()+"\",\"//span[@class='dtText']\")),\": \",FALSE,TRUE),1,1))");
  if(e.range.getValue() == "") sh.getRange("B"+e.range.getRow()).clear(); //clear column B row if the adjacent word on column A gets deleted
}

Sample Demonstration

  • Input the words crystal & believe on cells A2 & A3

enter image description here

Upvotes: 2

Related Questions