Google Spreadsheet:在单元格值更改时运行脚本 [英] Google Spreadsheet: run script on cell value change

问题描述

在电子表格中，我有两张纸，*和 Calculator .当我更改"*"！B1(即工作表的单元格B1 *)上的值时，我想运行脚本:更改(实际上是清除)计算器"的值！B3(即工作表计算器"的单元格B3 ).

In a spreadsheet I have two sheets, * and Calculator. When I change the value on "*"!B1 (i.e. cell B1 of sheet *), I want to run the script: to change (actually clear) the value of "Calculator"!B3 (i.e. cell B3 of sheet "Calculator").

这是我的代码.我是通过Spreadsheet> Tools>脚本编辑器创建的，因此它不是独立的:

Here's my code. I created it through Spreadsheet > Tools > Script editor, so it's not an standalone:

版本1和版本2的区别在于，在版本1 中，第一个功能已运行，而第二个未命中(反之亦然，在版本2中反之亦然，我将在下面详细说明一件事.

The difference between Version 1 and 2 is that in Version 1 the first function is run and the second is missed (vice versa in Version 2), plus one thing I'll detail below.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var languageSheet = ss.getSheetByName("*").getSheetName();
  var languageCell = languageSheet.getRange("B1");
  var sheets = ss.getSheets()[0];
  var languageCellCheck = sheets.getActiveCell();
  var cookingMethodSheet = ss.getSheetByName("Calculator").getSheetName();
  var cookingMethodCell = cookingMethodSheet.getRange("B3");
  var range = e.range;

  if (range == languageCell)
    cookingMethodCell.setValue("A");
}

//function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var languageCell = ss.getSheetByName("*").getRange("B1");
  var sheets = ss.getSheets()[0];
  var languageCellCheck = sheets.getActiveCell();
  var cookingMethodCell = ss.getSheetByName("Calculator").getRange("B3");
  var range = e.range;

  if (range == languageCell)
    cookingMethodCell.setValue("A");
//}

版本2

//function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var languageSheet = ss.getSheetByName("*").getSheetName();
  var languageCell = languageSheet.getRange("B1");
  var sheets = ss.getSheets()[0];
  var languageCellCheck = sheets.getActiveCell();
  var cookingMethodSheet = ss.getSheetByName("Calculator").getSheetName();
  var cookingMethodCell = cookingMethodSheet.getRange("B3");
  var range = e.range;

  if (range == languageCell)
    cookingMethodCell.setValue("A");
//}

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var languageCell = ss.getSheetByName("*").getRange("B1");
  var sheets = ss.getSheets()[0];
  var languageCellCheck = sheets.getActiveCell();
  var cookingMethodCell = ss.getSheetByName("Calculator").getRange("B3");
  var range = e.range;

  if (range == languageCell)
    cookingMethodCell.setValue("A");
}

---

当我说版本1"时，我的意思是我将"*"！B1的值从其他"更改为任何"，并且单元格"Calculator"！B3没有更改.然后，使用版本2，我尝试了相同的操作，但是得到了相同的结果(B3中没有变化).

---

When I say "Version 1" I mean I changed the value of "*"!B1 from "Something" to "Whatever", and cell "Calculator"!B3 wasn't changed. Then, using Version 2, I tried the same but got the same result (no change in B3).

具有两个函数(两个版本都相同)的想法是因为我不知道是否使用 ss.getSheetByName("Calculator").getSheetName(); 或直接选择in ss.getSheetByName("*").getRange("B1"); 会有所不同.显然不是.

The idea of having two functions (same for both versions) is because I don't know if using ss.getSheetByName("Calculator").getSheetName(); or directly selecting in as in ss.getSheetByName("*").getRange("B1"); will make a difference. Apparently, it doesn't.

什么是错误，在哪里?

推荐答案

按您的变量名进行操作 var languageSheet = ss.getSheetByName("*").getSheetName(); languageSheet 实际上是一个工作表对象，而不是字符串.似乎可以肯定 varsheets = ss.getSheets()[0];

Going by your variable name var languageSheet = ss.getSheetByName("*").getSheetName(); it seems that you expect languageSheet to actually be a sheet object and not a string. That seems to be reaffirmed var sheets = ss.getSheets()[0];

因此，您必须编辑该行以说 var languageSheet = ss.getSheetByName("*")，而 var CookingMethodSheet = ss.getSheetByName("Calculator")

As such you must edit that line to say var languageSheet = ss.getSheetByName("*") and the same goes further on with var cookingMethodSheet = ss.getSheetByName("Calculator")

现在真正的问题:

if (range == languageCell)
  cookingMethodCell.setValue("A");

这将始终返回false.您正在比较两个不同的对象.他们永远不会一样.您需要使用 range.getColumn()和 range.getRow()或 range.getA1Notation()来比较它们的列和行或A1表示法.当前就像您在两张纸上写数字 3 并询问这两张纸是否是同一东西.它们将具有相同的价值并且具有可比性，但是它们永远不会是同一张纸.

this will always return false. You are comparing two different objects. They will never be the same. You need to compare their column and row or A1 notation instead by using something like range.getColumn() and range.getRow() or range.getA1Notation(). Currently it's like you write the number 3 on two pieces of paper and ask if the two pieces of paper are the same thing. They will both have the same value and be comparable, but they will never be the same piece of paper.

此外，按照您的代码进行操作，您将获得已编辑的范围，然后您将获得始终为同一单元格的活动单元格.因此，您最好将逻辑设为 if(1 == 1)或更具体地讲，因为您正在与范围类型对象进行比较，就像编写 if(1 == 2)

Furthermore, going by your code, you are getting the edited range and then you get the active cell which will always be the same cell. So you might as well have the logic be if (1 == 1) or more specifically, because you are comparing to range type objects, it's like writing if (1 == 2)

编辑:使用此答案的注释中所考虑的内容，您正在使事情变得更加复杂，而实际情况则需要如此.仅当更改 * 中的 B1 时，以下函数才会清除 Calculator 中的 B1:B3 :

With the things considered in the comments of this answer you are making things more complicated then they need to be. Here is a function that will clear B1:B3 in Calculator only if B1 in * is changed:

function onEdit(e) {
  var langName = '*'
  var langCell = 'B1'

  var curSheet = e.range.getSheet()

  if (curSheet.getName() === langName) {
    if (e.range.getA1Notation() === langCell) {
      var cookingMethodSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Calculator')
      var range = cookingMethodSheet.getRange('B1:B3')
      range.clear()      
    }
  }

您可以这样做，使它变短

which you can make a little shorter by doing

function onEdit(e) {      
  if (e.range.getSheet().getName() === '*') {
    if (e.range.getA1Notation() === 'B1') {

      SpreadsheetApp
        .getActiveSpreadsheet()
          .getSheetByName('Calculator')
            .getRange('B1:B3')
              .clear() 
    }
  }

只有缩进，所以行不会太长.

The indentation is there only so the line is not too long.

这篇关于Google Spreadsheet:在单元格值更改时运行脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！