仅当所有值均已填写时，才将行复制到另一张纸上 [英] Copy row to another sheet only if all values are filled

问题描述

感谢您的时间. 我的问题如下:

Thanks for your time. My questions are as follow:

我的SheetA(记录)包含行，当我单击Checkbox时，它应该检查所有单元格A-G是否都包含一些值， 如果A-G中的任何单元格为空，请不要在SheetB中复制tat并显示msgbox

My SheetA (Record) contains rows, when I click on the Checkbox, it should check if all cells A-G contains some value, If any cell from the A-G is blank, do not copy tat in SheetB and show msgbox

我还想实现A列是否包含某个值，然后仅在H列显示下拉列表，并在G列显示复选框

I also want to implement if Column A contains some value then only Show Dropdown at Column H and Checkbox at column G

下面是我的代码段

function onEdit(event) {
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if (s.getName() == "Record" && r.getColumn() == 8 && r.getValue() === true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn() - 1;
    var targetSheet1 = event.source.getSheetByName("Master Record Time");
    var target1 = targetSheet1.getRange(targetSheet1.getLastRow() + 1, 1);
    var range = s.getRange(row, 1, 1, numColumns);
    if (!range.offset(0, 0, 1, 7).getValues()[0].every(e => e.toString() == "")) { // Added
      range.copyTo(target1);
      range.offset(0, 2, 1, 5).clearContent();
      range.offset(0, 7).uncheck();
      Browser.msgBox("Your Record Submitted");
    }
  }
}

推荐答案

出于文档目的将其发布.

Posting this for documentation purposes.

您只想在所有单元格都不为空的情况下复制范围.

You only want to copy the range if none of the cells are blank.

现在，您正在检查every单元格是否为空，如果不是这种情况，则输入if语句:

Right now, you are checking whether every cell is blank, and entering the if statement if that's not the case:

!range.offset(0, 0, 1, 7).getValues()[0].every(e => e.toString() == "")

也就是说，即使有空白单元格，也要输入if语句.

That is to say, it will enter the if statement if there's any non-blank cell, even if there are also blank cells.

如 Tanaike ，您应该将该条件更改为以下内容:

As suggested by Tanaike, you should change that condition to the following:

!range.offset(0, 0, 1, 7).getValues()[0].some(e => e.toString() == "")

或者，或者:

range.offset(0, 0, 1, 7).getValues()[0].every(e => e.toString() != "")

参考:

• Array.prototype.every()
• Array.prototype.some()

Reference:

• Array.prototype.every()
• Array.prototype.some()

这篇关于仅当所有值均已填写时，才将行复制到另一张纸上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！