CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tamjid Taha
Tamjid Taha

Reputation: 171

How to add the condition if a cell value is not an email address in Google App Scripts

My following code is working fine but the issue is, I want to add a third condition range.getCell(i, 3) value is not an email address, then the corresponding row will be deleted. How to fix it?

  var spreadsheet = SpreadsheetApp.getActive();
  var dashboard = spreadsheet.getSheetByName("Dashboard");
  var sheetName = dashboard.getRange("A4").getValue();
  //retrieve the start date to use as desired
  var startDate = dashboard.getRange("B4").getDisplayValue();
  var endDate = dashboard.getRange("C4").getDisplayValue();
  var sheet = spreadsheet.getSheetByName(sheetName);
  //chose the range within the specified dates, for this first locate the date column
  var startRow = 2;
  var dateColumn = sheet.getRange(startRow,1,sheet.getLastRow(), 1);
  var dates = dateColumn.getDisplayValues().flat();
  var firstRow = dates.indexOf(startDate)+startRow;
  var lastRow = dates.lastIndexOf(endDate)+startRow;
  //now get the range between (and including) those rows
  var range = sheet.getRange(firstRow, 1, lastRow-firstRow+1, sheet.getLastColumn());

    var deleteRows = 0;      

    for (var i = range.getHeight(); i >= 1; i--){
        if(range.getCell(i, 1).isBlank() || range.getCell(i, 3).isBlank()
           **|| range.getCell(i, 3) IS NOT AN EMAIL Address){**
           sheet.deleteRow(range.getCell(i, 1).getRow());
           deleteRows++;
        }
        else{
         if(range.getCell(i, 6).isBlank()){
           range.getCell(i, 6).setValue(sheetName);
           range.getCell(i, 1).setNumberFormat("yyyy-mm-dd");   
         }      
       }
    }

Upvotes: 1

Views: 78

Answers (1)

Marios
Marios

Reputation: 27390

Since I don't have access to your sheet, it is really difficult for me to undestand if the rest of the code works.

But I managed to replicate part of your example and created the following code snippet which you can use instead of your code after range:

var data = sheet.getRange(firstRow, 1, sheet.getLastRow()-firstRow+1, sheet.getLastColumn()).getValues();
var deleteRows = 0;      
 
  for (var i = data.length-1; i >= 1; i--){
    if(data[i][0] == '' || data[i][2] == '' || !data[i][2].toString().includes('@')){  
      sheet.deleteRow(i+firstRow)
      deleteRows++;
    }
    else if(data[i][5] == '') {
    sheet.getRange(i+firstRow,6).setValue('sheetName');
    sheet.getRange(i+firstRow,1).setNumberFormat("yyyy-mm-dd");  
    }  
  }

Upvotes: 1

Related Questions