Google Apps Script - Returning data results based on multiple criteria

Question

I am trying to run a script that validates a table of stagnant data against several criteria.
I am fairly new to using scripts, and I spent most of yesterday gaining a base knowledge but I am still struggling.
The first objective I want to achieve is checking dates - I want to check the dates in one column, row by row, and if any of the dates are greater than the corresponding date two columns over I want either the whole row or just the second column value to return when I run the script. I think my main problem is that I still cannot fully grasp how to use the return function. So far I am only able to return the whole data set, not just the results of my if statement.

function dateValidation() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1');
var values = sheet.getDataRange().getValues();
var cell = sheet.getRange(1,21,5000,1) //first date column
var cell2 = sheet.getRange (1,23,5000,1) //second date column
var cell3 = sheet.getRange (1,2,5000,1) //column to return
  for(var i = 1; i < values.length; i++) {
    if(cell.getValues() > cell2.getValues())
    return cell3.getValues(); 
}

I have a feeling I may be going about this all wrong with the if statement. I typically use formulas like extended queries but for this, I need a function to adhere to a button to be able to validate the data on a whim, not have it constantly update in a query. Any help would be greatly appreciated, but I know I still have a lot to learn.

Answer 1

I tried to replicate your code and found some issues.

1. using values.length in your for loop might introduce index out of bounds once the number of rows in your valuesis not the same with the number of rows indicated in the 3rd parameter of getRange(1,21,5000,1).
2. The return of getValues() is 2 dimensional array. You have to indicate index of array you want to check to be able to access the data inside the array.
3. Using return cell3.getValues(); statement inside for loop will just return the whole values of cell3 once the condition is met.

To fix the issues:

1. Use values.length as third parameter of cell,cell2,cell3 getRange().
2. Each Array inside the 2d array represents a rows of data. Since we only need 1 column for cell, cell2, and cell3 we can indicate array[i][0] to access the first element of each array.
3. Instead of using return, create a empty array above the for loop and use push() to store the data that matches the condition.

Example:

Code:

function dateValidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var values = sheet.getDataRange().getValues();
  var cell = sheet.getRange(1,1,values.length,1).getValues(); //first date column
  var cell2 = sheet.getRange (1,2,values.length,1).getValues(); //second date column
  var cell3 = sheet.getRange (1,3,values.length,1).getValues(); //column to return
  var single_col = [];
  var whole_row = [];
  for(var i = 1; i < cell.length; i++) {
    if(cell[i][0] > cell2[i][0])
    {
      single_col.push(cell3[i][0]);
      whole_row.push(values[i]);
    }
  }
  Logger.log(single_col);
  Logger.log(whole_row);
  //choose either of these two below.
  //return single_col;
  //return whole_row;
}

Output:

References:

Javascript Return

getRange()

getValues()

Array.prototype.push()