CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
james_dean
james_dean

Reputation: 1517

Sheets, Filter Ranges and Google Scripts

I have a spreadsheet the collects my form responses from Google Form.

Attached to this sheet is a script, that pulls each new row out, does some calculations and puts the results into another sheet. Rather than copying the whole sheet every time, I instead pull the delta of the two sheets with a piece of code like this:

/*
*    Any new entries within the Form Responses are added to the respective tab
*    by comparing the sizes of Form Responses and tab. 
*    
*    The colParser argument defines the function that will extract the necessary columns.
*/
function updateTab(tab, responses, colParser) {

  var existingRows = tab.getDataRange().getNumRows();

  for (var i = existingRows; i <= responses.length - 1; i++) {
    tab.appendRow(colParser(responses[i]));
  }
}

The problem is that I've also got filters applied to the receiving sheets and the filter range is not updated whenever I insert any new data.

Is there a way I can get around this? Can I programmatically update the filter range as part of the update function above?

Upvotes: 0

Views: 844

Answers (2)

MatthewJTuttle
MatthewJTuttle

Reputation: 31

Modify your formula so that it doesn't reference the end of the range. This range will automatically add anything from added rows.

Wrong:

 =FILTER(A1:A4, B1:B4 > 0)

Right:

 =FILTER(A1:A, B1:B > 0)

Upvotes: 0

Zig Mandel
Zig Mandel

Reputation: 19854

Dont put the filter in the receiving sheet. Instead make another sheet (tab) and use the query or filter function as in =query(data!a1:F; "select * where ....")

Upvotes: 0

Related Questions