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