# Adding new rows breaking arrayformula

**URL:** <https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080>\
**Category:** Report a Bug\
**Created:** [January 12, 2021, 2:50pm UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080 "2021-01-12T14:50:51Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 12, 2021, 2:50pm UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/1 "2021-01-12T14:50:51Z")

</div>

I have a sheet with an arrayformula in one of the column headers that looks like so:

`={"Short Date";ARRAYFORMULA(IF(B2:B="","",B2:B))}`

Column B contains a date, formatted as `yyyy-mm-dd hh:mm:ss`  
The arrayformula is in a separate column (N), and the column is formated as `mmm dd`

So essentially I’m just taking the long date, copying it to another column and reformatting.  
The arrayformula works fine, BUT… every time a row is added to the sheet, it breaks 😱

![Screen Shot 2021-01-12 at 10.46.51 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/2/323628e26a68a41556171f28a52494cf2a27f303.png)

I’ve checked and re-checked, and I’m 99.99% certain that nothing is being written to that column when a row is added. So I’m stumped.

Anyone encountered this problem before?

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [January 12, 2021, 3:41pm UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/2 "2021-01-12T15:41:31Z")

</div>

Have you ever had any actions configured to write to that column, even incidentally? I think the add row action has a bug that has not been dealt with. Seems like when you switch between an actual value and a custom value, it writes an empty character instead of a “null” value so the formula breaks.

---

<div class="post-metadata">

**Author:** ![Vladimir\_Zambakhidze](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/vladimir_zambakhidze/32/13882_2.png) [@Vladimir\_Zambakhidze](https://community.glideapps.com/u/Vladimir_Zambakhidze)\
**Post date:** [January 12, 2021, 3:48pm UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/3 "2021-01-12T15:48:17Z")

</div>

I already wrote about this error here [Formula error while adding row](https://community.glideapps.com/t/formula-error-while-adding-row/20727)  
Support replied on January 6: “It will be fixed in next Wed. release.”

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 12, 2021, 3:59pm UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/4 "2021-01-12T15:59:38Z")

</div>

> [@ThinhDinh](#):
>
> Have you ever had any actions configured to write to that column, even incidentally? I think the add row action has a bug that has not been dealt with. Seems like when you switch between an actual value and a custom value, it writes an empty character instead of a “null” value so the formula breaks.

Not that I can remember. In fact, as part of my troubleshooting I tried including a ‘Clear Values’ on this column as part of the compound action. But that didn’t help. Just looking at the reply below from @Vladimir_Zambakhidze, it seems like it might be a bug.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 12, 2021, 4:00pm UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/5 "2021-01-12T16:00:18Z")

</div>

> [@Vladimir\_Zambakhidze](#):
>
> I already wrote about this error here [Formula error while adding row](https://community.glideapps.com/t/formula-error-while-adding-row/20727)  
> Support replied on January 6: “This will be fixed in the next release of the environment.”

ah, thank you.  
Yes, it seems like I’ve bumped into the same bug.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 13, 2021, 12:51am UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/6 "2021-01-13T00:51:14Z")

</div>

I created a simple trigger function to work around this for the time being:

```
function format_short_date (sheet) {
  var col = 14;
  for (var row=2; row<=sheet.getLastRow(); row++) {
    sheet.getRange(row,col).setFormula('=B'+row).setNumberFormat('mmm dd');
  }
}

```

It’s a bit slow, because it processes row by row, but in this case speed is not important.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [January 13, 2021, 1:05am UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/7 "2021-01-13T01:05:04Z")

</div>

I was using a script to handle my case. I had the arrayformula in the row header, so I just grab the column from row 2 onwards and clear them. Hopefully it’s faster for your case.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 13, 2021, 3:06am UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/8 "2021-01-13T03:06:48Z")

</div>

Oh, that’s a good idea. Didn’t occur to me to do it that way. Much better, thanks 👍

---

<div class="post-metadata">

**Author:** ![Vladimir\_Zambakhidze](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/vladimir_zambakhidze/32/13882_2.png) [@Vladimir\_Zambakhidze](https://community.glideapps.com/u/Vladimir_Zambakhidze)\
**Post date:** [January 14, 2021, 3:54pm UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/9 "2021-01-14T15:54:41Z")

</div>

Fixed

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 19, 2021, 1:53am UTC](https://community.glideapps.com/t/adding-new-rows-breaking-arrayformula/21080/10 "2021-01-19T01:53:02Z")

</div>

Not for me. I just tried removing my workaround, and as soon as the next row was added, it broke 🤬