This process will take 5 steps; a loop, if/else statements, JavaScript arithmetic, comparative operators, and finally writing data back to the sheet. For your row values, it is assumed that you have a header row (frozen or otherwise) that shouldn't be used in calculations.
You can find a great guide on JavaScript operators, arithmetic and general language here:
http://www.w3schools.com/js/default.asp
Step 1: Looping & General Variable Definition
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getActiveRange();
var lastRow = sheet.getLastRow();
var checkColumn = sheet.getMaxColumns();
var checkRange = sheet.getRange(2, checkColumn, (lastRow - 1), 1);
var check = checkRange.getValues();
var red = "#ff0404";
var green = "#3bec3b";
You'll notice that the .getRange method uses numbered rows and columns to define a start place and then an index to define how many rows/ columns to include. .lastRow gives a numerical value to which we need to subtract 1 for the index value.
I also use a technique for looping that includes showing which cells have already been processed. This allows for the line-by-line structure you want while also pinpointing where an error may have occurred.
for (var i = 0; i < check.length; i++) {
This is the basic structure for a loop. We have an index, i, that we increment after each time through the loop. The most common index is 'i' but other variables can be used. It is also good practice to change the name of your indexes should you loop within a loop.
Step 2: If/Else Statements
if (check[i] == "✓") {
continue;
} else {
var checkCell = sheet.getRange((i+2), checkColumn, 1, 1);
function sorter() {
var number = sheet.getRange((i+2), 1, 1, 1).getValue();
var sqrtCell = sheet.getRange((i+2), 2, 1, 1);
var moreCell = sheet.getRange((i+2), 3, 1, 1);
var lessCell = sheet.getRange((i+2), 4, 1, 1);
var outputCell = sheet.getRange((i+2), 5, 1, 1);
We set a condition to check, if (check[i] == "✓") {, and a action to take, continue,. This basically means that if we have already set a marker that the row has been processed, skip over it and check the next one. check[i] uses our index we set to loop through all of the values to look for '✓'.
After that we set an alternative if the statement was false. This is where the meat of the code comes in and processes the numbers and writes the data. The .getRange methods are all to define exactly where to look and where to write.
Step 3: Arithmetic
var squareRoot = Math.sqrt(number);
sqrtCell.setValue(squareRoot);
These are JavaScript methods that will not be shown in the GAS Reference as they are integral to JavaScript itself. You can refer to w3schools to find more. At this point we have found the square root programatically and written the value back to the cell defined earlier.
Step 4 & 5: Operators & Writing Values
if (squareRoot >= 10) {
moreCell.setValue(number);
outputCell.setValue("Select");
outputCell.setBackground(green);
} else {
lessCell.setValue(number);
outputCell.setValue("Reject");
outputCell.setBackground(red);
}
Here is another if/else statement that uses an operator for the condition. We check our sqrt variable this time and use the operator >= for greater-than-or-equal-to. If we find that the statement is true, write the original number to one column, label another column as "Select" and make that label green. If we find that the statement is false, write the original number to a different column, label "Reject", and make the label red.
We finish out the code by telling it to first make the check cell red, then crunch the numbers, then reset the background and write our denotation mark.
}
checkCell.setBackground(red);
sorter();
checkCell.setBackground('white');
checkCell.setValue("✓");
}
}
}
You can make your mark anything you prefer and can use varying CSS color formats to easily label cells as you choose. The order in which you tell your code to perform is crucial. Take care to test frequently and make sure that things are happening when and how you tell them to.
You will notice that we define what the sorter() function is first and then call it in order after. This makes sure that it actually runs right when we need it to.
I would also suggest looking into onEdit() and time-triggers so that it will run automatically when there is an edit made/ value added or at set time intervals.
The full code and reference pictures have been included below.
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getActiveRange();
var lastRow = sheet.getLastRow();
var checkColumn = sheet.getMaxColumns();
var checkRange = sheet.getRange(2, checkColumn, (lastRow - 1), 1);
var check = checkRange.getValues();
var red = "#ff0404";
var green = "#3bec3b";
for (var i = 0; i < check.length; i++) {
if (check[i] == "✓") {
continue;
} else {
var checkCell = sheet.getRange((i+2), checkColumn, 1, 1);
function sorter() {
var number = sheet.getRange((i+2), 1, 1, 1).getValue();
var sqrtCell = sheet.getRange((i+2), 2, 1, 1);
var moreCell = sheet.getRange((i+2), 3, 1, 1);
var lessCell = sheet.getRange((i+2), 4, 1, 1);
var outputCell = sheet.getRange((i+2), 5, 1, 1);
var squareRoot = Math.sqrt(number);
sqrtCell.setValue(squareRoot);
if (squareRoot >= 10) {
moreCell.setValue(number);
outputCell.setValue("Select");
outputCell.setBackground(green);
} else {
lessCell.setValue(number);
outputCell.setValue("Reject");
outputCell.setBackground(red);
}
}
checkCell.setBackground(red);
sorter();
checkCell.setBackground('white');
checkCell.setValue("✓");
}
}
}
