How to check multiple blank cell using ISBLANK before submit data?-CodePudding

I have problem with dealing isBlank() and getRange() from apps script.

I have a simple form and would like to check all the cell is filled up upon user clicking a submit button (assigned the script submitData)

Here's my script:

function submitData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); //Form Sheet
  var datasheet = ss.getSheetByName("Data"); //Data Sheet
  var ui = SpreadsheetApp.getUi(); //Access to UI

  var validate_cell = formSS.getRange("B3:B7");

  if (!validate_cell.isBlank()) {
    
    //Input Values
    var values = [[formSS.getRange("B3").getValue(),
                  formSS.getRange("B8").getValue(),
                  formSS.getRange("B4").getValue()]];
    
    datasheet.getRange(datasheet.getLastRow() 1, 1, 1, 3).setValues(values);

    var formula = formSS.getRange("B8").getFormulas();
    var rangetoclear = ["B5", "B6", "B7", "B8"]
    formSS.getRangeList(rangetoclear).clearContent();
    formSS.getRange("B8").setFormulas(formula)

  } else {
    error = 'Please fill up everything';
    ui.alert(error);
  }
}

Right now, my validation of isBlank() only works in prompting the ui.alert(error) if all the cells in getRange("B3:B7") is empty.

I have tried using getRangeList() but it doesn't seem to work with isBlank() I have tried changing to getRange("B3", "B4", "B5", "B6", :B7"), but return with errors. I have also tried for loop for getRange but i am not sure what is the right ways as all my for loop implementation doesn't work.

Can someone please help me to straighten my mind?

CodePudding user response：

I changed your code a little bit

function submitData() {
  let ss        = SpreadsheetApp.getActive(),
      formSS    = ss.getSheetByName("Form"),                       //Form Sheet
      formData  = formSS.getRange("B3:B8").getValues().flat(),     //Form Data
      formDataHasBlankCells  = formData.includes('');              //check if there are any empty cells in the data

  if (formDataHasBlankCells) {
    SpreadsheetApp.getUi().alert('Please fill up everything')
  } else {
    let datasheet = ss.getSheetByName("Data"),
        values = [[formData[0],    // B3 value
                   formData[5],    // B8 value
                   formData[1]]];  // B4 value
    datasheet.getRange(datasheet.getLastRow() 1, 1, 1, 3).setValues(values);

    // I removed cell B8 from the cleaning range because this cell contains the formula, 
    // not the value - so there is no need to clear the cell and overwrite the formula
    let rangetoclear = ["B5", "B6", "B7"];
    formSS.getRangeList(rangetoclear).clearContent();
  }

In order to check for empty cells in the range B3:B7, I used the array method .includes()