Google Sheets: Automatically add date formula to last row

0
votes

enter image description here

I'm posting information from a form to google sheets.

After the insert (which adds the last row), I want to do some apps script post processing. Before I do the post processing I want to fill in a couple of blank cells which are not submitted by the form, including the OFFEREXPIRATIONDATE column which should be set at :

=TODAY()+7

I've been reading https://www.benlcollins.com/formula-examples/array-formulas-forms/ , but I'm not sure if you can apply Arrayformulas to add new formulas into cells.

I tried putting the following in cell J2:

=ARRAYFORMULA({"TODAY + 7";J3:INDEX(J:J,IF(ISBLANK(I3),"",TODAY()+7))})

with the idea of checking if the row to the left is blank, and if not inserting the TODAY()+7 formula in, but this is giving a circular dependancy error.

How can I get this working?

1
google-sheets
Bind a form submit trigger to write the formula in r1c1 format – tehhowch

1 Answers

1
votes

Putting one of these two in J2 should do the trick:

Insert date in J unless I is empty:

=ARRAYFORMULA(to_date(if(I2:I="","",today()+7)))

Insert date in J unless I is #VALUE! or empty (in case you don't want to insert a date either when cells in I get an error):

=ARRAYFORMULA(to_date(IFERROR(if(I2:I="","",today()+7),"")))