Reputation: 171
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
Reputation: 27390
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
.
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());
}
}
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.
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()
:
Upvotes: 1