Google-sheets – How to reference the result of a IMPORT function as a defined name in a single cell

google sheetsgoogle-sheets-arraysimportxml

Note: The motivation for my question is just formula readability. My sheet is working fine.

Let's pretend I have an IMPORTXML function that's currently returning 2 columns and a varying number of rows depending on the url used. So the expression is IMPORTXML(MyUrl, MyXPath).

So the way I'm using that expression right now is INDEX(IMPORTXML(MyUrl, MyXPath),1,4) and this is repeated many times with just different row values. This is working fine, and in order for this to work it's not necessary for there to be a range on the sheet showing the results.

I would rather not repeat that expression IMPORTXML(MyUrl, MyXPath) many times over and over in my formulas, but give that expression a name and just use that name instead, in order to make my formulas more readable. So I want to say INDEX(MyData,1,4).

Let's say MyData is in cell B10. What formula needs to go into B10?

Constraints (and yes these are integral to the question):

  1. MyData is just in cell B10, not multiple cells.
  2. IMPORTXML(MyUrl, MyXPath) is not expanded onto the surface of the sheet.
  3. I can reference MyData just as MyData, not as SPLIT(MyData) or something.
  4. I can't just stick IMPORTXML(MyUrl, MyXPath) somewhere and highlight the data and define a range name. Because the size of it will vary as MyUrl changes.

Best Answer

Google Sheets doesn't include a way to assign names to expressions (some people call them formulas) such INDEX(IMPORTXML(MyUrl, MyXPath),1,4)

You could use a named range, but that implies to add the expression in someplace on your spreadsheet and display the result, but you could hide that sheet.

Another alternative is to create a custom function but as Google Apps Script hasn't built-in formula evaluation you should find a way to do that or replace the referred expression for something that do the same but using JavaScript.

Related

Related Topic