Checking/ Referencing Empty Array Values (google App Script)

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

Solution 1:

This 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], []];

functionmyFunction(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

Solution 2:

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() {
  varss= SpreadsheetApp.getActiveSpreadsheet()
  varsheet= ss.getActiveSheet()
  varrange= sheet.getRange("A3:A33")
  varvalue= range.getValues()
  varsheetCheckRange= sheet.getRange(2,2)
  varsheetCheck= sheetCheckRange.getValue()
  if (sheetCheck == "Date") {
    for (i = 0; i < value.length; i++) {
      varrow= sheet.getRange((i + 3), 1, 1, 11)
      varsessionCell= sheet.getRange((i + 3), 3)
      vardateCell= sheet.getRange((i+3), 2)
      varsection1= sheet.getRange((i+3), 2, 1, 5)
      varsection2= 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 company. With the function named 'onEdit()', there's no need for authorization and setup of triggers by each user.

Solution 3:

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.

Solution 4:

Use a comparison 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], []];

functionmyFunction(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');
  }

 }
}