google sheetsgoogle-apps-scriptregex
Let's say that I have a big Google spreadsheet that multiple users have access to, and I'm not able to control which users have access to it. I have a concern for trolls.
How would I prevent people from entering certain words, specifically slurs and curse words, something fancy with regex in the form?
The following piece of code will set the background color of the entire row:
function onFormSubmit(e) {
// get data and make it a 1D array
var time = e.range.getValues()[0];
// get the minutes and hours of the timestamp (time[0])
var minutes = time[0].getMinutes(), hours = time[0].getHours();
// calculate the total amount of minutes
var totalMin = (hours * 60) + minutes;
// do the logic (thanks to geekzspot for the 1087)
if(totalMin > 1087) {
e.range.setBackgroundColor('yellow');
} else {
e.range.setBackgroundColor('red');
}
}
UPDATE
This code will set only the back ground color of the timestamp:
function onFormSubmit(e) {
// get active spreadsheet and sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
// get data and make it a 1D array
var time = e.range.getValues()[0];
// get row index
var row = e.range.getRowIndex();
// get the minutes and hours
var minutes = time[0].getMinutes(), hours = time[0].getHours();
// calculate the total amount of minutes
var totalMin = (hours * 60) + minutes;
// set range to timestamp
var range = sh.setActiveCell("A"+row);
// do the logic (thanks to geekzspot for the 1087)
if(totalMin > 1087) {
range.setBackgroundColor('yellow');
} else {
range.setBackgroundColor('red');
}
}
Add this script, via the script editor, to your spreadsheet and set the trigger:
Here's the form: Test For Web Applications and here's the sheet
Use AVERAGEIF :
=AVERAGEIF(B4:B6,B10,C4:C6)
B4:B6 - column Name C4:C6 - column Strength (for exmpl.) B10 - specific name
This function is available only in New Google SpreadSheet: https://support.google.com/drive/answer/3256529
Best Answer
Yes you can use regexmatch with the "custom formula" option on the data validation menu item.
Create a list of all the words you want to reject and join them with the
|as your delimiter, which represents "or" in regex.I recommend leaving them lower case and then within the formula we will also lowercase the text to make sure we catch both cases/scenarios, but for example I did the validation on all of column H:
The words I chose to reject were
thenandtext- if you choose not to reject the input but just flag it it will look like the image below, alternatively you can choose "reject input" and that will literally reject any cell they try to enter that contains your offensive strings.PS if you choose reject, it wont ever even get entered in like it does in the image - i just wasnt able to capture an event NOT occurring in a photograph lol