check the row before moving in google sheets

Question

I use this script to move row to another sheet when value in column 21 =Delivered

I need before moving the row check if there are empty specific cells in column 6,15,18,19 and if there are some empty cells show alert of the value that are missing

i'm not a programer I try to understand how the script work and embedding code in other code

so if you can help me to rewrite this scrtip to be faster and working better

Thanks in advance

function onEdit() {

 var sheetNameToWatch1 = "Assign_Page";
 var columnNumberToWatch = 21;
 var valueToWatch = "Delivred";
 var sheetNameToMoveTheRowTo = "C_Delivery_Archive";
 var sheetNameToMoveTheRowTo2 = "Buffering";

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getActiveCell();

 if ( (sheet.getName() == sheetNameToWatch1) && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
 var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
 var targetSheet2 = ss.getSheetByName(sheetNameToMoveTheRowTo2);


 var targetRange2 = targetSheet2.getRange(targetSheet2.getLastRow()+ 1, 1);
 sheet.getRange(range.getRow(), 1, 1, 25).copyTo(targetRange2, {contentsOnly:true});

 var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
 sheet.getRange(range.getRow(), 1, 1, 25).copyTo(targetRange, {contentsOnly:true});

sheet.deleteRow(range.getRow());

var Sheet4 = ss.getSheetByName("Delivery_Information");
var Sheet1 = ss.getSheetByName("Dashbord");
var Sheet2 = ss.getSheetByName("Assign_Page");
var Sheet3 = ss.getSheetByName("Report");
var Sheet5 = ss.getSheetByName("Invoice_Info");
var Sheet6 = ss.getSheetByName("Fleet_Status");

 Sheet4.showSheet();
 Sheet1.hideSheet();
 Sheet2.hideSheet();
 Sheet3.hideSheet();
 Sheet5.hideSheet();
 Sheet6.hideSheet();
 ss.setActiveSheet(ss.getSheetByName('Delivery_Information'));     
 }
}

Ed Nelson Ed Nelson · Accepted Answer · 2017-10-29T18:40:59

I think this will do what you want. If column 21 is set to 'Delivered' it checks columns 6, 15, 18, and 19 to see if they are filled in. If they are not, it pops up a message box listing the missing cells. It also resets 'Delivered' to blank. Fill in the missing cells and set 'Delivered' again. The same checks are run again. If everything is there, no message appears and the rest of the script runs.

function onEdit() {
 var sheetNameToWatch1 = "Assign_Page";
 var columnNumberToWatch = 21;
 var sheetNameToMoveTheRowTo = "C_Delivery_Archive";
 var sheetNameToMoveTheRowTo2 = "Buffering";
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getActiveCell();
 var row= sheet.getActiveCell().getRow()
   if ( sheet.getName() == sheetNameToWatch1 && range.getColumn() == columnNumberToWatch && range.getValue() == "Delivered") {
   var cnt =checkCells()
   if(cnt > 0){return}//If all required cells are not filled in quit.
    else{
 var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
 var targetSheet2 = ss.getSheetByName(sheetNameToMoveTheRowTo2);


 var targetRange2 = targetSheet2.getRange(targetSheet2.getLastRow()+ 1, 1);
 sheet.getRange(range.getRow(), 1, 1, 25).copyTo(targetRange2, {contentsOnly:true});

 var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
 sheet.getRange(range.getRow(), 1, 1, 25).copyTo(targetRange, {contentsOnly:true});

sheet.deleteRow(range.getRow());

var Sheet4 = ss.getSheetByName("Delivery_Information");
var Sheet1 = ss.getSheetByName("Dashbord");
var Sheet2 = ss.getSheetByName("Assign_Page");
var Sheet3 = ss.getSheetByName("Report");
var Sheet5 = ss.getSheetByName("Invoice_Info");
var Sheet6 = ss.getSheetByName("Fleet_Status");

 Sheet4.showSheet();
 Sheet1.hideSheet();
 Sheet2.hideSheet();
 Sheet3.hideSheet();
 Sheet5.hideSheet();
 Sheet6.hideSheet();
 ss.setActiveSheet(ss.getSheetByName('Delivery_Information'));     
 }
  }}

function checkCells() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var row=ss.getActiveCell().getRow()
  var c=[6,15,18,19] //Array of columns to check
  var empty=[]//Array for empty cells
  for(i=0;i<c.length;i++){
    if(s.getRange(row,c[i]).getValue()==""){//If cell is blank
       var A1=s.getRange(row,c[i]).getA1Notation()//Get cell address
       empty.push(A1)//Write empty cell address to empty array
    }}
if(empty.length != 0){//If all required cell don't have a value
      Browser.msgBox("Cells "+empty+" can not be blank. Fill in then reset 'Delivered'", Browser.Buttons.OK_CANCEL) //Show message
      s.getRange(empty[0]).activate()//Activate first cell that needs a value
      s.getRange(row, 21).clearContent()//Set 'Delivered to blank.
  var rtn=empty.length //Return empty array count
  return rtn
  }}

If you want to write the headers from row 2 in the message instrad of the cell addresses, replace the checkCells function with this:

function checkCells() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var row=s.getActiveCell().getRow()

  var c=[6,15,18,19] //Array of columns to check
  var empty=[]//Array for empty cells
  var header=[]//Array of Headers in row 2
  for(i=0;i<c.length;i++){
    if(s.getRange(row,c[i]).getValue()==""){//If cell is blank
       var A1=s.getRange(row,c[i]).getA1Notation()//Get cell address
       var hdr=s.getRange(2,c[i]).getValue()//Get the header values
       empty.push(A1)//Write empty cell address to empty array
       header.push(hdr)
    }}
  if(empty.length != 0){//If all required cell don't have a value
     // Browser.msgBox("Cells "+empty+" can not be blank. Fill in then reset 'Delivered'", Browser.Buttons.OK_CANCEL) //Show cell message
      Browser.msgBox("Column(s) "+header+" can not be blank. Fill in then reset 'Delivered'", Browser.Buttons.OK_CANCEL) //Show header message
      s.getRange(empty[0]).activate()//Activate first cell that needs a value
      s.getRange(row, 21).clearContent()//Set 'Delivered to blank.
  var rtn=empty.length //Return empty array count
  return rtn
  }}

check the row before moving in google sheets

1 Answers