Google-sheets – Protect spreadsheet against duplicate data and sheet addition/deletion

google sheetsgoogle-apps-scriptgoogle-sheets-data-validation

There are some people that love to troll and spam my Google spreadsheet with useless info. Google Sheet lacks granular permission restriction.

I need to prevent duplicate data from being submitted. I can prevent manual duplicate data entry by setting Data Validation with a custom formula:

=countif(A$1:A1,A1)=1

However, this only prevents manual data entry. If users fill the cells down, or if they copy and paste data into multiple cells, Data Validation won't work.

I also need to prevent them from adding new sheets or deleting an entire sheet.

How can I achieve this?

Best Answer

you can lock down the spreadsheet:
you can protect the sheets
if you need users to be able to make input then create a new spreadsheet for them with some instructions how to behave and then filter out gathered data with QUERY or FILTER and import them to your spreadsheet with IMPORTRANGE from where all the email action will happen

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Best Answer

Related Solutions

Google Sheets – Protect Formatting While Allowing Data Changes

Google Sheets – How to Split Multi-Line Cells into New Rows

Code

Explanation

Screenshot

Example

Related Topic