Question

60

Checking/ Referencing Empty Array Values (Google App Script)

rated 0 times [ 67] [ 7] / answers: 1 / hits: 18175 / 9 Years ago, wed, september 2, 2015, 12:00:00

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

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

jazminuniquer

Add To Favorites

Follow

Total Points: 63

Total Questions: 121

Total Answers: 96

Location: Cambodia

Member since Thu, May 21, 2020

4 Years ago

jazminuniquer questions

1 Next.js change locale for the current path that has query, using router

Sat, Nov 20, 21, 00:00, 3 Years ago

1 Error: bundle initial-es5 exceeded maximum budget. Budget 6.00 MB was not met by 133.51 kB with a total of 6.13 MB

Thu, Feb 18, 21, 00:00, 3 Years ago

1 How to get values from client.users.fetch(id) in discord.js

Thu, Sep 3, 20, 00:00, 4 Years ago

1 Apply motion to react component Framer-Motion

Thu, Jun 25, 20, 00:00, 4 Years ago

1 Vuetify - How to select a row data using v-radio-group in v-data-table

Tue, May 19, 20, 00:00, 4 Years ago

View All

answered 9 Years ago zoiel · Accepted Answer

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.