Adding new rows breaking arrayformula

Bugs
#1

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

Screen Shot 2021-01-12 at 10.46.51 PM

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?

#2

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.

1 Like
#3

I already wrote about this error here Formula error while adding row
Support replied on January 6: “It will be fixed in next Wed. release.”

2 Likes
#4

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.

#5

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

1 Like
#6

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.

#7

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.

1 Like
#8

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

1 Like
#9

Fixed

1 Like
#10

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

1 Like