Google Script to check sheet column values

Question

I have a google sheet with a formula in each cell of column B that returns "Y" or "N" using an IF formula based on a certain condition.

What I would like to do is create a google script that runs hourly (via installable trigger) and searches column B for all rows with "Y" in the cells. I then want it to take those rows and pull the values from column C, then use those values to push through another function using the value from C for that row and return the result in column D for that row and then repeat for every row where "Y" was found in column B. Based on searches in Stack, I found this code which will search the column. But I cant figure out how to use the data to pull the value from the adjacent column C to push through my other function and then return the result back in Column D. Also when running a simple test on this code, it looks like its only running for row 2 and not searching any other row in the sheet.

    function onSearch()
{
    var searchString = "Y";
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy of DataFinal"); 
    var column =2; //column Index   
    var columnValues = sheet.getRange(2, column, sheet.getLastRow()).getValues(); //1st is header row
    var searchResult = columnValues.findIndex(searchString); //Row Index - 2

    if(searchResult != -1)
    {
        //searchResult + 2 is row index.
        SpreadsheetApp.getActiveSpreadsheet().setActiveRange(sheet.getRange(searchResult + 2, 3)).setValue("found here");
    }

This is the code that I want to include under the search script to then push the values from the found rows (with "Y") through and return the result from another function (shown below) back into column D.

    var timedis = gettime(value) \\ this is where i would take the value from column C push it through my other function 
      o.setValue(timedis). \\ the result of gettime (timedis) is what i woudld like to put in column D in the same row and then repeat for all rows where "Y" was found in B. 
}

    function gettime(f) {
      \\my other function script would go here. No help needed on this part. 

    return timedis
    }

jdaz jdaz · Accepted Answer · 2020-06-02T18:17:07

You can use a regular for loop to go through the whole column and do what you need:

function onSearch() {
  var searchString = "Y";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy of DataFinal"); 
  var column = 2; //column Index   
  // start getRange at 2 to skip header row
  var columnValues = sheet.getRange(2, column, sheet.getLastRow()).getValues().flat(); 

  for (let valuesIdx = 0; valuesIdx < columnValues.length; valuesIdx++) {
    if (columnValues[valuesIdx] === searchString) {
      // row number is valuesIdx + 2 because of skipping header row 
      // and because rows in getRange are 1-indexed
      nextColVal = sheet.getRange(valuesIdx + 2, column + 1).getValue();
      let timedis = gettime(nextColVal);
      sheet.getRange(valuesIdx + 2, column + 2).setValue(timedis);
    }
  }
}

Google Script to check sheet column values

1 Answers