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

Question

我有一个电子表格：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     |

在我自动获得 SMS trought android 程序“IFTTT”的地方，我在那里有公式来计算从 SMS 正文中获取的工作时间、日期等。

|    | 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 脚本。无论如何如何通过谷歌表格宏来做到这一点？我试过以下方法：

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 delete 它，但我不知道如何用 Javascript 编写它。

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

Answer 1

在通过 sheets 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")})然后表格变成：|&nbsp; &nbsp; | C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;||----+---------------------------------+---------------------------------------------------------------------------------------------------------------------|| 1> | From IFTTT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | =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 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| 3> | 0809 1800 0909 0600 RLK Dvorak&nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| 4> | 0909 0600 0909 1800 UNIS Brando |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|这里 D:D 的其余部分是自动自动填充的。我们在标题行上使用数组文字：{"Extracted Date";Formula for rest of the column}每当出现新行时，INDEX/COUNTA()自动计算最后一行并自动将公式填充到最后一行。有关 的更深入解释，请参见此处INDEX/COUNTA。

Answer 2

解决方案：该解决方案使用谷歌应用脚本.&nbsp;这不需要数组公式，而是使用自动填充。为了使解决方案起作用，您不应使用数组公式，而应使用普通公式。获取活动范围，它将代表当前插入的范围 onChange 或 onFormSubmit。offset右侧的范围以获取计算列并将范围扩展到右侧的所有计算列。用于range.autoFillToNeighbor填充范围的所有计算区域。示例脚本：/**&nbsp;* @param {GoogleAppsScript.Events.SheetsOnChange|GoogleAppsScript.Events.SheetsOnFormSubmit} e&nbsp;*/const onFormSubmitORonChange = e => {&nbsp; const rg = SpreadsheetApp.getActiveRange(),&nbsp; &nbsp; wd = rg.getWidth(),&nbsp; &nbsp; sh = rg.getSheet(),&nbsp; &nbsp; lc = sh.getLastColumn(),&nbsp; &nbsp; numRows = Math.max(&nbsp; &nbsp; &nbsp; rg&nbsp; &nbsp; &nbsp; &nbsp; .offset(0, wd, 1, 1)&nbsp; &nbsp; &nbsp; &nbsp; .getNextDataCell(SpreadsheetApp.Direction.UP)&nbsp; &nbsp; &nbsp; &nbsp; .getRow() - 1,&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; ),&nbsp; &nbsp; numCols = lc - wd;&nbsp; sh.getRange(2, wd + 1, numRows, numCols).autoFillToNeighbor(&nbsp; &nbsp; SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES&nbsp; );};