Google Sheets – RegExp for Data Validation

google sheetsregex

I need a help for regexp writing to validate format:

any_positive_integer@any_text,any_positive_integer@any_text, …..

To be exact, "any_positive_integer@any_text" separated by commas and inside, number and text separated by "@".

Any idea and help for regexp doing it?

Best Answer

The quick start tutorial at https://developers.google.com/apps-script/quickstart/macros shows how to write a JavaScript function in the App Script editor and call it from a Google spreadsheet.

In this case the function can use a regexp such as

/\d+@[^,]+(,\d+@[^,]+)*/

That means one or more digits followed by an @ and one or more non-comma characters, followed by zero or more repetitions of a comma and the same pattern.

That pattern will match anywhere in the given string. If you want to match against the entire input string, i.e. not allow other characters before or afterwards, then start the pattern with /^ and end it with $/ instead of plain / and /. If you then want to allow white space before or after the pattern, you can add \s+ right after the ^ and/or before the $.

You'll want to call a JavaScript regexp method like test() to test a string against the pattern. Example using a simpler pattern (just comma separated numbers):

function check(input) {
  return /^\d+(,\d+)*$/.test(input);
}

There are web sites containing tools for interactive testing of regular expressions. That's a good way to go since these things can be tricky and hard to read. Build it up slowly and test it thoroughly. I didn't test the expressions above. You should test that they meet your needs.

Edit: Per https://support.google.com/docs/answer/3098292 you don't need to call JavaScript to use regular expressions in Google spreadsheets. Example formula:

=REGEXMATCH(A1, "^\d+$")

using a simpler pattern that matches a string of digits.

Related Solutions

Google-sheets – How to split then combine data to rows

It seems you want the processed entries (the row with ~) to remain in the spreadsheet. In this case it's reasonable to have some indicator of whether the row has been processed by the script, so it doesn't add the same things again. Something like this:

+---------+------------+---------------------------------------------+----------+-----------+
| Invoice |    Name    |                 Description                 |   Due    | Processed |
+---------+------------+---------------------------------------------+----------+-----------+
|    1087 | Customer22 | 2,20,20,None~Item 485,Item 234,Item879,None | 6/2/2015 |           |
|    1088 | Customer33 | 3,4~this,that                               | 6/5/2015 |           |
+---------+------------+---------------------------------------------+----------+-----------+

And here is a script that handles it. Standard JavaScript string methods: split, indexOf, and concatenation.

function splitJoin() {
  var i, j, dataString, quantItems, quant, items;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  for (i=0; i<values.length; i++) {
    dataString=values[i][2];
    if ((dataString.indexOf('~')!=-1) && (values[i][4]!='yes')) {
      quantItems=dataString.split('~');
      quant = quantItems[0].split(',');
      items = quantItems[1].split(',');
      for (j=0; j<quant.length; j++) {
        if (quant[j]!='None') {
           sheet.appendRow([values[i][0], values[i][1], quant[j]+'-'+items[j], values[i][3]]);
        }
      }
      range.getCell(i+1, 5).setValue('yes');
    }
  }
}

Output:

+---------+------------+---------------------------------------------+----------+-----------+
| Invoice |    Name    |                 Description                 |   Due    | Processed |
+---------+------------+---------------------------------------------+----------+-----------+
|    1087 | Customer22 | 2,20,20,None~Item 485,Item 234,Item879,None | 6/2/2015 | yes       |
|    1088 | Customer33 | 3,4~this,that                               | 6/5/2015 | yes       |
|    1087 | Customer22 | 2-Item 485                                  | 6/2/2015 |           |
|    1087 | Customer22 | 20-Item 234                                 | 6/2/2015 |           |
|    1087 | Customer22 | 20-Item879                                  | 6/2/2015 |           |
|    1088 | Customer33 | 3-this                                      | 6/5/2015 |           |
|    1088 | Customer33 | 4-that                                      | 6/5/2015 |           |
+---------+------------+---------------------------------------------+----------+-----------+

In this example there is no separation between old and new format, because appendRow puts a row directly under the existing data. But if you put something like "new format" at the bottom of spreadsheet, it will be appended after it.

But it's probably better to put the exported data on another sheet, so that the existing one can still receive new entries without messing things up. E.g., add

var targetSheet = ss.getSheetByName("Exported");

at the beginning, and then use targetSheet.appendRow(... instead of sheet.appendRow(....

Google-forms – Regular expression in Google Form crashes browser tab

Short answer

Looks that you found a failure on the user Google Forms user input sanitation. Please, on the Google Form editor, use the ? > Report a problem to let Google Form team engineers know about problem.

Explanation

A regular expression used as data validation rule should not make the browser through an error message. If the data validation is invalid, the Google Form should display an error message to the form editor.

Regarding the bonus question, that is the way that data validation work on Google Forms. You could also send feedback to Google about this too.

Google Docs Help Forum

Google forms keeps failing to save when certain regular expressions are entered., posted on September 15, 2016