Any one can help how to do a scripts for auto lock / protect range/row by date

I have try but this script only lock a cell only ( cell M:M )

a = SpreadsheetApp.getActiveSpreadsheet();
  var s = a.getSheetByName('ExportPlan');
  var values = s.getRange("M1:M").getValues();
  var row, col, len, index; 

function mylockranges() {
  var myDate = new Date();
  myDate.setDate(myDate.getDate() - 3); //myDate is the date before 3 days of today

  //First range to lock
  var row = 1;
  var col = 1;

  for (row = values.length-1; row >=3 ; row--) {
    // Only unprotect rows with dates from today - 3 on
  if (typeof values[row][0] == 'object' && values[row][0] < myDate) {
    lockRange(row, col);


function lockRange(row, col){
  row = row+1 ; 
  var range = s.getRange(row, col, 1, 25);

  // Create protection object. Set description, anything you like.
  var protection = range.protect().setDescription('Protected, row ' + row);
  • You need either 3 or 4 parameters for a range. If it's only 1 column, then you only need 3 parameters. If the range will be multiple rows and multiple columns, you need 4 parameters. Start Row, Start Column, Number of Rows, Number of Columns The function being used to set the range only has 2 parameters: lockRange(row, col); You need to add the number of rows in the range, and the number of columns in the range. The range is already using 4 parameters var range = s.getRange(row, col, 1, 25); But the third and forth parameter are "hard coded" to constant values of 1 and 25. – Alan Wells Mar 13 '16 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.