How to create a dynamic named range?

Question

I saw this question and its accepted answer, but I can't wrap my head around how/if it might be used for my situation.

I'm pulling data dynamically from several APIs so the data may be different at any moment, including the actual placement of the data on my sheet. I want to define various cells within the returned data, as named ranges. But without knowing for certain exactly what cell each piece of data will reside in at all times, named ranges don't work well for this. Ideally I could place say a vlookup in a named range, but you can't use formulas at all in named ranges, in Google Sheets.

I don't update the formulas that often, but when I do my calculations break if the named ranges aren't correct anymore. I would have to use vlookup in dozens of formulas on multiple sheets and it just makes the formulas huge and ungainly, when a named range keeps them incredibly tidy and easy to understand. GAS is fine with me as long as I know what to do with it.

Is there any way to get to the same effective result using a named range? I reference these named ranges in many places throughout my workbook, which is why I wanted to use named ranges in the first place... it makes all those other formulas much easier to read & debug.

EDIT: I hope I can provide a bit more of an example here. A hypothetical set of data might be:

ABC   123456
DEF   987654
GHI   246802
ABC   537910

But the next time I pull the data, it could look like this:

XYZ   135791
ABC   123456
LMN   086420
DEF   987654
GHI   246802
ABC   537910
LMN   776655

So now my original 3 values are still there, but they have moved in their rows. A vlookup would make this very easy since it will always adapt to wherever the target value is located, and return the adjacent value. But I need each one of those adjacent values to be its own named range, that will always be correct regardless of where the data moves to.

Also there are often multiples of the key value in the data, and a given formula elsewhere in the sheet needs to pull the first one, or second, or third, etc.

Answer 1

In Google Sheets, named ranges works with "static" ranges, not formulas.

I'm thinking on two approaches, both using Google Apps Script.

One aproach is to use a custom function, the other is to create a script that update the named ranges.

Custom Function

With a custom function we could use Google Apps Script to extend the built-in spreadsheet function library, in this case to use custom function name in a similar way that a named range could be used but it would have the logic to findout where are located the values after the last import.

Example:

The follwing custom function looks for a value on column A, then return the value on the same row from column C.

/** 
 * My Named Range
 *
 * @customfunction
 */
function myNamedRange(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1');
  var colA = s.getRange(1, 1, s.getLastRow(),1);
  var values = colA.getValues();
  var search_key = 'C';
  var index = values.join('').indexOf(search_key);
  var data = s.getDataRange().getValues(); 
  return data[index][2];
}

It could be used in a formula as follows:

=ISTEXT(myNamedRange(A1))

NOTES:

• Some built-in functions like SUMPRODUCT, doesn't accept custom function as arguments.
• Custom functions results are cached and recalculated when the spresheet is opened or when its parameters changes. On the above example it's assumed that the value of A1 will change every time an import occurs.

Script to update named ranges

The Class Spreadsheet has several methods to handle named ranges that could be used to create, update and delete named ranges.

Answer 2

I've come up with a solution which is acceptable to me. I created a secondary table that fetches the data I need to turn into a named range, by using a nested vlookup and indirect, like so:

=vlookup(J22,indirect("F"&row(vlookup(I22,A:G,COLUMN($F$1),false))+1):G91,2,false)

This question was originally attempting to find a more direct solution so I simplified my example data, but the final data is a bit messier. I needed to first locate a string that I knew would never move (which is in column A), then figure out its row number and look one down to set the start of the range for a second vlookup, which then retrieves the actual piece of data I need.

This is in turn made into a named range, and now no matter where my data moves itself vertically as the API returns change, my named ranges don't break. Working like a charm!

Answer 3

Google Sheets simplifies Dynamic Named Ranges by adjusting the "height" of the column according to the countA of the items in the column. All you have to do is use the formula 'Sheets1'!A:A on your named range panel - as opposed to writing the specific range like 'Sheets1'!A1:A10. By putting the entire height of the column as your Named Range, Google sheet will output only those cells that have values inside, empty cells will be left out and in effect, you have created for yourself a dynamic range :). Enjoy!