Sheet formula is not working on newly created row

Ask for Help
23

Hello, sure, there you go

As before, the first row will remain empty, and actually the formula on C:C I had to copy it manually as it wasn’t updating in the newly created row

24

Firstly in your formula, you have to write it like this:

=DrivingMeters(A2,B2)

Instead of:

=DrivingMeters(A2:A,B2:B)

Then, in column D, which I will call “Kilometers”, you are trying to use an Arrayformula on the header cell, which can’t compute a division over 1000 since it’s a text. You have to pass it like this for the column to work on rows with numerical data.

={"Kilometers";ARRAYFORMULA(IF(C2:C<>"",C2:C/1000,""))}

I have modified the script a bit. It should now work.

function fillFormula() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheet = ss.getSheetByName("Distances");
spreadsheet.getRange('C2').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
var lr = spreadsheet.getCurrentCell().getRowIndex();
spreadsheet.getRange("C"+(lr)).autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);}
2 Likes
25

Awesome! It works perfectly, now. Thank you so much — learnt something new.

1 Like
26

Sorry to bother, but actually it’s not working. I checked and double checked if I found any differences from the sample you made, but I can’t find any. And it’s not loading the script on a newly added row.

Since I have to submit it to the app store (the app is actually already in the Glide store, but I want to add this feature), can I share you the sheet to please have a look in the “production” file?

27

Yeah send me a private message, I will try to find the error.

1 Like