如何在多张工作表上运行 onEdit [英] How to run onEdit on multiple sheets

问题描述

我的代码是一个调度程序，它使用户注册到特定工作表中的特定位置.问题是该代码仅适用于第一张纸(1 月 20 日星期一").

My code is a scheduler that makes a user sign-up to a particular slot in a particular sheet. The problem is that the code works only on the first sheet ("Jan 20 Mon").

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Jan 20 Mon");
  var sheet2 = ss.getSheetByName("Jan 21 Tue");
  var sheet3 = ss.getSheetByName("Jan 22 Wed");
  var sheet4 = ss.getSheetByName("Jan 23 Thurs");
  var sheet5 = ss.getSheetByName("Jan 24 Fri");

  var condition1as1 = sheet1.getRange("B2").getValue(); var condition1bs1 = sheet1.getRange("C2").getValue(); var condition1cs1 = sheet1.getRange("D2").getValue(); var condition1ds1 = sheet1.getRange("E2").getValue();
  var condition1as2 = sheet2.getRange("B2").getValue(); var condition1bs2 = sheet2.getRange("C2").getValue(); var condition1cs2 = sheet2.getRange("D2").getValue(); var condition1ds2 = sheet1.getRange("E2").getValue();
  var condition1as3 = sheet3.getRange("B2").getValue(); var condition1bs3 = sheet3.getRange("C2").getValue(); var condition1cs3 = sheet3.getRange("D2").getValue(); var condition1ds3 = sheet1.getRange("E2").getValue();
  var condition1as4 = sheet4.getRange("B2").getValue(); var condition1bs4 = sheet4.getRange("C2").getValue(); var condition1cs4 = sheet4.getRange("D2").getValue(); var condition1ds4 = sheet1.getRange("E2").getValue();
  var condition1as5 = sheet5.getRange("B2").getValue(); var condition1bs5 = sheet5.getRange("C2").getValue(); var condition1cs5 = sheet5.getRange("D2").getValue(); var condition1ds5 = sheet1.getRange("E2").getValue();

  if (condition1cs1 == "1" && condition1ds1 == "None") {
    var response = Browser.msgBox("PLEASE RECHECK SCHEDULE AND TID.", condition1as1+" , "+condition1bs1+" Are you sure?", Browser.Buttons.YES_NO);
    if (response == "yes") {
      sheet1.hideRows(2,1);
      sheet1.getRange('E2').setValue("1");
      //insert msgBox here
    } else {
      Browser.msgBox("Input again.");
    }
  }

  if (condition1cs2 == "1" && condition1ds2 == "None") {
    var response = Browser.msgBox("PLEASE RECHECK SCHEDULE AND TID.", condition1as2+" , "+condition1bs2+" Are you sure?", Browser.Buttons.YES_NO);
    if (response == "yes") {
      sheet2.hideRows(2,1);
      sheet2.getRange('E2').setValue("1");
      //insert msgBox here
    } else {
      Browser.msgBox("Input again.");
    }
  }
}

基本上，我尝试设置我正在使用的所有工作表，以便我可以随时访问它们.第一个 if 用于第一张纸(并且有效).第二个 if 用于第二张纸(它不起作用).

Basically, I tried to set-up all the sheets I am working with so I could access them anytime. The first if is for the first sheet (and it works). The second if is for the second sheet (and it doesn't work).

推荐答案

阅读电子表格编辑事件rel="nofollow">了解事件.使用传递给 onEdit 函数的事件，您可以确定发生编辑的位置，并避免使用任何显式方法来检索该信息.

Read Spreadsheet Edit Events in Understanding Events. Using the event that's delivered to an onEdit function, you can determine where an edit has occurred, and avoid using any explicit methods to retrieve that information.

警告:此功能目前在 2013 年 12 月推出的新表格中已失效.有足够的 脚本问题，您现在应该避免使用新表格.

Caveat: This feature is currently broken with the New Sheets that was introduced Dec 2013. There are enough Scripting Issues that you should avoid using the New Sheets for now.

这是您如何做到这一点的示例.

This is an example of how you could do that.

function onEdit(event) {
  var ss = event.source;  // you don't use this, but if you did, here's how to get it

  var cell = event.range;  // This is the cell that was edited
  // TODO: Add code that returns immediately if the edit occurred somewhere we don't care about

  var sheet = cell.getSheet();

  var request = sheet.getRange("B2:E2").getValues();
  var conditionas = request[0];
  var conditionbs = request[1];
  var conditioncs = request[2];
  var conditionds = request[3];

  if (conditioncs == "1" && conditionds == "None") {
    var response = Browser.msgBox("PLEASE RECHECK SCHEDULE AND TID.", conditionas+" , "+conditionbs+" Are you sure?", Browser.Buttons.YES_NO);
    if (response == "yes") {
      sheet.hideRows(2,1);
      sheet.getRange('E2').setValue("1");
      //insert msgBox here
    } else {
      Browser.msgBox("Input again.");
    }
  }
}

这篇关于如何在多张工作表上运行 onEdit的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！