检查/引用空数组值(Google App脚本) [英] Checking/ Referencing Empty Array Values (Google App Script)

问题描述

我有一个Google Spreadsheet，它为工作上的每月旅行做了一些简单的单元格引用和数学运算.我有一个函数读取A列中的值(通过工作表上的WEEKDAY()公式实现.如果它是星期六(值5)或星期日(值6)，则会涂黑整个行.以某种方式格式化工作日(值0-4)，并且仅当该行中的日期单元格为空白时，才能将该行重新格式化为标准空白.如何搜索数组(您可以看到如何创建它)并在该数组中找到一个空值?我将复制并粘贴该数组值的示例日志(注意定期[])

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 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("")
      }
    }
  }
}

我还通过将函数重命名为来解决需要单独的触发器的问题函数onEdit()这也使复制电子表格以在公司内部共享变得更加容易.使用名为"onEdit()"的函数，无需每个用户授权和设置触发器.

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.

这篇关于检查/引用空数组值(Google App脚本)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！