Set Range protection weekly time-based trigger

0
votes

I am trying to write a script that will protect a varying range in sheets weekly. I have figured out the weekly locking and trigger to initiate it. However, Each week needs a new range. If week 1 was;

var range = sheet.getRange(4, 5, 5, 6);

week 2 would need;

var range = sheet.getRange(10, 5, 5, 6);

and so on. So only my Row input will need changing based off the week the trigger is actived.

Complete Script is currently as follows:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Multiple'); //INSERT SHEET NAME HERE


function lockRange(row, col){
  var range = sheet.getRange(10, 5, 5, 6);

  // Create protection object. Set description, anything you like.
  var protection = range.protect().setDescription('Date1');

 // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
 // permission comes from a group, the script will throw an exception upon removing the group.
 var me = Session.getEffectiveUser();
 protection.addEditor(me);
 protection.removeEditors(protection.getEditors());
 if (protection.canDomainEdit()) {
   protection.setDomainEdit(false);
 }
}
1
javascriptdatetimegoogle-apps-scriptgoogle-sheetstriggers
Welcome to Stack Overflow. It looks that you are looking for help on the implementation of an algorithm in Google Apps Script. Please add a brief description of your search/research efforts (as is suggested in How to Ask) regarding how set a row number based on the week (tip: look for JavaScript questions ) - Rubén
You should set a datum somewhere for the start of the weekly range (say Monday 1 June 2020), then calculate how many whole weeks have passed since then to calculate the first parameter passed to getRange. - RobG

1 Answers

0
votes

Answer:

You can store the next week in the Script's properties using PropertiesService.

Code Modification:

function lockRange(row, col){
  var week = PropertiesService.getScriptProperties().getProperty("week");

  if (!week) {
    week = PropertiesService.getScriptProperties().setProperty("week", 4)
  }
  else {
    week = PropertiesService.getScriptProperties().setProperty("week", parseInt(week) + 6)
  }  

  week = PropertiesService.getScriptProperties().getProperty("week");

  var range = sheet.getRange(parseInt(week), 5, 5, 6);

  // continue your code...
}

function clearProperties() {
  // run this function ONCE to clear any properties that are being stored in
  // the script from testing/previous runs.
  PropertiesService.getScriptProperties().deleteAllProperties();
}

References: