Google Sheets: INDIRECT() with a Range

1
votes

This question could be rephrased to, "Using a programmatically generated range in FILTER()" depending on the approach one takes to answer it.

EDIT- It seems that I inadvertently included too much information in my attempts to demonstrate what I've tried so that my question was unclear. The changes I made in this edit should remedy that.

I am currently filtering using the following function:

Code Block 1

=filter('Data Import'!1:10000,'Data Import'!D:D<12)

After importing data, Column D:D can change positions (eg, it could be in column F:F), but always has the header "student.grade".

The question is: How can I reference this variable-position column with a fixed header in a filter function as in the one given in code block 1? In other words, can I replace 'Data Import'!D:D` with valid code that will allow this function to work regardless of the location of the column with header "student.grade?"

What I've tried:

I can use the following code to correctly find the address of the column (whatever it happens to be after data import) as a string:

Code Block 2

=substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")&":"&substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")

The function in code block 2 above returns "D:D" when the header "student.grade" is in cell D1, and "F:F" when "student.grade" is in cell F1. I thought I could simply plug this value into a FILTER() function and be on my merry way, but in order to convert my string to a usable address, I attempted to use an INDIRECT() function on the string produced in code block 2 above.

Code Block 3

=filter('Data Import'!1:3351,'Data Import'!indirect(substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")&":"&substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,""),TRUE)<12)

The formula won't parse correctly.

Simplifying the indirect portion of the same function to test whether or not it will work when given a range produces the same error:

Code Block 4

=filter('Data Import'!1:3351,indirect('Data Import'!&"D:D")<12)

This leads me to believe INDIRECT() doesn't handle ranges, or if it does, I don't know the syntax. This Stack Overflow post seems to suggest this is possible, but I can't work out the details.

This question is NOT an attempt to get others to help me solve my programming dilemma. I can do that with various scripts, giant columns of secondary if statements, and more.

This question is asked for the sake of understanding how to pass a variable range into a filter function (if it's possible).

3
google-apps-scriptgoogle-sheetsspreadsheet
You are launching into examples of code without actually telling us what you are trying to achieve (other than a dynamic filter range), and without showing us what your data looks like. Please edit your question to include more information about the actual scenario your are working with, and access to your spreadsheet (or a version of it) - otherwise your question has no content. – Tedinoz
I clarified my question. Most of the content outlines what I've already tried rather than posing a question. I suggest altering your language to be less confrontational ("your question has no content") lest you provoke defensive responses instead of useful discussion. While your comment was valid (I didn't clarify my question well enough), it was difficult not to argue with you. – Zediiiii
Point taken. Thank you for mentioning it. – Tedinoz
It seems to me that the lack of certainty about the 'student.grades' column is an/THE issue. FWIW, I've written a script that updates a named range that has 'student.grades' in the header. I won't post it as an answer, because it may discourage people from looking at the question and/or posting an answer that that can cope with the moveable header/column, etc. Let me know if you want the script. – Tedinoz
With player0 having adding an excellent formula based answer, I'll add the script as an answer so you can have it for reference. – Tedinoz

3 Answers

3
votes

once again, maybe this is what you want:

=FILTER('Data Import'!1:100000, 
 INDIRECT("'Data Import'!"&
 ADDRESS(1,       MATCH("student.grade", 'Data Import'!1:1, 0), 4)&":"&
 ADDRESS(1000000, MATCH("student.grade", 'Data Import'!1:1, 0), 4)) < 12)

1
votes

I have no idea what you want to achieve but take a look at this:

={'Data Import'!1:1;
 FILTER('Data Import'!1:10000, 'Data Import'!D:D < 12)}

or:

=QUERY(FILTER('Data Import'!1:10000, 'Data Import'!D:D < 12), 
 "select * label Col4 'student.grade'", 0)

1
votes

The OP's existing solution is based on Filter command. The challenge is that the column containing "student.grade" is not fixed, however player0 has provided an excellent formula-based solution.

An alternative might be to make use of a named range. The following code finds "student.grades" in the header (row 1) and re-defines the named range accordingly.

function so54541923() {

  // setup the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Data Import";
  var sheet = ss.getSheetByName(sheetname);

  // define the header row
  var getlastColumn = sheet.getLastColumn();
  var headerRange = sheet.getRange(1, 1, 1, getlastColumn);
  Logger.log("DEBUG: Header range = " + headerRange.getA1Notation()); //DEBUG

  // assign a variable for student Grades
  var grades = "student.grade";

  // get the headers and find the column containing "student grades"
  var headerData = headerRange.getValues();
  var gradesIndex = headerData[0].indexOf(grades);
  // add one to the index number to account for start=zero
  gradesIndex = gradesIndex + 1;
  Logger.log("DEBUG: gradesIndex = " + gradesIndex); //DEBUG

  // convert the column number to a letter 
  // assumes that student.grade will never get past column Z
  var temp, letter = '';
  if (gradesIndex > 0) {
    temp = (gradesIndex - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    gradesIndex = (gradesIndex - temp - 1) / 26;
  }
  Logger.log("DEBUG: the column is " + letter); //DEBUG

  //var newrange = "'" + sheetname + "'!"+letter+":"+letter+";";
  // Logger.log("the new range is "+newrange); 

  // get the named ranges
  var namedRanges = ss.getNamedRanges();
  Logger.log("DEBUG: number of ranges: " + namedRanges.length); //DEBUG

  // if named range is student grades, then update range
  if (namedRanges.length > 0) {
    for (var i = 0; i < namedRanges.length; i++) {
      var thename = namedRanges[i].getName();
      Logger.log("DEBUG: Loop: i: " + i + ", and the named range is " + thename); //DEBUG
      if (thename = "student.grade") {

        // Logger.log("DEBUG: The named range is student.grade");//DEBUG

        // set the new range based on the column found earlier
        var nonstringrange = sheet.getRange("'" + sheetname + "'!" + letter + ":" + letter);
        namedRanges[i].setRange(nonstringrange);
        Logger.log("DEBUG: The new range is " + namedRanges[i].getRange().getA1Notation()); //DEBUG

      } else {
        Logger.log("DEBUG: The named range is NOT grades"); //DEBUG
      }
    }
  }
}