CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

Stop activation of script if cells not populated

I have the following script which is triggered by a tick box in column Y of each row of the "Events/Incidents" sheet:

// Copy and paste from Events/Incidents sheet to Vehicle Damage sheet
 {var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  var sourcesheetname = "Events/Incidents";
  var checkbox = range.getValue();
  if (sheet.getName() == sourcesheetname && column == 25 && row > 2 && checkbox == true) {
    var targetsheetname = "Vehicle Damage";
    var target = e.source.getSheetByName(targetsheetname);
    var numCols = sheet.getLastColumn();
    var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
    values.splice(17)
    values.splice(8, 8)
    values.splice(5, 1)
    values.splice(3, 1); // Removing undesired values
    var lastRow = target.getLastRow();
    var lastCol = target.getLastColumn();
    target.appendRow(values); // Append new row
     if (lastRow >= 3) 
      target.getRange(lastRow, 10, 1, 1).copyTo(target.getRange(lastRow + 1, 10, 1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMULA);  //Copy and paste the formula in column J 
      target.getRange(lastRow, 19, 1, 1).copyTo(target.getRange(lastRow + 1, 19, 1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMULA); //Copy & paste the formula in column S
      target.getRange(lastRow + 1, 1, 1, values.length + 12).setBorder(true, true, true, true, true, true, "white", SpreadsheetApp.BorderStyle.SOLID);
      target.getRange(lastRow, values.length).offset(1, 1).activate();}

When the tick box is checked, the script copies columns A, B, C, E, G, H and Q from the ticked row of the "Event/Incident" sheet and pastes them sequentially into columns A:G of the "Vehicle Damage" sheet.

I found that users are ticking the checkbox before the cells in the "Events/Incident" sheet have been populated. I, therefore, want to stop the activation of the script until all of the cells A, B, C, E, G, H and Q from the ticked row of the "Event/Incident" sheet have been populated. I would also like a message to pop up telling the user that they must first populate the cells.

I would appreciate help with this.

Upvotes: 0

Views: 71

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

  • After verifying that the checkbox is checked, query for the values in the edited row
  • Make sure that the corresponding values are != "" and != " " before continuing
  • Else - prompt the user to fill out all the columns of interest

Sample:

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  var sourcesheetname = "Events/Incidents";
  var checkbox = range.getValue();
  if (sheet.getName() == sourcesheetname && column == 25 && row > 2 && checkbox == true) {
    var numCols = sheet.getLastColumn();
    var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
    if(values[0] != "" && values[0] != " " && values[1] != "" && values[1] != " " && values[2] != "" && values[2] != " " && values[4] != "" && values[4] != " " && values[6] != "" && values[6] != " " && values[7] != "" && values[7] != " " && values[16] != "" && values[16] != " "){
      var targetsheetname = "Vehicle Damage";
      var target = e.source.getSheetByName(targetsheetname);
      values.splice(17)
      values.splice(8, 8)
      values.splice(5, 1)
      values.splice(3, 1); // Removing undesired values
      var lastRow = target.getLastRow();
      var lastCol = target.getLastColumn();
      target.appendRow(values); // Append new row
      if (lastRow >= 3) 
        target.getRange(lastRow, 10, 1, 1).copyTo(target.getRange(lastRow + 1, 10, 1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMULA);  //Copy and paste the formula in column J 
      target.getRange(lastRow, 19, 1, 1).copyTo(target.getRange(lastRow + 1, 19, 1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMULA); //Copy & paste the formula in column S
      target.getRange(lastRow + 1, 1, 1, values.length + 12).setBorder(true, true, true, true, true, true, "white", SpreadsheetApp.BorderStyle.SOLID);
      target.getRange(lastRow, values.length).offset(1, 1).activate(); 
    }else {
      range.setValue(false);
      Browser.msgBox("Please fill out columns A, B, C, E, G, H and Q before ticking the checkbox");
    }
  }
}

Upvotes: 1

Related Questions