基于公式的单元格中的onEdit脚本未在Google表格上触发 [英] onEdit script not firing on Google Sheet in formula based cells

问题描述

我对Google脚本编写完全陌生，但是我使用了各种帖子来拼凑出我需要的内容:某些东西会在我的Google表格中的每个表格中的每个单元格(不为空)上添加灰色边框，而在空白时为白色边框单元格是空的.

I'm completely new to Google script writing, but I've used various posts to piece together what I need: Something that adds grey borders to each cell in every sheet within my Google Sheets when its not empty and white borders when cells are empty.

function onEdit(range) {
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange();
  range.setBorder(true, true, true, true, true, true, "white", SpreadsheetApp.BorderStyle.SOLID);
    var values = range.getValues();
    for (var i = 0; i < values.length; i++) {
        for (var j = 0; j < values[i].length; j++) {
            if (values[i][j] !== "") {
                range.getCell(i + 1, j + 1).setBorder(true, true, true, true, true, true, "#dfdfdf", SpreadsheetApp.BorderStyle.SOLID)
            }
        }
    }
}

当我手动更改任何单元格中的数据时，此方法非常有效；但是，在脚本正在监视的那些单元上，从另一个工作表中提取数据无法触发触发器/脚本.我该如何解决这个问题，以便带有公式的单元格(引用其他工作表)仍会触发我的脚本?

This works perfectly when I manually change the data in any cell; however, on those cells where the script is monitoring, pulls data from another sheet fails to fire the trigger/script. How can I get around this so that cells with formulas (that reference other sheets) will still fire my script?

任何帮助将不胜感激.谢谢！

Any help is greatly appreciated. Thanks!

推荐答案

基于注释中的信息.

在var range中，将.getActiveSheet()更改为.getSheetByName('Sheet1')，其中sheet1是要设置边框的工作表.

In var range change .getActiveSheet() to .getSheetByName('Sheet1') where sheet1 is the sheet where you want the borders to be set.

当前，当您编辑工作表时，.getActiveSheet()会将工作表放置在进行编辑的地方，而当您在电子表格中编辑任何工作表时，您希望的是该脚本仅在特定工作表上起作用，因此.getSheetByName('Sheet1')

Currently, when you edit your sheet .getActiveSheet() is getting the sheet where you're making the edit, instead what you want is when you edit any sheet in the spreadsheet you want this script to only work on a specific sheet hence .getSheetByName('Sheet1')

编辑

如果编辑或更改是通过公式进行的，则触发器不会触发.请参见此处

The triggers don't fire if the edit or change is made by a formula. See here

这篇关于基于公式的单元格中的onEdit脚本未在Google表格上触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！