Google Sheets Wildcards in if statements

Question

I have a Google Sheet that has form responses. The e-mail address was not required, however it should have been. Either way, I am trying to back-fill the e-mail addresses (in order to make them ready to import as Spiceworks tickets, but I digress). I am going through and typing in usernames, but I want Sheets to auto-fill the domain. I was thinking I could do this by having it detect that the string ended in @, and then just adding the domain to it. Currently I have:

    // assumes source data in sheet named Done 14-15
  // test column with done is col 9 or I

      if(s.getName() == "Done 14-15" && r.getColumn() == 9 && r.getValue() == "?@" ) {
        var row = r.getRow();
        var value = r.getValue();
        r.setValue(value + "example.org");
        var numColumns = s.getLastColumn();
        s.getRange(row, 1, 1, numColumns).copyTo(target);
      }

As you can see, I have a question mark for a wildcard. I have tried using an asterisk or a percentage sign as well, and not gotten anywhere. It will replace if I have literally ?@ in there, but I want it to take anything@ and append our domain.

score 0 · Answer 1 · answered Oct 06 '15 at 14:38

0

RegEx should solve your problem.

Replace the r.getValue() == "?@" with

var regEx = new RegExp('.*@$')
if (regEx.test(r.getValue())) {
    // your code
}

answered Oct 06 '15 at 14:38

Zahir Ally

326
3
5

score 0 · Answer 2 · answered Oct 06 '15 at 17:19

0

Instead of r.getValue() == "?@" you can write r.getValue().endsWith("@")

answered Oct 06 '15 at 17:19

Tesseract

8,049
2
20
37

score 0 · Answer 3 · edited May 23 '17 at 12:29

The email addresses can be easily updated like this:

var newValue = event.value.replace(/@$/,'@example.org');

Where the match is not found, the replacement will not happen... and newValue will equal the original value. Instead of checking for the match before deciding to do something, I'm suggesting doing it then checking the result.

Since you are entering the email addresses by hand, this is a good application of the onEdit() simple trigger and its event object.

function onEdit(event) {
  var r = event.range;
  var s = r.getSheet();

  if (s.getName() == "Done 14-15" && r.getColumn() == 9 && r.getRow() > 1) {
    // Replace an @ at the end of the string with domain
    var newValue = event.value.replace(/@$/,'@example.org');

    // If value changed, write it back to spreadsheet
    if (event.value !== newValue) {
      event.range.setValue(newValue);
    }
  }
}

If you have rows that have already been edited and need to be checked, this function will take care of them. It uses the technique from How can I test a trigger function in GAS? to create a fake event, then passes it to the onEdit() trigger function.

// Call onEdit for each row in conversion sheet
function convertAllEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName( "Done 14-15" );
  var lastRow = sheet.getLastRow();
  for (var row=1; row<lastRow; row++) {
    var fakeEvent = {
      range: sheet.getRange(row,9);
      value: range.getValue();
    };
    onEdit( fakeEvent );
  }
}

Google Sheets Wildcards in if statements

3 Answers3