当单元由外部源(firebase)更改时，如何在Google表格中触发Google Apps脚本? [英] How to trigger a google apps script in google sheets when a cell is changed by an external source (firebase)?

问题描述

我正在尝试触发已绑定到Google表格的Google App脚本.

I am trying to trigger a Google App Script that I have bound to a google sheet.

firebase中的数据被拉入工作表，该工作表将触发脚本.我当前使用的是onEdit(e)，当我手动向其中添加数据时可以使用，但是从Firebase更新数据时不起作用.每当更改特定范围内的单元格值时，有什么方法可以设置触发器以运行该函数.

Data from firebase is pulled into a sheet which should trigger the script. I'm currently using onEdit(e) which works when I manually add the data in but doesn't work when the data is updated from firebase. Is there any way I can set up a trigger to run the function every time the cell values in a specific range are changed.

谢谢！

function onEdit(e) {
  var ss = SpreadsheetApp.getActive()
  var dumpingGround = ss.getSheetByName("Dumping Ground")
  var dataDump = ss.getSheetByName("Data Dump")
  var ddLastRow = dataDump.getLastRow() + 1
  var editCol = e.range.getColumn();
  var editRow = e.range.getRow();

  Logger.log("edit")
  /*Copy from dumping ground to data dump*/
  if ((editRow == 1 && editCol == 2) || (editRow == 1 && editCol == 3)){

    /*Set required range for data dump and dumping ground*/
    var ddRange = '\'Data Dump\'!A' +ddLastRow+ ':BF' + ddLastRow
    var dgCopyRange = '\'Dumping Ground\'!B1:BF1'
    
    /*Copy action*/
    ss.getRange('\'Dumping Ground\'!B1:BF1').copyTo(ss.getRange(ddRange), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

    /*Check data dump row count and delete 1000 rows if necessary*/
    if(ddLastRow = 9999){
      dataDump.deleteRows(1, 1000);
    }
  }
};

推荐答案

从Apps脚本

脚本执行和API请求不会导致触发器运行.例如，调用Range.setValue()编辑单元格不会导致电子表格的onEdit触发器运行.

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

我认为您最好，但是会创建一个时间触发并检查指定范围内的变化.当然，我不能真正确定那是否可以满足您的目的，但是值得一试.

I think your best but would be to create a time trigger and check for changes in the specified range. Naturally, I can't really say for sure if that's something that would even work for your purposes, but it'd be worth a try.

以下是一些可供考虑的替代方法:

Here are some alternatives to consider:

• 如果数据提取是由脚本启动的，则只需让该函数完成所需的操作即可.
• 如果您有单独的服务来填充数据，则可以使用Sheets API.
• 您可以将脚本发布为网络应用，并通过GET触发或POST
• 您可以尝试使用 Apps Script API 执行功能>.请复查要求，因为它可能无法满足您的要求.

• If the data pull is initiated by your script, then simply have that function complete the desired actions.
• If you have a separate service populating the data, you can use the Sheets API.
• You could publish the script as a web app and trigger via GET or POST
• You could try executing a function using Apps Script API. Please review the requirements for doing so, because it may not work for your requirements.

这篇关于当单元由外部源(firebase)更改时，如何在Google表格中触发Google Apps脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！