CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Abhishek Vaidya
Abhishek Vaidya

Reputation: 102

Copy row to another sheet only if all values are filled

enter image description hereThanks for your time. My questions are as follow:

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

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

Below is my code snippet

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

Upvotes: 0

Views: 196

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Posting this for documentation purposes.

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

Current issue:

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

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

Possible modifications:

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

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

Or, alternatively, to:

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

Reference:

Upvotes: 2

Related Questions