CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
MasterCrander
MasterCrander

Reputation: 476

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

Upvotes: 1

Views: 20439

Answers (4)

Israel A G
Israel A G

Reputation: 1

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

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

 }
}

Upvotes: 0

Jens Astrup
Jens Astrup

Reputation: 2454

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.

Upvotes: 0

MasterCrander
MasterCrander

Reputation: 476

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 company. With the function named 'onEdit()', there's no need for authorization and setup of triggers by each user.

Upvotes: 2

Etienne de Villers
Etienne de Villers

Reputation: 51

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

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

Upvotes: 4

Related Questions