[Google App Script] How to make Iterating Check
#GoogleScript #Iterate
After your watching, you will know:
1. How to get the properties of file, sheet, cell, and cell range
2. How to set the cell properties
3. How to iterate a range in a sheet
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Please make sure to leave a like👍 & comment
❤️ SUBSCRIBE IF YOU ARE NEW AND TURN THE BELL ON🔔
The source code:
NOTE: NG - Not greater than
function checkScore() {
var ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange("C3:L12");
for(var row = 1; row NG range.getWidth(); row++) {
for(var col = row + 1; colNG range.getHeight(); col++) {
sheet.getRange("O2").setValue(range.getCell(row,col).getA1Notation() + ", " + range.getCell(col,row).getA1Notation());
var colorU = range.getCell(row,col).getBackground();
var colorD = range.getCell(col,row).getBackground();
range.getCell(row,col).setBackground("yellow");
range.getCell(col,row).setBackground("yellow");
if (range.getCell(row,col).getValue() != -range.getCell(col,row).getValue()) {
range.getCell(row,col).setBackground("green");
range.getCell(col,row).setBackground("green");
row = range.getWidth() + 1;
break;
} else {
range.getCell(row,col).setBackground(colorU);
range.getCell(col,row).setBackground(colorD);
}
}
}
sheet.getRange("O2").setValue("Done");
}
function GetInfo(a1Notation) {
var ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(a1Notation);
var r1 = 1;
var rL = range.getHeight(); //last row
var c1 = 1;
var cL = range.getWidth(); //last column
var leftUp = "\t" + r1 + "," + c1 + ": ";
var rightUp = "\t" + r1 + "," + cL + ": ";
var leftDown = "\t" + rL + "," + c1 + ": ";
var rightDown = "\t" + rL + "," + cL + ": ";
ui.alert(ToString([ "File Name:" + sheet.getName()
, "Sheet Name:" + sheet.getSheetName()
, "A1Notation:" + range.getA1Notation()
, "getLastRow, getLastColumn:" + range.getLastRow()+","+range.getLastColumn()
, "Height, Width: " + range.getHeight() + "," + range.getWidth()
, "Cells:"
, leftUp + range.getCell(r1,c1).getValue()
, rightUp + range.getCell(r1,cL).getValue()
, leftDown + range.getCell(rL,c1).getValue()
, rightDown + range.getCell(rL,cL).getValue()
])
);
}
function ToString(arrItems){
var newLine = "\r\n";
var msg="";
for (const item of arrItems){
msg += item + newLine;
}
return msg;
}