当从IFTTT的最后一行接收到数据时，如何用公式自动填充最后一行? [英] How to Autofill last row with formula, when data is received from IFTTT on the last row?
问题描述
| | 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 |
我在哪里自动收到短信，而Android程序"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(设置公式)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.
推荐答案
通过表格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.
-
我们在标题行上使用数组文字:
{"Extracted Date";Formula for rest of the column}
每当出现新行时，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屋！
