How to Autofill last row with formula, when data is received from IFTTT on the last row?

Question

I have a spreadsheet: 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     |

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 |

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.

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";""))

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.

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.

Answer 1

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

can be modified as

In 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"})

OR using regex:

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

The table then becomes:

|    | 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 |                                                                                                                     |

Here the rest of D:D is auto filled automatically.

• We use array literals on the header row: {"Extracted Date";Formula for rest of the column}

• 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.

Answer 2

Solution:

• This solution uses google-apps-script. This does not need array formulas, but uses autofill. For the solution to work, you should not use array formula, but plain formula.
• Get the active range, which will represent the current range inserted onChange or onFormSubmit.
• offset the range to the right to get the calculated columns and expand the range to all of calculated columns on the right.
• Use range.autoFillToNeighbor to fill all the calculated regions of the range.

Sample script:

/**
 * @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
  );
};

References:

• How to create a onFormSubmit or onChange trigger for this function?