Google Sheets – How to Create a Dynamic Named Range

google sheetsgoogle-apps-script

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.

Best Answer

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.

Related Topic