Retrieving Selective rows in Google Sheet based on conditions

Question

In Googlesheet I wanted to select certain rows based on the date. I am aware of the getRange() function but not aware as to how to add condition to this function. For example if I have 10 rows in the spreadsheet out of which:

Two rows are having a date column with date of 01/01/2019.
Three rows are having a date column with date of 01/07/2019.
Remaining five rows are having a date column with date of 01/20/2019.

I am try to get a way to write google sheet script to pull all rows having date column between 12/31/2018 and 01/09/2019 and I should get five rows in this particular case.

I have written the below code to create a new sheet but didnt know how to select the range with condition :

  var spreadsheet = SpreadsheetApp.getActive();
  var sheets = spreadsheet.getSheets();
  var mainSheet = sheets[0];  
  var fileName = mainSheet.getRange("B2:C2").getDisplayValue();
  var date = Utilities.formatDate(new Date(), "GMT+5", "yyyy-MM-dd")
  var newFileName = "WS_"+date;
  var dataSheetFileList = DriveApp.getFilesByName(fileName);
  var dataSheetFile = dataSheetFileList.next();
  dataSheetFile.makeCopy(newFileName)
  var newFileUrl = DriveApp.getFilesByName(newFileName).next().getUrl();
  
  var newFile = DriveApp.getFilesByName(newFileName);
  newFile.next().setSharing(DriveApp.Access.DOMAIN_WITH_LINK, DriveApp.Permission.EDIT);
  
  var dataSheetFileList = DriveApp.getFilesByName(newFileName); 
  if(dataSheetFileList.hasNext()){
    var dataSheetFile = dataSheetFileList.next();
    var dataSheet = SpreadsheetApp.open(dataSheetFile);
    var dataSheetList = dataSheet.getSheets();
    var dataSheetCcb = dataSheetList[0];
    **<Code to select the rows based on date>**
  }

Sample Input Google Sheet Data

I am intending to automaticlally create a Summary Google Sheet(screenshot below) using the above Sample Google Sheet :

Your message is a gross violation of the rules of the community. To want does not mean to get stackoverflow.com/help/on-topic Show your efforts - The Community will be show its abilities. — contributorpw
@oshliaer The code I have written till now will not be of any use to the question I asked so didnt provide any code.Sorry for violating the rule.My bad.I am adding the code I have written till now. — Sree
On Google Sheets the "select" term has a special meaning. Using the Google Sheets UI the user selects a row by clicking on the row header, then the row is highligthed. Is that what you mean, do you want to highlight the rows that meet the condition to get only the values of the rows from the data range? — Rubén
@Rubén .Sorry for wrong usage of words again.I didnt mean the meaning you mentioned in your comment.My intention was to automatically prepare new summary google sheet from the details google sheet.For example if we take the simple sample data that I had attached, it had list of students on different exam date.From that data, I was trying to create a summary of count of students having exam on different dates.I will update the question with my intended output data. — Sree

contributorpw contributorpw · Accepted Answer · 2019-05-12T12:20:05

There is my implementation

/* exported onOpen, userActionSelectByCondition, rowSelector_, userActionSelectByDateCondition */

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Advansed selector')
    .addItem('Select between 40 and 1000', 'userActionSelectByCondition')
    .addItem(
      'Select between 01/02/2019 and 01/04/2019',
      'userActionSelectByDateCondition'
    )
    .addToUi();
}

/**
 * Select rows of the active sheet by condition
 */
function userActionSelectByCondition() {
  var /** @type {condition} */ condition = function(row) {
      return row[0] > 40 && row[0] < 1000;
    };

  var sheet = SpreadsheetApp.getActiveSheet();

  rowSelector_(sheet, condition);
}

/**
 * Select rows of the active sheet by condition
 */
function userActionSelectByDateCondition() {
  var date1 = new Date(2019, 0, 2, 0, 0, 0, 0).getTime();
  var date2 = new Date(2019, 0, 4, 0, 0, 0, 0).getTime();
  var /** @type {condition} */ condition = function(row) {
      return (
        row[2] &&
        row[2].getTime &&
        row[2].getTime() > date1 &&
        row[2].getTime() < date2
      );
    };

  var sheet = SpreadsheetApp.getActiveSheet();

  rowSelector_(sheet, condition);
}

/**
 *
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
 * @param {condition} callback
 * @returns {GoogleAppsScript.Spreadsheet.RangeList}
 */
function rowSelector_(sheet, callback) {
  return sheet
    .getRangeList(
      sheet
        .getDataRange()
        .getValues()
        .reduce(function(p, c, i) {
          if (callback(c)) p.push(i + 1 + ':' + (i + 1));
          return p;
        }, [])
    )
    .activate();
}

/**
 * Callback for condition
 *
 * @callback condition
 * @param {Array<object>} row A row of the sheet
 * @returns {boolean}
 */

Example https://docs.google.com/spreadsheets/d/1i7h3CpWa6SEFK8uegNIcKJcpeg1n1-FdyjUwGWicm_M/edit?usp=sharing and video https://www.facebook.com/oshliaer/videos/2196890940397483/

If you really need to select rows you can use getRangeList().activate().

Otherwise if you need to get data by condition you can use getDataRange().getValues().reduce() for work with values.

Retrieving Selective rows in Google Sheet based on conditions

1 Answers