CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Khaurum
Khaurum

Reputation: 33

protecting cells with google apps script (with dynamic button or command)

does anyone know if it is possible to make a google apps script for a google spreadsheet, that protects particular cells if a given situation occurs? Fx. if an X occurs in "A1", then "A2" should be protected?

Upvotes: 3

Views: 6938

Answers (5)

Wicket
Wicket

Reputation: 38131

The "feature request" referred by Henrique G. Abreu was fixed. Reference comment #165

Today, we have launched the ability to programmatically create and manipulate protected ranges and protected sheets with Apps Script.

With the new Protection class in the Spreadsheet service, your scripts can touch every aspect of range or sheet protection, just like in the new UI. (The older PageProtection class, which had more limited features, will be deprecated, but will stick around in case you need to work with older spreadsheets. The new Protection class only applies to the newer version of Sheets.)

You can find more details here:

http://googledevelopers.blogspot.com/2015/02/control-protected-ranges-and-sheets-in.html

https://developers.google.com/apps-script/releases/#february_2015

https://developers.google.com/apps-script/reference/spreadsheet/protection

Upvotes: 1

ymz
ymz

Reputation: 6916

i know that this is an old question and still - google "feture request" (posted by Henrique Abreu) has no progrees till now (end of 2014)

my hunch: this kind of feature would be a workaround for google forms (http://www.google.com/forms/about) when users are requested to edit cells that getting locked afterwards (by a criteria or another kind of logic)

i recently came accross a similar situation at my workplace, and my solution is very simple - make a form and share it with relevant users. the form fields will enable users to fill up the data they should be locked. that form will end up generating a sheet of information (updated online each time a form is filled). using "IMPORTRANGE" (https://support.google.com/docs/answer/3093340?hl=en) from another sheet will result in querying the "locked" information inside your relevant sheet. since the data is inside another sheet and is represented as a formula - it cannot be edited (at least not as a data)

i really considering a true workaround which can actually "lock" a cell (by replacing its value to the original by a script).. if there is such a request

Upvotes: 0

Bubz
Bubz

Reputation: 180

I have a work-around, code follows

function protect_named_range(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target_sheet = ss.getSheetByName('Sheet1');//change "sheet1" for the sheet your range is in
  var array_range1 = target_sheet.getRange("A1:A1");
  var array_range2 = target_sheet.getRange("A2:A2");
  var range_test = array_range1.getValue();// or getFormula or what ever you want to test
  if(range_test == "correct answer"){ //if cell A2 = "correct answer" then cell B2 becomes a named range called "named_range"       
    ss.setNamedRange("named_range", array_range2);
   SpreadsheetApp.setActiveSheet(target_sheet);
   var sheet = SpreadsheetApp.getActiveSheet();
   var permissions = sheet.getSheetProtection();
   permissions.setProtected(true);
   target_sheet.setSheetProtection(permissions);
  }
 }

Sorry I have been away for awhile. This is the basic code I would use, however it doesnt actually protect the range "B2" if cell A2 = "correct answer. instead it protects the sheet named "sheet1" if cell A2 = "correct answer" so that only the spreadsheet owner may edit anything on the sheet.

I know this isnt exactly what your after, but I hope it can help.

Upvotes: -2

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

There's no API to manage cell protection yet. This feature request asks for this. You may want to star it to keep track of updates and kind of vote for it.

Upvotes: 1

makio
makio

Reputation: 66

It is not. I don't believe that's the way that cell protection works. A cell is manually protected, or not protected at all.

There is no cell function or Script Object that enables that functionality.

Upvotes: 1

Related Questions