CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-appsgoogle-sheets-api
tht13
tht13

Reputation: 47

Google script FIlter issue

I am programming a help desk system using google script, forms and spreadsheet. To filter the queries the submissions are placed into different sheets depending on category, this is done through the FILTER function. however every time a new submission is made the filter function does not update, (it uses the CONTINUE function to cover the other cells) instead the cell with the FILTER function must be selected and crtl+shift+E must be entered

is there a way around this? I have tried two methods the first was looking to have a function to enter the shortcut, but is this possible?

the second is auto entering the continue function everytime a new submission is made, I have this working however google sheets does not recognise the named range, (the continue function has the set up CONTINUE(original cell, rows away, columns away) its the original cell that it does not identify, instead I must manually select the cell and re-write the exact same cell reference.

Thank you for your help, if you need to see my code please ask :)

This is the code for the second option where I try to enter the function manually to the cells.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var numEntry = ss.getSheetByName('Home').getRange("B8").getValue() + 2;
var cat = ss.getSheetByName('Software problem').getRange(numEntry, 4, 1, 9);
cat.getCell(1, 1).setValue('=CONTINUE(D2, '+(numEntry-1)+', 1)');

Upvotes: 0

Views: 2743

Answers (1)

Mogsdad
Mogsdad

Reputation: 45750

Your option 1: Have a script enter keystrokes automatically? Not supported in apps-script.

Your Option 2: It shouldn't be necessary to programmatically insert CONTINUE, as the required CONTINUEs for your FILTER should be automatic, when rows in your filter range match the expressed criteria. Something else is wrong, so don't get caught up with this red herring.

You mention "google sheets does not recognise the named range" - I'd like to know what you mean by that, because I suspect this is where your solution will be. You can use named ranges within FILTER statements. You can also use open-ended ranges, like FormInput!A1:X or FormInput!E1:E.

If you're trying to manipulate named ranges using scripts, then you may have run into a known issue, "removeNamedRange() only removes named ranges that were created via Apps Script". (To get around that, manually delete the named range, then create it only from script.)

Here's a function I use to create a named range for all data on a sheet. You could adapt this to your situation. (I use this with QUERY functions instead of FILTER, you might want to consider that as an alternative.)

function setNamedRangeFromSheet(sheetName) {
  // Cannot remove a named range that was added via UI - http://code.google.com/p/google-apps-script-issues/issues/detail?id=1041
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  try { ss.removeNamedRange(sheetName) } catch (error) {};
  var sheet = ss.getSheetByName(sheetName);
  var range = sheet.getDataRange();
  ss.setNamedRange(sheetName,range);
}

Using FILTER, you need to match the length of your sourceArray (which can be a named range) and any criteria arrays you use. To programmatically create a named range for a single-column criteria within your sourceArray, and of the same length, use getNumRows() on the sourceArray range.

Now, within your submission handling function, triggered on form submit, you'd have something like this. (I assume your trouble reports are coming into a single sheet, "FormInput" - adjust as necessary.)

...
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  try { ss.removeNamedRange("FormInput") } catch (error) {};
  var sheet = ss.getSheetByName("FormInput");
  var inputRange = sheet.getDataRange();
  ss.setNamedRange("FormInput",inputRange);

  try { ss.removeNamedRange("Criteria") } catch (error) {};
  var criteriaCol = 4; // Another guess, that Column E contains our criteria
  var criteriaRange = sheet.getRange(0,criteriaCol,inputRange.getNumRows(),1);
  ss.setNamedRange("Criteria",criteriaRange);
...

And with that in place, the content of A1 on your "Software problem" sheet just needs to contain the following. (Assuming that you're looking for "Bug"s.):

=FILTER(FormInput,Criteria="Bug")

I mentioned open-ended ranges earlier. If you aren't doing enough manipulation of data to justify named ranges, you could set up your filter like this, and not have to change it as new input came in:

=FILTER(FormInput!A1:X,FormInput!E1:E="Bug")

Upvotes: 1

Related Questions