Tamjid Taha
Tamjid Taha

Reputation: 171

How to add users as editors to a spreadsheet in one function and remove them in another function?

I want to add a number of users (students) as editors to a spreadsheet in function refreshSheet() and after 5 minutes I want to remove them in another function onePeriod(). The code works fine if I add and remove the users in the same function but does not work in two different functions.

Please note that I have 2-5 permanent editors of the spreadsheet that I don't want to delete. Only those students need to remove which I added in the refreshSheet() function.

My codes are:

function refreshSheet(){
  var sheet1 = spreadsheet.getSheetByName('StudentList');
  
  //Need to bring all students emails from Column of StudentList sheet to students array. 
  var data = sheet1.getLastRow();
  var students = [];
  var students = sheet1.getRange(2, 6, data).getValues();  
  
  var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ss = SpreadsheetApp.openById(ssId);
  for (var i=0; i<students.length; i++){ // you are looping through rows and selecting the 1st and only column index
     if (students[i][0] !== '') ss.addEditor(students[i][0].toString()); 
  }  

  //Utilities.sleep(300000); Not the problem at all
}

// Removing all the student editors from the spreadsheet
function onePeriod(){
  for (var i=0; i<students.length; i++) {
      if (students[i][0] !== '') ss.removeEditor(students[i][0].toString());
  }
}   

Upvotes: 0

Views: 59

Answers (1)

Marios
Marios

Reputation: 27390

Modification 1:

The issue in your code is that you haven't defined students in the onePeriod function:

function onePeriod(){
  for (var i=0; i<students.length; i++) {
      if (students[i][0] !== '') ss.removeEditor(students[i][0].toString());
  }
} 

Since students is scoped in another function, it can't be used within onePeriod or any other function. You can either define again students within onePeriod as I do in the solution below, or define it as a global variable so it can be used in both refreshSheet and onePeriod.

Modification 2:

I have 2-5 permanent editors of the spreadsheet that I don't want to delete.

Then define an array of the students you don't want to delete and exclude them from the if condition:

function onePeriod(){
    var exclude_students = ['name1','name2','name3']; // put students you don't want to delete as editors
    var sheet1 = spreadsheet.getSheetByName('StudentList');
    var data = sheet1.getLastRow();
    var students = [];
    var students = sheet1.getRange(2, 6, data).getValues();   
  for (var i=0; i<students.length; i++) {
      if (students[i][0] !== '' && !exclude_students.includes(students[i][0])) ss.removeEditor(students[i][0].toString());
  }
} 

Modification 3 similar to ziganotschka`s answer:

Instead of pausing your code, you can create a scheduled trigger for onePeriod to be executed after 5 minutes:

ScriptApp.newTrigger("onePeriod")
 .timeBased()
 .after(5 * 60 * 1000) // create trigger to execute onePeriod after 5 minutes
 .create();  

I also added a code to delete any triggers associated with this function, so you clear the previous triggers before you create a new one.

Solution:

function refreshSheet(){
  var sheet1 = spreadsheet.getSheetByName('StudentList');
  
  //Need to bring all students emails from Column of StudentList sheet to students array. 
  var data = sheet1.getLastRow();
  var students = [];
  var students = sheet1.getRange(2, 6, data).getValues();  
  
  var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ss = SpreadsheetApp.openById(ssId);
  for (var i=0; i<students.length; i++){ // you are looping through rows and selecting the 1st and only column index
     if (students[i][0] !== '') ss.addEditor(students[i][0].toString()); 
  }  
  createTrigger();
}

// Removing all the student editors from the spreadsheet except for some
function onePeriod(){
    var exclude_students = ['name1','name2','name3']; // put students you don't want to delete as editors
    var sheet1 = spreadsheet.getSheetByName('StudentList');
    var data = sheet1.getLastRow();
    var students = [];
    var students = sheet1.getRange(2, 6, data).getValues();   
  for (var i=0; i<students.length; i++) {
      if (students[i][0] !== '' && !exclude_students.includes(students[i][0])) ss.removeEditor(students[i][0].toString());
  }
} 


function createTrigger() {
  Trigger_delete();
  ScriptApp.newTrigger("onePeriod")
  .timeBased()
  .after(5 * 60 * 1000) // create trigger to execute onePeriod after 5 minutes
  .create();  
}


function Trigger_delete(){
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
  if (triggers[i].getHandlerFunction() == "onePeriod") {
    ScriptApp.deleteTrigger(triggers[i]);
  }
} 
}

Note:

You only need to execute the function refreshSheet():

enter image description here

Upvotes: 1

Related Questions