How can I move to next range using macros?

Question

I have a sample macros code that I got from recording. So, the code protects the 'C' & 'D' columns whenever I run it.

BUT, What I want is when I run this code second time, it protects the next 2 columns i.e. 'E' and 'F', and so on.

How can I do this.?

Also, Is there a way that this code runs automatically at a set time of a day each day, for a month, so I don't have to manually run the code everyday.

/** @OnlyCurrentDoc */

function DataReWriteProtection() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C:D').activate();
  var protection = spreadsheet.getRange('C:D').protect();
};

Answer 1

This code may give you a start for developing your own code.

The script should be installed as a Installable, Time Driven, Trigger. You should choose the frequency and execution time that best suits you.

function so5577363801() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // assumes that cell A1 contains the column value (as an integer) for the next column to be protected
  // assumes that two columns (side-by-side) are to be protected
  var colNumRange = sheet.getRange("A1");
  var colNum = colNumRange.getValue();
  var startcol = colNum;
  var endcol = colNum+1;

  // convert column numbers to letters
  var startletter = columnToLetter(startcol);
  var endletter = columnToLetter(endcol);
  // Logger.log("DEBUG: Cell A1: "+colNum+", start column: "+startcol+", endcol: "+endcol+", start letter: "+startletter+", end letter: "+endletter);//DEBUG

  // define the range to be protected
  var range = sheet.getRange(startletter+":"+endletter);
  // Logger.log("DEBUG: range = "+range.getA1Notation());//DEBUG
  var rangename = '"'+startletter+":"+endletter+'"';
  //Logger.log("DEBUG: range name: "+rangename);//DEBUG

  // protect the range, and assign a description
  var protection = sheet.getRange(startletter+":"+endletter).protect();
  protection.setDescription(rangename).setWarningOnly(true);

  // update CellA1 with the column number to be used next time.
  var newcol = colNum+2;
  colNumRange.setValue(newcol); 
}

function columnToLetter(column){
  var temp, letter = '';
  while (column > 0){
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}