CODEWITHSUNDEEP
google-apps-scriptfilter
user3447653
user3447653

Reputation: 4148

Filter a sheet based on specific column values

I am trying to filter a sheet based on the following 3 column values. I am new to Apps Script programming, any suggestions would be appreciated.

Added the image: JobID has to be filtered with variable "jobid" and BOLDate has to be filtered between variables "fromdate" and "todate". Hence, the result would have only the first 6 rows in the table as last row does not fall within the date range.

#3 Filters

var fromdate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form').getRange(3,10).getValue();
var todate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form').getRange(3,11).getValue();
var jobid = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form').getRange(25,5).getValue();

#Read the input sheet and apply the following filters on 3 columns - JOBID is filtered with jobid and
BOLDATE is filtered between fromdate and todate.

var Sheet_Input = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Input');
var Sheet_Input_Data = Sheet_Input.getDataRange().getValues();

for (var i = 0; i < Sheet_Input_Data.length; i++) {
  var JobID = Sheet_Input_Data[i][0];
  var BOLDate = Sheet_Input_Data[i][7];
  
  #Trying to do the filter and get resultant rows

}

enter image description here

Upvotes: 0

Views: 122

Answers (1)

Cooper
Cooper

Reputation: 64032

function findData() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Input');
  const vs=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
  const fm=ss.getSheetByName('Form');
  const fr=fm.getRange(3,10).getValue()
  const frv=new Date(fr).valueOf();
  const to=fm.getRange(3,11).getValue();
  const tov=new Date(to).valueOf();
  const id=fm.getRange(3,12).getValue();
  let row=[];
  vs.forEach((r,i)=>{
    let dtv=new Date(r[7]).valueOf();
    if(dtv>=frv && dtv<=tov && id==r[0]) {
      row.push(r);
    }
  });
  Logger.log(r);
}

Provide me with data that I can copy and paste and I'll test it otherwise you can test it.

Upvotes: 2

Related Questions