当从最后一行的 IFTTT 收到数据时，如何使用公式自动填充最后一行？

IT技术 javascript google-apps-script google-sheets google-sheets-formula
2021-02-25 09:12:07

我有一个电子表格：https : //docs.google.com/spreadsheets/d/1df2cp4DsJvSeBvhsNjLgIa5x_RO1X7s_APRdFzU6jqQ/edit?usp=sharing

|    | C                               | D              |
|----+---------------------------------+----------------|
| 1> | From IFTTT                      | Extracted Date |
| 2> | 0809 1800 0909 0600 RLK Steiger | 08.09.2020     |
| 3> | 0809 1800 0909 0600 RLK Dvorak  | 08.09.2020     |
| 4> | 0909 0600 0909 1800 UNIS Brando | 09.09.2020     |

我在那里自动获得短信 trought android 程序“IFTTT”我有然后计算工作小时数、日期等的公式，这是从短信正文中获取的。

|    | C                               | D                                         |
|----+---------------------------------+-------------------------------------------|
| 1> | From IFTTT                      | Extracted Date                            |
| 2> | 0809 1800 0909 0600 RLK Steiger | =MID(C2,1,2)&"."&MID(C2,3,2)&".2020"      |
| 3> | 0809 1800 0909 0600 RLK Dvorak  | =MID(C3,1,2)&"."&MID(C3,3,2)&".2020"      |
| 4> | 0909 0600 0909 1800 UNIS Brando | //<= New row from IFTTT. Set formula here |

我现在遇到的问题是 android 程序总是将 SMS 放在最后一个空白行。所以我不能在那里预先设置公式，因为这样它就在公式之下，而且它是手工完成的。

我尝试过使用 Google Apps 脚本。无论如何如何通过 Google Sheets 宏来做到这一点？我尝试了以下方法：

function myFunction() {    
  var spreadsheet = SpreadsheetApp.getActive();       
   spreadsheet.getRange('D1').activate();
   spreadsheet.getCurrentCell().setFormula('=if(isnumber(A1);mid(C1;1;2)&"."&mid(C1;3;2)&".2020";""))

但是，如果使用它，那么它不会将单元格留空，而是使用其中的公式，而且我必须为每个单元格执行此操作。我的想法是 if(set the formula) else 删除它，但我不知道如何在 Javascript 中编写它。

理想情况下，如果列的D:D最后一行包含文本，则使用我在那里的公式，但我也不知道该怎么做。

2个回答

这是通过sheet api 或Google 表单处理自动插入数据时的一个非常常见的问题。最简单的解决方案是将所有公式转换为arrayformulas。例如，你在 D2 中的公式

=MID(C2,1,2)&"."&MID(C2,3,2)&".2020

可以修改为

在 D1：

=ARRAYFORMULA({"Extracted Date";MID(C2:INDEX(C:C,COUNTA(C:C)),1,2)&"."&MID(C2:INDEX(C:C,COUNTA(C:C)),3,2)&".2020"})

或使用 正则表达式：

=ARRAYFORMULA({"Extracted Date";REGEXREPLACE(C2:INDEX(C:C,COUNTA(C:C)),"(\d{2})(\d{2}).*","$1.$2.2020")})

然后表格变成：

|    | C                               | D                                                                                                                   |
|----+---------------------------------+---------------------------------------------------------------------------------------------------------------------|
| 1> | From IFTTT                      | =ARRAYFORMULA({"Extracted Date";MID(C2:INDEX(C:C,COUNTA(C:C)),1,2)&"."&MID(C2:INDEX(C:C,COUNTA(C:C)),3,2)&".2020"}) |
| 2> | 0809 1800 0909 0600 RLK Steiger |                                                                                                                     |
| 3> | 0809 1800 0909 0600 RLK Dvorak  |                                                                                                                     |
| 4> | 0909 0600 0909 1800 UNIS Brando |                                                                                                                     |

这里 D:D 的其余部分是自动填充的。

  • 我们在标题行上使用数组文字：{"Extracted Date";Formula for rest of the column}

  • 每当新行出现时，INDEX/COUNTA()自动计算最后一行并自动将公式填充到最后一行。有关的更深入解释，请参见此处INDEX/COUNTA。

您好，非常感谢您的帮助！我试过这样做，但我不知道为什么，但由于我假设的语言存在一些差异。例如，我不能使用 ，但我必须使用 ; 没关系，我可以检查一下。但是它不会填满表格，因为根据工作表，MID 函数有 3 个参数而不是 3 个。我不确定我是否在那里做错了什么？如果可能的话 - 我不想打扰太多，这已经是很大的帮助了！要从另一个工作表“List2”中获取数据，可以使用 counta 函数，对吗？再次感谢你！
2021-04-28 09:12:07
@PatrikGremlica =ARRAYFORMULA({"Datum";ČÁST(C2:INDEX(C:C;POČET2(C:C));1;2)&"."&ČÁST(C2:INDEX(C:C;POČET2(C:C));3;2)&".2020"})
2021-04-28 09:12:07
嗨，对不起，这是我第一次自己使用这个网站，我真的很感激这个，也编辑了原始问题。我用过这个，不幸的是它会翻译成捷克语： =ARRAYFORMULA({"Datum";ČÁST(C2;INDEX(C:C;POČET2(C:C));1;2)&"."&ČÁST( C2;INDEX(C:C;POČET2(C:C));3;2)&".2020"})
2021-04-30 09:12:07
@PatrikGremlica 关于中期，你能在这里显示你编辑的当前公式吗？请就非相关问题提出新问题
2021-05-07 09:12:07
@PatrikGremlica 这不应该发生。中的行之间是否有额外的空格C:C？如果是这样，你应该删除它。或者，只需将 +1 添加到 counta: COUNTA(...)+1。如果单击答案左侧的复选标记有帮助，请考虑接受答案。
2021-05-07 09:12:07

解决方案：

  • 该解决方案使用 谷歌应用程序脚本. 这不需要数组公式，但使用自动填充。为了使解决方案起作用，您不应使用数组公式，而应使用普通公式。
  • 获取活动范围，该范围将代表插入 onChange 或 onFormSubmit 的当前范围。
  • offset 右侧的范围以获取计算列并将范围扩展到右侧的所有计算列。
  • 使用range.autoFillToNeighbor填补范围内的所有计算的地区。

示例脚本：

/**
 * @param {GoogleAppsScript.Events.SheetsOnChange|GoogleAppsScript.Events.SheetsOnFormSubmit} e
 */
const onFormSubmitORonChange = e => {
  const rg = SpreadsheetApp.getActiveRange(),
    wd = rg.getWidth(),
    sh = rg.getSheet(),
    lc = sh.getLastColumn(),
    numRows = Math.max(
      rg
        .offset(0, wd, 1, 1)
        .getNextDataCell(SpreadsheetApp.Direction.UP)
        .getRow() - 1,
      1
    ),
    numCols = lc - wd;
  sh.getRange(2, wd + 1, numRows, numCols).autoFillToNeighbor(
    SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES
  );
};

参考：

其它你可能感兴趣的问题
上一篇使用 InlineEditing 和 CellEdit 在 JQ Grid 上启用保存按钮 下一篇如何将数字格式化为货币字符串