I have a Google Spreadsheet that does some simple cell referencing and math for monthly travel for work. I have a function that reads the values in column A (which are attained by the WEEKDAY() formula on the sheet. It blacks out the whole row if it is a Saturday(value 5) or Sunday(value 6). I need it to format the weekdays a certain way (values 0-4) and, only if the date cell in that row is blank, to reformat that row back to the standard blank. How do I search through an array (you can see how I created it) and find an empty value in that array? I will copy-paste an example log of the array values (notice the periodic [])
function weekendClean() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getActiveSheet()
var range = sheet.getRange("A3:A33")
var value = range.getValues()
var sheetCheckRange = sheet.getRange(2,2)
var sheetCheck = sheetCheckRange.getValue()
if (sheetCheck == "Date") {
for (i = 0; i < value.length; i++) {
if (value[i] == 5 || value[i] == 6) {
var row = sheet.getRange((i + 3), 1, 1, 11)
var cell = sheet.getRange((i + 3), 3)
row.setBackground("Black")
row.setBorder(true, null, true, null, null, null)
//top, left, bottom, right, vertical, horizontal
cell.setValue("W")
} if (value[i] == 0 || value[i] == 1 || value[i] == 2 || value[i] == 3 || value[i] == 4) {
var section1 = sheet.getRange((i+3), 2, 1, 5)
var section2 = sheet.getRange((i+3), 8, 1, 3)
section1.setBackground("white")
section2.setBackground("white")
section1.setBorder(false, null, false, null, null, false)
section2.setBorder(false, null, false, null, null, false)
}
}
}
}
Also, feel free to clean up that code with explanation (especially for the bit that reads what to do for the weekdays)
[15-09-01 17:54:04:051 PDT] [[1.0], [2.0], [], [4.0], [5.0], [6.0],
[0.0], [1.0], [], [3.0], [4.0], [5.0], [], [], [1.0], [], [3.0],
[4.0], [5.0], [6.0], [0.0], [1.0], [2.0], [3.0], [], [5.0], [6.0],
[0.0], [1.0], [2.0], []]
I have a Google Spreadsheet that does some simple cell referencing and math for monthly travel for work. I have a function that reads the values in column A (which are attained by the WEEKDAY() formula on the sheet. It blacks out the whole row if it is a Saturday(value 5) or Sunday(value 6). I need it to format the weekdays a certain way (values 0-4) and, only if the date cell in that row is blank, to reformat that row back to the standard blank. How do I search through an array (you can see how I created it) and find an empty value in that array? I will copy-paste an example log of the array values (notice the periodic [])
function weekendClean() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getActiveSheet()
var range = sheet.getRange("A3:A33")
var value = range.getValues()
var sheetCheckRange = sheet.getRange(2,2)
var sheetCheck = sheetCheckRange.getValue()
if (sheetCheck == "Date") {
for (i = 0; i < value.length; i++) {
if (value[i] == 5 || value[i] == 6) {
var row = sheet.getRange((i + 3), 1, 1, 11)
var cell = sheet.getRange((i + 3), 3)
row.setBackground("Black")
row.setBorder(true, null, true, null, null, null)
//top, left, bottom, right, vertical, horizontal
cell.setValue("W")
} if (value[i] == 0 || value[i] == 1 || value[i] == 2 || value[i] == 3 || value[i] == 4) {
var section1 = sheet.getRange((i+3), 2, 1, 5)
var section2 = sheet.getRange((i+3), 8, 1, 3)
section1.setBackground("white")
section2.setBackground("white")
section1.setBorder(false, null, false, null, null, false)
section2.setBorder(false, null, false, null, null, false)
}
}
}
}
Also, feel free to clean up that code with explanation (especially for the bit that reads what to do for the weekdays)
[15-09-01 17:54:04:051 PDT] [[1.0], [2.0], [], [4.0], [5.0], [6.0],
[0.0], [1.0], [], [3.0], [4.0], [5.0], [], [], [1.0], [], [3.0],
[4.0], [5.0], [6.0], [0.0], [1.0], [2.0], [3.0], [], [5.0], [6.0],
[0.0], [1.0], [2.0], []]
4 Answers
Reset to default 4This is how you would retrieve an empty array in your situation :
var x = [[1.0], [2.0], [], [4.0], [5.0], [6.0], [0.0], [1.0], [], [3.0], [4.0], [5.0], [], [], [1.0], [], [3.0], [4.0], [5.0], [6.0], [0.0], [1.0], [2.0], [3.0], [], [5.0], [6.0], [0.0], [1.0], [2.0], []];
function myFunction(x) {
for (var i = 0; i < x.length; i ++) {
if(x[i].length == 0){
// array is empty
Logger.log('empty');
} else {
//array not empty
Logger.log('not empty');
}
}
}
I also noticed that the ";" is missing from your statements. Your script runs as it is ?
Cheers
I worked around the issue of finding which spot in the array was empty and rather set up a variable to reference each cell in the loop and check if it itself was blank. If it saw that it was, it performed the code block. Example below.
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getActiveSheet()
var range = sheet.getRange("A3:A33")
var value = range.getValues()
var sheetCheckRange = sheet.getRange(2,2)
var sheetCheck = sheetCheckRange.getValue()
if (sheetCheck == "Date") {
for (i = 0; i < value.length; i++) {
var row = sheet.getRange((i + 3), 1, 1, 11)
var sessionCell = sheet.getRange((i + 3), 3)
var dateCell = sheet.getRange((i+3), 2)
var section1 = sheet.getRange((i+3), 2, 1, 5)
var section2 = sheet.getRange((i+3), 8, 1, 3)
if (value[i] == 5 || value[i] == 6) {
row.setBackground("Black")
row.setBorder(true, null, true, null, null, null)
//top, left, bottom, right, vertical, horizontal
sessionCell.setValue("W")
} if (value[i] == 0 || value[i] == 1 || value[i] == 2 || value[i] == 3 || value[i] == 4) {
section1.setBackground("white")
section2.setBackground("white")
section1.setBorder(false, null, false, null, null, false)
section2.setBorder(false, null, false, null, null, false)
} if (dateCell.isBlank()) {
section1.setBackground("white")
section2.setBackground("white")
section1.setBorder(false, null, false, null, null, false)
section2.setBorder(false, null, false, null, null, false)
sessionCell.setValue("")
}
}
}
}
I also worked around needing a separate trigger by renaming the function to function onEdit() This also makes it much easier to copy the spreadsheet for sharing within the pany. With the function named 'onEdit()', there's no need for authorization and setup of triggers by each user.
The ArrayLib library would be able to do this for you, it makes the process of dealing with 2D arrays much simpler. For this, you'd want the IndexOf function, and use -1 to search through all columns.
Use a parison to null
var x = [[1.0], [2.0], [], [4.0], [5.0], [6.0], [0.0], [1.0], [], [3.0], [4.0], [5.0], [], [], [1.0], [], [3.0], [4.0], [5.0], [6.0], [0.0], [1.0], [2.0], [3.0], [], [5.0], [6.0], [0.0], [1.0], [2.0], []];
function myFunction(x) {
for (var i = 0; i < x.length; i ++) {
if(x[i] == null){
// array is empty
Logger.log('empty');
} else {
//array not empty
Logger.log('not empty');
}
}
}