CODEWITHSUNDEEP
validationgoogle-apps-scriptgoogle-sheets
Mastropiero
Mastropiero

Reputation: 13

Check programmatically if a value is compliant with a datavalidation rule

I am using data validation rules on a Google Spreadsheet.

In my scenario, I need users to entry only valid values. I use the 'Reject input' to force them to write only validated content.

However, the 'Reject input' option works for manually entried data only, but it does not work if the user pastes content into the cell from a different source (e.g. a MS Excel document). In that case, a warning is still shown but the invalid value is written.

In other words, I need the 'Reject input' option to work also with pasted content. OR... another approach would be to programmatically check the validity of the value according the Datavalidation rule for that cell.

Any ideas?

Thank you in advance.

Upvotes: 1

Views: 2235

Answers (2)

Niccolo
Niccolo

Reputation: 815

I am posting another answer because this is a programmatic solution.

It has a lot of problems and is pretty slow but I am demonstrating the process not the efficiency of the code.

It is slow. It will be possible to make this run faster.

It assumes that a single cell is pasted.

It does not cater for inserting of rows or columns.

This is what I noticed

The onEdit(event) has certain properties that are accessible. I could not be sure I got a full listing and one would be appreciated. Look at the Spreadsheet Edit Events section here.

The property of interest is "e.value".

I noticed that if you typed into a cell e.value = "value types" but if you pasted or Paste->special then e.value = undefined. This is also true for if you delete a cell content, I am not sure of other cases.

This is a solution

Here is a spreadsheet and script that detects if the user has typed, pasted or deleted into a specific cell. It also detects a user select from Data validation.

Type, paste or delete into the gold cell C3 or select the dropdown green cell C4.

You will need to request access, if you can't wait just copy & paste the code, set a trigger and play with it.

Example

Code

Set the trigger onEdit() to call this or rename it to onEdit(event)

You can attach it to a blank sheet and it will write to cells(5,3) and (6,3).

function detectPaste(event) {
  var sheet      = event.source.getActiveSheet();   
  var input_type =" ";

  if (event.value == undefined) { // paste has occured
    var activecell = event.source.getActiveCell();  

    if (activecell.getValue() == "") {  // either delete or paste of empty cell
      sheet.getRange(5,3).setValue("What a good User you are!");
      sheet.getRange(6,3).setValue(event.value);    
      input_type = "delete"
    }
    else {
      activecell.setValue("");
      sheet.getRange(5,3).setValue("You pasted so I removed it");
      sheet.getRange(6,3).setValue(event.value);
      input_type = "paste";
    }
  }
  else { // valid input
    sheet.getRange(5,3).setValue("What a good User you are!");
    sheet.getRange(6,3).setValue(event.value);    
    input_type = "type or select";
  }
  SpreadsheetApp.getActiveSpreadsheet().toast('Input type = ' + input_type, 'User Input detected ', 3);
}

Upvotes: 1

Niccolo
Niccolo

Reputation: 815

I had a little play with this.

I had inconsistent behavior from google. On occasion when I ctrl-c and ctrl-p, the target cell lost its data validation!

To do this programmatically

  1. Write myfunction(e)
  2. Set it to run when the spreadsheet is edited, do this by Resources>Current Project's Triggers
  3. Query e to see what has happened.

Use the following to gather parameters 

var sheet      = e.source.getActiveSheet(); 
var sheetname  = sheet.getSheetName();
var a_range    = sheet.getActiveRange(); 
var activecell = e.source.getActiveCell();  
var col        = activecell.getColumn(); 
var row        = activecell.getRow();

You may wish to check a_range to make sure they have not copied and pasted multiple cells.

Find out if the edit happened in an area that you have data validated;

if (sheetname == "mySheet") {
  // checking they have not just cleared the cell
  if (col == # && activecell.getValue() != "") {
    THIS IS WHERE YOU CHECK THE activecell.getValue() AGAINST YOUR DATA VALIDATION
    AND USE 
    activecell.setValue("") ;
    to clear the cell if you want to reject the value
  }
}

The obvious problem with this is that it is essentially repeating programmatically what the data validation should already be doing.

So you have to keep two sets of validation rules synchronized. You could just delete the in sheet data validation but I find that useful for providing the user feedback. Also is the data validation you are using provides content it is practical to leave it in place.

It would be great if there was a way of detecting that ctrl-p had been used or one of the paste-special options and only run the script in those cases. I would really like to know the answer to that. Can't find anything to help you there.

Note also that if someone inserts a row, this will not trigger any data validation and the onEdit() trigger will not detect it. It only works when the sheet is edited and by this I think it means there is a content change. onChange() should detect insertion, it is described as;

Specifies a trigger that will fire when the spreadsheet's content or structure is changed.

Upvotes: 1

Related Questions