CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Chris Bell
Chris Bell

Reputation: 3

Google App Script for checking if cells are blank

I've got a script that saves data from cells to a seperate sheet then clears the cells.

I'd like to add in a message box/error pop up or something that let's the user know there is a blank cell and to fill it out.

This would ideally stop the script from saving the data to prevent duplicate entry.

My current script is this 

function  submitData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); 
  var datasheet = ss.getSheetByName("Data"); 

    var values = [[formSS.getRange("C3").getValue(),
                 formSS.getRange("C5").getValue(),
                 formSS.getRange("C7").getValue(),
                 formSS.getRange("C9").getValue(),
                 formSS.getRange("C11").getValue(),
                 formSS.getRange("C13").getValue()]];

  datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 6).setValues(values);
  Browser.msgBox('Entry Captured - Thank you come again!')


  var sheet = SpreadsheetApp.getActive().getSheetByName('Form');
  sheet.getRange('C3:C9').clearContent();
}

Upvotes: 0

Views: 2382

Answers (3)

Travis Edward
Travis Edward

Reputation: 1

//Create a seperate list of values that you want required and name it required. 
//This will loop in the required fields and send an error message if one is missing. 

var REQUIRED = [[formSS.getRange("C3").getValue(),
             formSS.getRange("C5").getValue(),
             formSS.getRange("C7").getValue(),
             formSS.getRange("C9").getValue()]];

REQUIRED[0].forEach(function(val) {
   if (val === "") { 
     throw new Error("Please fill in all the required fields");
    }
})

Upvotes: 0

TheMaster
TheMaster

Reputation: 50856

This is a typical use case for array.some:

//if some values are empty strings     
if(values[0].some(val => val === '')) throw new Error("All fields are compulsory");

Upvotes: 0

Amit Agarwal
Amit Agarwal

Reputation: 11278

You can add this blank value check for the values array.

values[0].forEach(function(val) {
   if (val === "") { 
       Browser.msgBox("Required Cell is empty");
       return;
   }
})

Upvotes: 1

Related Questions