CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user3911545
user3911545

Reputation: 309

Filter content by empty cells

I've got a Google spreadsheet with a list of users. Those users have got data (name, email, age).

Also there is a function that send by email this list of users.

This function works ok, but I need to send only the users with their empty cells.

Any help how to do it in Google Apps Scripts spreadsheets?

Example

Email body:

username | age          | email

jhon     |  **empty**   | [email protected]

sam      | 25           | **empty**

Upvotes: 0

Views: 967

Answers (1)

Serge insas
Serge insas

Reputation: 46794

There are many ways to do that and even more ways to present data in an email...

Here is one of them, from this code you should be able to build a script that shows result the way you want... the script works as it is and shows the logic of the selection.

See comments in code that explain each step

function sendIncompleteData() {
  var sh = SpreadsheetApp.getActive().getActiveSheet();
  var data = sh.getDataRange().getValues();// get all the data in the sheet
  var headers = data.shift();// extract headers and keep in a variable
  Logger.log(headers+'\n'+data);// check values in the logger
  var toSend = [];// this will hold the list to send
  for(var n in data){ // iterate the array
    var send = false;
    var row = data[n];// row by row and cell by cell, starting in second column
    var msg = 'Missing element for '+row[0]+' : ';// prepare message for 1 row
    var present = '  (';// this will show available data
    for(var c=1 ; c<row.length ; c++){
      if(row[c]==''){msg+=headers[c]+'    ' ; send=true} else {present+=row[c]+' - '};
    }
    if(send){toSend.push(msg+present+')')};// collect data to send
  }
  if(toSend.length>0){ // if at least one row has missing data
    MailApp.sendEmail(Session.getEffectiveUser().getEmail(),'missing data in sheet '+sh.getName(),toSend.join('\n'));// send the message to the sheet user (change eventually the recipient to your need
  }
}

note : it will adapt automatically to any number of columns, you must have a header in row 1 with variable names (name, email, age ...)

Upvotes: 1

Related Questions