Google表格/脚本OnEdit可以更改两个表格 [英] Google Sheets/Script OnEdit to Change Two Sheets

问题描述

我一直在尝试制作锻炼日历的脚本.这个想法是在单独的选项卡上有两个版本.一个是日期/日期和锻炼的列表，第二个是传统的日历视图.

I've been trying to make a script for a workout calendar. The idea is to have two versions on separate tabs; one is a list of the days/dates and the workout, and the second is a traditional calendar view.

我遇到的挑战是扩展日历的选项，如果选中了复选框，则取消隐藏隐藏的行.对于时间表"，视图，我已经能够使用它，但是我无法在日历视图的单独工作表上使用它.我希望一个onEdit动作可以更改两个工作表.

The challenge I'm running into is the option to extend the calendar, un-hiding hidden rows if a checkbox is selected. For the "schedule" view, I've been able to get it to work, but I can't get it to work on the separate sheet for the calendar view. I'd like the one onEdit action to change both sheets, if possible.

这是我目前所在的位置:计划选项卡上H2中的复选框是触发器.如果未选中，则将隐藏97-124行，并在选中该框时显示.以下是成功的代码，基于我对

This is where I am currently: The checkbox in H2 on the Schedule Tab is the trigger. If unchecked, rows 97-124 are hidden, and shown if the box is checked. The successful code is below, based on what I've modified from this stackoverflow question.

function onEdit(e) {
  var sheet = e.range.getSheet();
  if (sheet.getName() !== 'Schedule') return; 
  if (e.range.getColumn() !== 8) return;
  if (e.value == 'TRUE') {
    switch (e.range.getRow()) {
      case 2:
        sheet.showRows(97,28);
        break;
    }
  } else {
    switch (e.range.getRow()) {
      case 2:
        sheet.hideRows(97,28);
        break;
    }
  }
};

但是我无法影响第二个日历"工作表.默认情况下，可以看到三个月，然后根据日期如何显示隐藏的行，再显示1-2个月.C1单元格中有一个公式可以反映月份数(行1最终将被隐藏)，但是我一直无法找到一种方法来使代码触发公式的结果.使用此链接中的代码，我可以通过手动输入要显示的月份数来获得所需的结果，但这是我要避免的额外步骤.(我知道我可以通过将计算出的隐藏行的日期移动到工作表的顶部来简化此操作，以获得更简洁的showRows/hideRows；在我决定尝试使工作表自动化之前就完成了布局)./p>

But I can't get it to affect the second Calendar sheet. The default there would be having three months visible, then showing hidden rows for an additional 1-2 months based on how the dates fall. There's a formula in cell C1 that reflects the number of months (Row 1 would ultimately be hidden), but I haven't been able to find a way to get the code to trigger on the result of the formula. Using code from this link, I've been able to get the desired result by manually entering the number of months I want shown, but that's the extra step I'm trying to avoid. (I know I could make this a little easier to manage by moving the hidden rows calculated dates to the top of the sheet, for a more concise showRows/hideRows; the layout was done before I decided to try this automating the sheet).

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  if( activeSheet.getName() == 'Calendar' ) {
    var activeRange = ss.getActiveRange();
    var activeRow = activeRange.getRow();
    var activeColumn = activeRange.getColumn()
    if( activeColumn == 3 && activeRow == 1 ) {
      if ( activeRange.getValue() == '3' )
        activeSheet.hideRows(65,42);
      if ( activeRange.getValue() == '4' )
        activeSheet.showRows(65,21);
        activeSheet.hideRows(66,1);
        activeSheet.hideRows(69,1);
        activeSheet.hideRows(72,1);
        activeSheet.hideRows(75,1);
        activeSheet.hideRows(78,1);
        activeSheet.hideRows(81,1);
        activeSheet.hideRows(84,1);
        activeSheet.hideRows(86,21);
      if ( activeRange.getValue() == '5' )
        activeSheet.showRows(65,42);
        activeSheet.hideRows(66,1);
        activeSheet.hideRows(69,1);
        activeSheet.hideRows(72,1);
        activeSheet.hideRows(75,1);
        activeSheet.hideRows(78,1);
        activeSheet.hideRows(81,1);
        activeSheet.hideRows(84,1);
        activeSheet.hideRows(87,1);
        activeSheet.hideRows(90,1);
        activeSheet.hideRows(93,1);
        activeSheet.hideRows(96,1);
        activeSheet.hideRows(99,1);
        activeSheet.hideRows(102,1);
        activeSheet.hideRows(105,1);
    }
  }
};

任何帮助，甚至确认onEdit都不会影响多张图纸，将不胜感激！

Any help, even confirmation that an onEdit can't affect multiple sheets, would be appreciated!

推荐答案

onEdit 简单触发器可能会影响多个工作表，它具有多个限制，包括30秒的执行时间限制.参考 https://developers.google.com/apps-script/guides/triggers

onEdit simple trigger can affect multiple sheets it has several limitations including an execution time limit of 30 secs. Ref. https://developers.google.com/apps-script/guides/triggers

由于 hideRows 是一个缓慢的操作，并且您编写了其中的几个脚本，因此解决方案是改为使用可安装的触发器.

As hideRows is a slow operation and you script have a several of them perhaps the solution is to used a installable trigger instead.

这篇关于Google表格/脚本OnEdit可以更改两个表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！