CODEWITHSUNDEEP
javascriptregexif-statementgoogle-apps-scriptgoogle-sheets
user13854773
user13854773

Reputation: 43

Google App Script check if the edited cell contains space

The following code is not working when I put a value with space in editedCellValue

function onEdit(e) {

    var editedCellRange = 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test").getActiveRange();
    var editedCellValue = 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test").getActiveRange().getValue;
    if (RegExp(editedCellValue).test("\s")==true){editedCellRange.setValue("contains space")}
    else 
    {editedCellRange.setValue("ELSE IF")}
}

Please help with why this is not working.

thanks.

Upvotes: 4

Views: 541

Answers (1)

Tanaike
Tanaike

Reputation: 201503

Modification points:

  • If you want to use test, please modify to (/\s/g).test(editedCellValue).
  • For example, you can also use includes like editedCellValue.includes(" "). And also, indexOf can be used.
  • In your situation, I think that the event object can be also used.
  • In your script, var editedCellValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test").getActiveRange().getValue; doesn't retrieve the value because getValue is not run. Please add () like getValue().
  • You can declare one SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test").

When above points are reflected to your script, it becomes as follows.

Modified script:

function onEdit(e) {
  var range = e.range;
  if (range.getSheet().getSheetName().toLowerCase() != "test") return;
  var editedCellValue = range.getValue();
  if ((/\s/g).test(editedCellValue)) {  // or if (editedCellValue.includes(" ")) {
    range.setValue("contains space")
  } else {
    range.setValue("ELSE IF")
  }
}
  • In this case, when you edit the cell, onEdit is automatically run by the OnEdit trigger. And, in your script, the edited cell is overwritten. Please be careful this.

Note:

  • If you want to directly run the function with the script editor, please modify above script as follows.

    • From

        var range = e.range;
  if (range.getSheet().getSheetName().toLowerCase() != "test") return;

    • To

        var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test").getActiveRange();

References:

Upvotes: 1

Related Questions