如何自动从查看模式切换到编辑模式，反之亦然 [英] How to automatically switch from view mode to edit mode and vice-versa

问题描述

我在Google Spreadsheet中有几张表作为主题，这些记录是通过android应用记录的.当我选择查看模式为编辑模式时，可以记录出席情况.

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.

对于老师来说，在上课时启用编辑模式也是一件很乏味的工作，然后在完成学习后每天再次返回查看模式.我想做的是，使用onOpen()函数创建一个子菜单来使整个过程自动化，以便教师可以轻松地做到这一点.

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.

代码如下:

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]);

}

推荐答案

您可以在Apps脚本中创建基于时间的可安装触发器，该触发器会将学生的权限更改为编辑者，以便他们记录出席者，然后将其更改回查看者.

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.

假设学生的电子邮件地址存储在students数组中，下面的代码段将能够完成您的任务:

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

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:

• studentsToEditors，用于将每个学生作为编辑者添加到电子表格中；

• studentsToEditors which is used to add each student as an editor to the spreadsheet;

studentsToViewers，用于删除每个学生作为电子表格的编辑者；

studentsToViewers which is used to remove each student as an editor to the spreadsheet;

creteTriggers用于创建两个时间驱动的触发器:

creteTriggers which is used to create two time-driven triggers:

• 一个将运行在大约9并连接到studentsToEditors函数的

将在大约9:30(+/- 15分钟)运行并附加到studentsToViewers函数；

one which will run at approximately 9:30 (+/- 15 minutes) and is attached to the studentsToViewers function;

通过这种方式，学生将被添加为编辑者，以记录其出勤情况，然后被删除.

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

nearMinute(minute) studentsToViewers所使用的a>方法指定触发器运行的分钟(正负15分钟).

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

方法1

如果您拥有一个GSuite帐户，该帐户允许您执行脚本的时间超过

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.

您只需在代码中添加Utilities.sleep(300000);三次，即可监视所需的15分钟.

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]);
}

方法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.

假设您在removeStudents()函数中删除了学生，则可以创建这样的触发器:

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.

• 可安装触发器Apps脚本;

ClockTriggerBuilder Apps脚本 ;

Class Utilities Apps脚本;

配额应用脚本；

这篇关于如何自动从查看模式切换到编辑模式，反之亦然的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！