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

by

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.

More Related Contents:

  1. Long processing time likely due to getValue and cell inserts
  2. Why do we use SpreadsheetApp.flush();?
  3. Convert column index into corresponding column letter
  4. Merging or Combining two onEdit trigger functions
  5. How to poll a Google Doc from an add-on
  6. How to read the correct time/duration values from Google Spreadsheet
  7. Google Spreadsheet Script – How to Transpose / Rotate Multi-dimensional Array?
  8. How to copy a row from one google spreadsheet to another google spreadsheet using google apps script?
  9. Emailing Google Sheet range (with or without formatting) as a HTML table in a Gmail message
  10. How much faster are arrays than accessing Google Sheets cells within Google Scripts?
  11. Google Sheets add-on: Set anonymous/dynamic functions to Menu using Google App Script
  12. Google Apps Script toast messages don’t appear for anonymous editors
  13. Synchronize independent spreadsheet rows, filled by IMPORTRANGE()
  14. How to speed ​up the search data in sheet
  15. Scraping data to google sheets from a website that uses JavaScript
  16. How to Transpose / Rotate Multi-dimensional Array?
  17. How to allow access for importrange function via apps script?
  18. How to fix ‘TypeError: Cannot call method “getRange” of null’ in Google Apps Script
  19. How to run certain scripts under the authority of a specific user?
  20. Set anonymous/dynamic functions to Menu
  21. To exceed the ImportXML limit on Google Spreadsheet
  22. How do I replace text in a spreadsheet with Google Apps Script?
  23. How to make onEdit() trigger function apply to multiple sheets
  24. Google apps script: how to persist data in spreadsheet between different function calls?
  25. Google Script: Play Sound when a specific cell change the Value
  26. Failure of calling Google App Script onEdit function many times in a second
  27. Add timestamp to named column in Google Spreadsheet onEdit script
  28. What are some use cases for SpreadsheetApp.flush()?
  29. google script detect empty cell after edit or delete
  30. Converting Google spreadsheet date into a JS Date object?

Leave a Comment