CODEWITHSUNDEEP
javascriptfor-loopif-statementgoogle-apps-scriptgoogle-sheets
Learning_script
Learning_script

Reputation: 91

Simple for loop, if statement and output message

I think this one is pretty simple, but I am new to this, so I am not sure where to go from here :)

I have a Google Sheet, with some data (pretty large sheet). I want a script that check whether the number in a cell (column I) is larger than the number in the same row, but another column (column D).

Imagine two columns with 5 rows: Column D = (3, 3, 3, 3, 3) and Column I = (2, 2, 7, 2, 2)

SO in this example, I want that the script to tell me that I have problem in "Row 3", because the number in Column I, Row 3 is larger than the number in Column D, Row 3 :)

This is what I have:

    function Check() {

      var spreadsheet = SpreadsheetApp.getActive();
      var sheet = spreadsheet.getActiveSheet();
      var lr = sheet.getLastRow();
      var Summary;

      for (var i=6; i<lr; i++) {
        if (sheet.getRange(i,9) > sheet.getRange(i,4)){
          summary.setvalue("Problem")

         }  
      } 

    }

I want it to start in row 6, because my data starts here. I am only checking column I (therefore 9) and column D (therefore 4)

I am not sure what to do with my for loop and If statement from here? SO now the code is checking every row in column 4 (D) and column I (9), but how do I store store the value, whenever the number in column 9 is larger than the number in column 4? And I also want an output, with all rows where we have a problem, when the code is done? If we don't have any problem, I want a message saying: "no problem"

Can anybody guide me from here?

Upvotes: 0

Views: 83

Answers (1)

Neii
Neii

Reputation: 598

If your output can be set in the sheet (let say in column "J"), you can use this:

function Check() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet       = spreadsheet.getActiveSheet();
  var maxRows     = sheet.getMaxRows(); // get the number of rows
  var startRow    = 6;
  var diffCol1    = 'D';
  var diffCol2    = 'I';
  var outputCol   = 'J';
  var outputStrOK = 'no problem';
  var outputStrKO = 'problem';
  var outputRange = []; // stored values
  for (var i = startRow; i <= maxRows; i++) {
    var valueA = sheet.getRange(diffCol2+i).getValue();
    var valueB = sheet.getRange(diffCol1+i).getValue();
    // add controls on values then
    if (valueA > valueB) {
      outputRange.push([outputStrKO]);
    } else {
      outputRange.push([outputStrOK]);
    }
  }
  // setting a range of values is much faster than fulfilling cell by cell in loop
  sheet.getRange(outputCol+startRow+':'+outputCol+maxRows).setValues(outputRange);
}

you should have this as a result:

#|D|I|J
6|9|4|problem
7|4|9|no problem

Upvotes: 2

Related Questions