google-apps-scriptgoogle-sheetsgoogle-sheets-macros

How to automatically switch from view mode to edit mode and vice-versa

I have several sheets as subjects in my Google Spreadsheet where attendance are recorded from an android app. When I select View mode to Edit mode, attendance can be recorded.

By default I want to keep it in view mode so that students can NOT submit attendance outside the time given by me as a owner of the sheets or the class teachers (editors).

Please note that my sheet users are:

All (owner, editors and students) are using university domain specific mail for accessing sheets.

It is also a tedious job for a teacher to enable Edit mode during a class for attendance and then after completing it, again go back to view mode everyday. What I want is to, create a submenu using onOpen() function to automate the whole process so that teachers can do it easily.

The Codes are as follows:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Attendance Task')
    .addItem('Refresh Sheet', 'refreshSheet')
    .addToUi();
}

//Refresh Sheet for setting Editor mode to students and taking their attendance from mobile app
function refreshSheet() { 

  var spreadsheet = SpreadsheetApp.getActive();

  //StudentList is a student information sheet where in Column F all the emails of students kept.  
  var sheet = spreadsheet.getSheetByName('StudentList');

  //Need to bring all students emails from Column F of StudentList sheet to students array.  
  var students = [];

  var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ss = SpreadsheetApp.openById(ssId);
  for (var i=0; i<students.length; i++){
    //Checking the students are in Editor or Viewer Mode
    if(students[0] || students[1] || students[2] in Viewer Mode){
     ss.addEditor(students[i]); 
    }  
  }

  //Now start a timer and after 10 or 20 minutes all students should be removed automatically from Editor to Viewer Mode.    
     ss.removeEditor(students[i]);

}
Solution

  • You can create a time-based installable trigger in Apps Script which will change the permissions for the students to editors in order for them to record the attendance and afterwards change it back to viewers.

    Assuming the students email addresses are stored in the students array, the snippet below will be able to fulfill your task:

    Snippet

    
function studentsToEditors() {
  var students = [];
  var ss = SpreadsheetApp.openById("ID_OF_THE_SS");
  for (var i=0; i<students.length; i++)
    ss.addEditor(students[i]);
}

function studentsToViewers() {
  var students = [];
  var ss = SpreadsheetApp.openById("ID_OF_THE_SS");
   for (var i=0; i<students.length; i++)
     ss.removeEditor(students[i])
}

function createTriggers() {
  ScriptApp.newTrigger('studentsToEditors')
      .timeBased()
      .atHour(9)
      .create();

  ScriptApp.newTrigger('studentsToViewers')
      .timeBased()
      .atHour(9)
      .nearMinute(30)
      .create();
}

    Explanation

    The above code is composed of three functions:

    In this way, the students will be added as editors in order to record their attendance and afterwards removed.

    Note

    The nearMinute(minute) method used for the studentsToViewers specifies the minute at which the trigger runs (plus or minus 15 minutes).

    Update

    Method 1

    If you own a GSuite account which allows you to execute a script for longer than the 6 minutes/script execution time, you can use the below method.

    You can simply add Utilities.sleep(300000); to your code three times in order to monitor the 15 minutes needed.

    Utilities.sleep(300000);
Utilities.sleep(300000);
Utilities.sleep(300000);
for (var i=0; i<students.length; i++) {
   ss.removeEditor(students[i]);
}

    Method 2

    If the quota does not allow you to use the above method, you can create a function which specifically removes the students editors and afterwards attaches a time based trigger to it.

    Assuming that you remove the students in a removeStudents() function, you can create a trigger like this:

    function createTrigger(){
   ScriptApp.newTrigger("myFunction")
      .timeBased()
      .after(900000)
      .create();
}

    If needed, you can also remove the above trigger, depending on your needs/quotas.

    Also, please bear in mind that you can configure the time-based trigger to fulfill your needs by checking this documentation here and choosing the most appropriate options for your use-case.

    Reference