添加新行时，自动用公式填充列 [英] Auto fill a column with formulas when new rows are added

问题描述

我有一个自动化工作流程(Zapier)，每当在Trello中添加新卡时，该工作流程都会向Google表格中添加新行.我们使用它来跟踪部门等的统计信息.我还有一些额外的字段，它们可以将日期字段转换为周数，日，月，年等简单的内容.

I have an automation workflow (Zapier) which adds a new row to a Google Sheets whenever a new card is added in Trello. We use it to track statistics for departments etc. I have some extra fields which transform a date field into things like weeknumber, day, month, year... simple stuff.

我需要编写一个脚本，查找在电子表格中输入的新行，然后使用预设公式自动填充其他列.

I need to write a script which looks out for new rows entered into the spreadsheet and then auto-fills the other columns with preset formulas.

示例布局:

通过自动化填充的列:

a,b,d,e

要通过脚本填充的列.

f,g,h,i,j,k

我看过一些相似的脚本，但是没有将上一行的公式用作自动填充的内容.

I've seen a few scripts that are similar but don't take a formula from the row above as the content to autofill.

注意:每一行的公式都在上面的行中，该行已由我手动添加(现在).因此，从理论上讲，脚本可以引用上面的单元格以使用正确的公式.

Note: The formulas for each column are available in the row above which has been added manually by me (for now). So, in theory, the script could reference the cell above for the correct formula to use.

注2:我无法使用ARRAYFORMULA方法，因为Zapier将看到该行具有内容，并将跳至下一个空行.

Note 2: I cannot use the ARRAYFORMULA method because Zapier will see the row as having content and will skip to the next empty row.

推荐答案

我认为这可以做到.

function fillInFGHIJK()
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++)
  {
    if(!vA[i][5] && !vA[i][6] && !vA[i][7] && !vA[i][8] && !vA[i][9] && !vA[i][10])
    {
      sh.getRange(i,6,1,6).copyTo(sh.getRange(i+1,6,1,6));//According to Michelle this will works better because the cell references change appropriately
    }
  }
}

这篇关于添加新行时，自动用公式填充列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！