CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Steve Stoltz
Steve Stoltz

Reputation: 3

Delete Columns from a spreadsheet that has blanks or 0's

I've seen several scripts listed on how to delete rows that contain blanks or 0's however I have a form and once submitted the responses are entered into the last row. The form has required fields but also has fields that don't need to be answered. For the columns that don't have a response I would like to not have the headers show up on the email on once the form is submitted. If I use the scripts that deal with rows all the information is deleted.

My current script is:

function sendFormByEmail(e) 
{    
var email = "[email protected]"; 
var subject = "Form Submitted";  
var s = SpreadsheetApp.getActiveSheet();
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];    
var message = "";    

for(var i in headers)
message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + "\n\n"; 

MailApp.sendEmail(email, subject, message); 
}

Right now if the person filling out the form doesn't provide a response I get the following:

Cell Number =

I would like to get only headers on responses and eliminate any blanks or "0's".

Upvotes: 0

Views: 139

Answers (1)

Mogsdad
Mogsdad

Reputation: 45740

You simply need to validate your conditions before adding a response value to the message.

Since a blank response can sometimes contain spaces (and thus be 'non-blank'), first use the .replace() method to strip any padding before testing the string.

You can adjust the if statement here to suit your needs:

function sendFormByEmail(e) 
{    
  var email = "[email protected]"; 
  var subject = "Form Submitted";  
  var s = SpreadsheetApp.getActiveSheet();
  var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];    
  var message = "";    

  for(var i in headers) {
    var value = e.namedValues[headers[i]]
                 .toString()
                 .replace(/(^\s+|\s+$)/g, ''); // Remove padding
    // Only report form responses that aren't blank or 0.
    if (value.length > 0 && value != '0') {
      message += headers[i] + ' = '+ value  + "\n\n"; 
    }
  }

  MailApp.sendEmail(email, subject, message); 
}

Upvotes: 2

Related Questions