Google Script - Filter data?

Question

Good Evening,

I have my data in google sheets with my code listed below. On sheet "Projects" for example I getRange 'B1:F500' thanks to earlier advice. Column F contains 1 or 0 (no nulls) in the cells, how do I only return the columns where column F only = 1? I am looking to apply the same to the other tabs.

function doGet(e) {

if ( e.parameter.func == "static" ) {
  var ss = SpreadsheetApp.getActive();
  var shProj = ss.getSheetByName("Projects").getRange('B1:F500').getValues(); **// where F=1**
  var shHier = ss.getSheetByName("Hierarchy").getRange('B1:F2000').getValues();
  var shFlr = ss.getSheetByName("Floors").getRange('B1:C300').getValues();
  var shRm = ss.getSheetByName("Rooms").getRange('B1:C500').getValues();
  var shCnd = ss.getSheetByName("Condition").getRange('B1:C50').getValues();
  var shSts = ss.getSheetByName("Status").getRange('B1:C50').getValues();
  var shRfg = ss.getSheetByName("Refrigerant").getRange('B1:C300').getValues();
  var shAcs = ss.getSheetByName("AccessE").getRange('B1:C50').getValues();
  
  return ContentService.createTextOutput(JSON.stringify({"Projects":shProj,"Hierarchy":shHier,"Floors":shFlr,"Rooms":shRm,"Condition":shCnd,"Status":shSts,"Refrigerant":shRfg,"AccessE":shAcs,})).setMimeType(ContentService.MimeType.JSON);
}

Many Thanks

The solution is : `shProj.filter(row=>row[4]==1)`. However, it is a good idea to do some research before you ask otherwise your post will be closed as a duplicate. — Marios, Oct 04 '20 at 19:34
Does this answer your question? [How to filter an array of arrays?](https://stackoverflow.com/questions/40849369/how-to-filter-an-array-of-arrays) — Marios, Oct 04 '20 at 19:42
That returns a new array which is the data that you want. `var newArray=shProj.filter(row=>row[4]==1)` — Marios, Oct 04 '20 at 19:43
I am lost and no it doesn't answer my question. I was thinking more on the lines of setColumnFilterCriteria(columnPosition, filterCriteria) — Garth, Oct 04 '20 at 19:45
Did you try it out ? What does this return ? `Logger.log(newArray)` after you execute the line in my comment. — Marios, Oct 04 '20 at 19:47
"That returns a new array which is the data that you want. var newArray=shProj.filter(row=>row[4]==1)" do I place this on the next line below "var shProj = ss.getSheetByName("Projects").getRange('B1:F500').getValues();"? — Garth, Oct 04 '20 at 19:51
var shProj = ss.getSheetByName("Projects").getRange('B1:F500').getValues(); //F includes the status. var newArray = shProj.filter(row=>row[4]==1); — Garth, Oct 04 '20 at 20:08
And I have changed the reference from 4 to 5. B to F is 5 fields — Garth, Oct 04 '20 at 20:17
Thanks Marios, I have learnt something new on the numbering, thanks. Please can you guide me where I need to edit my code. I am not IT. Thanks — Garth, Oct 05 '20 at 06:35

score 1 · Accepted Answer · answered Oct 05 '20 at 08:05

You can use the filter() method to achieve your goal:

function doGet(e) {

if ( e.parameter.func == "static" ) {
  var ss = SpreadsheetApp.getActive();
  var shProj = ss.getSheetByName("Projects").getRange('B1:F500').getValues(); **// where F=1**
  var shProjF1=shProj.filter(row=>row[4]==1); // <- new code
  
  var shHier = ss.getSheetByName("Hierarchy").getRange('B1:F2000').getValues();
  var shFlr = ss.getSheetByName("Floors").getRange('B1:C300').getValues();
  var shRm = ss.getSheetByName("Rooms").getRange('B1:C500').getValues();
  var shCnd = ss.getSheetByName("Condition").getRange('B1:C50').getValues();
  var shSts = ss.getSheetByName("Status").getRange('B1:C50').getValues();
  var shRfg = ss.getSheetByName("Refrigerant").getRange('B1:C300').getValues();
  var shAcs = ss.getSheetByName("AccessE").getRange('B1:C50').getValues();
  
  return ContentService.createTextOutput(JSON.stringify({"Projects":shProjF1,"Hierarchy":shHier,"Floors":shFlr,"Rooms":shRm,"Condition":shCnd,"Status":shSts,"Refrigerant":shRfg,"AccessE":shAcs,})).setMimeType(ContentService.MimeType.JSON); // "Projects":shProjF1
}

Thanks Marios, this has worked perfectly (I missed the part in the "return Content.Service..), Thank you so much. — Garth, Oct 05 '20 at 10:52

Google Script - Filter data?

1 Answers1