google-apps-script - 使用 Arrayformula 在 Google 表格中为团队创建动态任务列表

Question

我试图解决的问题：

我正在尝试创建一个动态任务列表。

截至目前，我正在使用这张表。

---

在这个任务列表中，我有主要任务，根据标签分配给用户。

示例标签：

• A = 阿曼达的作品
• B = 鲍勃的作品
• C = 坎迪斯的作品”

---

每个任务可以有多个子任务，这些子任务也可以有标签。

如果子任务没有明确设置标签，则应使用主任务的标签。

这是通过“隐藏助手列”完成的，如下所示：

---

然后，我希望每个标签都有单独的工作表（在同一个电子表格上）。

这些工作表需要有我的主要任务列表中的相应任务。

重要提示：如果子任务与主任务的标签不同，则只有该子任务应显示在标签列表中，不包括主任务。

---

我想要完成的事情：

我想为每个标签创建动态工作表，并为其分配正确的任务。

到目前为止我已经尝试过：

隐藏助手的自定义公式：

没有按预期工作。

=ARRAYFORMULA(
  IF(len(B3:B)=1,B3:B,
    if(len(E3:E)=0,A2:A,
)))

Apps 脚本onEdit例程

表现不尽如人意。

function SetMarker(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(""Life"");

  var lastRow = sheet.getLastRow();
  Logger.log("The Last Row # is "+lastRow);
  var startCell = 3; //Cell to begin work.
  Logger.log(" The Starting Cell # is "+startCell);
  var cell ;
  Logger.log(cell);

  for (var i = startCell; i <= lastRow; i++){
    var j = i -1;
    cell = sheet.getRange(i,1);
    Logger.log(""The Cell is ""+cell);
    cell.setFormula("=IF(F"+i+"=\"W\",\"W\",IF(B"+i+"=\"W\",\"W\",IF(E"+i+",\"\",IF(F""+j+"=\""W\"",\"\",A"+j+"))))");
  }
}

标签： google-apps-scriptgoogle-sheetsarray-formulas

Answer 1

我分析了您的问题并提出了一个可行的解决方案，该解决方案不会太慢或太复杂而无法更改。

解决方案

组件

为了获得满意的结果，我决定使用 Apps Script。

我的测试电子表格是这样的：

标签（您可以隐藏此工作表）

模板表（您可以隐藏此表）

注意那里的“”关键字应该被脚本替换

我冒昧地将其过滤掉，以避免与额外的列混淆。

主任务列表

编码：

var spreadsheetId = "1owILF2cVHr5bqBa5AYNpIM9_B7JP2ZFjiKZSH6itn5k";
var labelSheetName = "Labels";
var mainTaskListName = "Master Task List";
var templateSheetName = "Template Sheet";

/**
Returns a list of Labels in the format:
[0] - Label 
[1] - Sheet Name
@returns array<string> list of labels with corresponding sheet name.
*/
function getLabels() {
  var values = SpreadsheetApp.openById(spreadsheetId).getSheetByName(labelSheetName).getDataRange().getValues();
  values.shift();
  return values;
}

/**
Returns a list of tasks from the main task list in the format:
[0] - Assigned Label 
[1] - Task Number
[2] - Task name
[3] - Sub-task name
@returns array<string> list of task with their completed information.
*/
function readTasks() {
  var values = SpreadsheetApp.openById(spreadsheetId).getSheetByName(mainTaskListName).getDataRange().getValues();
  values.shift();
  
  var currentTaskLabel = null;
  for (var i=0; i<values.length; i++) {
    //Does this row have a defined label?
    if (values[i][0]==null || values[i][0] == "") {
      //No. Is this a Main task?
      if (values[i][2]!=null && values[i][2] != "") {
        //Main task, set the currentTaskLabel
        currentTaskLabel = values[i][0];
      } else { //No, use the currentTaskLabel.
        values[i][0] = currentTaskLabel;
      }
    } else {
      //Yes, use this label for the data.
      //Is this a main Taks or a sub-task?
      if (values[i][2]!=null && values[i][2] != "") {
        //Main task, set the currentTaskLabel
        currentTaskLabel = values[i][0];
      } //Sub-Task, do nothing
    }
  }
  
  Logger.log(values);
  return values;
}

/**
Removes existing sheets for labels and creates new ones with correct structure.
*/
function prepareSheet() {
  //Find all the labels
  var labelsSheets = getLabels().map(function(label) {return label[1]});
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  for (var i=0; i<labelsSheets.length; i++) {
    //Remove the sheets
    if (spreadsheet.getSheetByName(labelsSheets[i]) != null) spreadsheet.deleteSheet(spreadsheet.getSheetByName(labelsSheets[i]));
    //Create new sheets based of template
    var newSheet = spreadsheet.getSheetByName(templateSheetName).copyTo(spreadsheet).setName(labelsSheets[i]);
    newSheet.createTextFinder("<SHEET NAME>").replaceAllWith(labelsSheets[i]);
  }
}

/**
Inserts the tasks from the task list into the correct sheet
@param {object} the label representation based on `getLabels`
@param {object} the task list representation based on `readTasks`
*/
function insertTasks(labelRepresentation, fullTaskList) {
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(labelRepresentation[1]);
  
  var label = labelRepresentation[0];
  
  var filteredTaskList = fullTaskList.filter(function(task) {return task[0]==label});
  filteredTaskList = filteredTaskList.map(function(task) {return [task[1],task[2],task[3]]}); //Remove the label from the output
  
  for (var i=0; i<filteredTaskList.length; i++) {
      var newRow = sheet.appendRow(filteredTaskList[i]).getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn());
      //If this is a main task, paint the row background
      if (filteredTaskList[i][0]!=null && filteredTaskList[i][0]!="") {
        newRow.setBackground("#B7FFCD");
      } else {
        newRow.setBackground("#FFFFFF");
      }
  }
}

/**
Insert all tasks into the corresponding sheets, after preparing them.
*/
function insertAllTask() {
  var labels = getLabels();
  var tasks = readTasks();
  
  prepareSheet();
  
  for (var i=0; i<labels.length; i++) {
    insertTasks(labels[i], tasks);
  }
}

全部设置：

为了将这一切联系在一起，我创建了一个基于时间的触发器，每 5 分钟运行一次此函数。

这样，当 Apps 脚本处理您的数据时，您就不会停止。

未来的改进

• 主任务列表上的标签列可以是基于标签表中的值的列表。