Protecting your data

While we are taking care of data privacy, let's have a quick peek at data protection using Google Apps Script.

Google has introduced this ability quite recently and it opens a few interesting perspectives.

The following is an example of a script that protects a sheet after a user has added a value in a particular cell:

function myFunction(e) {
  var sheetIndex = e.source.getSheets().length;// to know how many sheets we already have
  var sourceValues = e.source.getActiveSheet().getDataRange().getValues();//get all the data from this sheet
  var cell = e.source.getActiveRange().getA1Notation();//get A1 notation for comfort of use
  Logger.log('SheetName:'+e.source.getActiveSheet().getSheetName()+'  user:'+Session.getActiveUser());
  if(cell=='A1' && e.source.getSheetName()=='Sheet1'){// execute only if cell A1 and Sheet1, else do nothing
    var copy = e.source.insertSheet('SheetCopy_'+sheetIndex,sheetIndex);//create a copy at the last index
    copy.getRange(1,1,sourceValues.length,sourceValues[0].length).setValues(sourceValues);//clone sheet1 values only, no format
    var permissions = copy.getSheetProtection();
    permissions.removeUser(Session.getActiveUser());//who is editing ? remove him from editors (does not work for owner of course)
    permissions.setProtected(true);
    copy.setSheetProtection(permissions);//protect the copy, the original editor of the sheet can't change it anymore
    e.source.getSheetByName('Sheet1').activate();//reset the browser to Sheet 1, not on the copy
  } 
}

The previous code is pretty funny as it will make a copy of the active sheet on certain conditions and prevent even the user from modify it. One could imagine following such a process as signing a document and preventing its modification thereafter.

It shows that any workflow can be automated quite easily.

Also note that this code does not work with new spreadsheets for now (as of January 2014, the new version of spreadsheet (which is an optional update) doesn't support the onEdit trigger, so this function can't be implemented) and must be tested on a shared document; the actual user must NOT be the owner of the document, but an editor (perhaps with shared edit permissions).

It is indeed not possible to restrict the sheet access for the sheet owner themselves (and that's a good thing!).