当从最后一行的 IFTTT 收到数据时，如何使用公式自动填充最后一行? [英] How to Autofill last row with formula, when data is received from IFTTT on the last row?
问题描述
我有一个电子表格: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 |
我自动收到短信的地方是安卓程序IFTTT"；然后我有公式来计算从 SMS 正文中获取的工作时间、日期等.
Where I get automaticly SMS trought the android program "IFTTT" I have there then formulas to count the hours worked, date etc. that is taken from the SMS body.
| | 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 放在最后一个空白行.所以我不能在那里预先设置公式，因为这样它就在公式之下，除非手工完成，否则它是无用的.
I have now the issue that the android program will always put the SMS to the last blank row. So I can't have the formulas pre-set there, because then it goes under the formulas and it's useles unles done by hand.
我尝试过使用 Google Apps 脚本.无论如何如何通过 Google Sheets 宏来做到这一点?我尝试了以下方法:
I've tried with Google Apps Scripts. Is there anyway how to do that by the Google Sheets macros? I've tried the following:
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 中编写它.
However if it's used, then it won't leave the cell empty, but with the formula in it and also I would have to do this for each cell. My idea was that if(set the formula) else delete it, but I don't know how to write that in Javascript.
理想情况下，如果 D:D 列的最后一行包含文本，则使用我在那里的公式，但我也不知道该怎么做.
Ideally it would be that if column D:D last row contains text, then use the formulas I have there, but I don't know how to do that either.
推荐答案
这是处理通过 sheet api 或 Google 表单自动插入数据时非常常见的问题.最简单的解决方案是将所有公式转换为 arrayformulas.例如，你在 D2 中的公式
This is a very common problem when dealing with auto-inserted data through sheets api or Google forms. The easiest solution would be to convert all your formulas into arrayformulas. For eg, Your formula in 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"})
或使用 regex:
=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 的其余部分是自动填充的.
Here the rest of D:D is auto filled automatically.
我们在标题行使用数组文字:
{提取日期"；列其余部分的公式}
每当有新行出现时，INDEX/COUNTA() 自动计算最后一行并自动将公式填充到最后一行.请参阅此处以获取有关 INDEX/COUNTA 的更深入说明.
Whenever a new row comes, INDEX/COUNTA() auto calculates the last row and automatically fills the formula upto last row. See here for a deeper explanation on INDEX/COUNTA.
这篇关于当从最后一行的 IFTTT 收到数据时，如何使用公式自动填充最后一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
