# Sheet formula is not working on newly created row

**URL:** <https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941>\
**Category:** Ask for Help\
**Created:** [May 28, 2020, 5:31pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941 "2020-05-28T17:31:59Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 28, 2020, 5:31pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/1 "2020-05-28T17:31:59Z")

</div>

![Screen Shot 2020-05-28 at 7.29.27 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/b/b820d9bd241d3648dafa1fb289ff996e7c0a2f1f.png) Hello, I think I’m lost in a very basic issue.

Basically in my sheet I have a couple of formulas and a script running, but when I create let’s say a new post which should use the formulas, those are not working.

I tried to copy those formulas in the entire column, but obviously Glide will add the new entry after the last row I made — see image attached.

How I can make sure the formula is replicated in a new row?

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [May 28, 2020, 6:09pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/2 "2020-05-28T18:09:33Z")

</div>

I don’t see any attached image, but I imagine the answer would be a well crafted Arrayformula.

> **[How do array formulas work in Google Sheets? Get the lowdown here.](https://www.benlcollins.com/formula-examples/array-formula-intro/)**
>
> This post takes you through the basics of array formulas in Google Sheets, with example calculations and a worksheet you can copy.

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 28, 2020, 6:16pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/3 "2020-05-28T18:16:19Z")

</div>

Hi Robert, thanks fo looking and the link.

However I’m not sure this is what I’m looking for, or if I can’t understand how to use it, as I made some tests but I can’t solve the issue.

I’ve now added the missing image to my previous post

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [May 28, 2020, 6:22pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/4 "2020-05-28T18:22:07Z")

</div>

Here’s an excellent write-up on arrayformula usage. Glide will not write data in a row that’s occupied with a formula. It looks for an empty row. When using arrayformulas, you write the formula once and it populates all rows. You just need to make sure you delete all empty rows, otherwise new data from Glide will be written to a new row at the bottom in the sheet.

> [@Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727):
>
> Hi, Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula. But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job. 
> 
> > **1/ Definition**
> >
> > Arrayformula is defined as “a range, mathematical expression using one cell range …

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 28, 2020, 8:28pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/6 "2020-05-28T20:28:19Z")

</div>

Hi @Jeff_Hager — I managed to solve for the formula, thanks! This video helped me to understand better

[![](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/5/50e23724a9aa420fb88d658462b9cc4655eeed86.jpeg "Google Sheets - Drag Formula Down Automatically - Autofill Arrays") ](https://www.youtube.com/watch?v=ndxOEbgeqoQ)

However, looks like I can’t use ArrayFormula with a custom script, correct? From what I understood I should have to change something on the script.

This is the script I’m using to calculate distances between two locations

function DrivingMeters(origin, destination) {  
var directions = Maps.newDirectionFinder()  
// .setMode(Maps.DirectionFinder.Mode.TRANSIT)  
.setOrigin(origin)  
.setDestination(destination)  
.getDirections();  
if (directions && directions.error\_message) throw directions.error\_message  
if (directions && directions.routes && directions.routes[0] && directions.routes[0].legs && directions.routes[0].legs[0] && directions.routes[0].legs[0].distance)  
return directions.routes[0].legs[0].distance.value;  
return “”;  
}

---

<div class="post-metadata">

**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\
**Post date:** [May 28, 2020, 8:48pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/7 "2020-05-28T20:48:09Z")

</div>

Hi @mrdobelina,

I saw you managed to solve you problem. I added to your formula the case when the event is already in the past.

The formula you can put in row 2:  
=if(A2=today(),“Today”,if(A2\>today(),“Leaving in “&datedif(today(),A2,“d”)&” days”,“Event over”))

The formula to be put in the header in row 1:  
={“Days to event”;ArrayFormula(if(A2:A="","",if(A2:A=today(),“Today”,if(A2:A\>today(),“Leaving in “&datedif(today(),A2:A,“d”)&” days”,“Event over”))))}

![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/a/acb996dedfdc43a37978fb8b7144e864655ef473.png)

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 28, 2020, 9:03pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/8 "2020-05-28T21:03:00Z")

</div>

Thank you @nathanaelb!

I was currently solving it with a game of TEMPLATE and IF & ELSE, but the formula looks easier.

---

<div class="post-metadata">

**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\
**Post date:** [May 28, 2020, 10:14pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/9 "2020-05-28T22:14:01Z")

</div>

Actually, if you can work your data in the Glide Data Editor rather than in GS, that would be your preferred option.

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 29, 2020, 6:39am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/11 "2020-05-29T06:39:14Z")

</div>

Can you help on using ArrayFormula with a custom script? I’m not able to make it work.

With the normal function is working

 ![normal-function-working](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/4/42a6c5b20c9bc348e3f696783f51f6a69978c5ec.png)

While with a custom script is not working

 ![custom-script-not-working](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/b/b0ad601665c646e57c76064cb0970d5973616611.png)

---

<div class="post-metadata">

**Author:** ![Spellytics](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/spellytics/32/2770_2.png) [@Spellytics](https://community.glideapps.com/u/Spellytics)\
**Post date:** [May 29, 2020, 6:51am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/12 "2020-05-29T06:51:39Z")

</div>

@mrdobelina  
What values are in F1:F?

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 29, 2020, 6:56am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/13 "2020-05-29T06:56:29Z")

</div>

F1:F is the column where I store the date of the event.

But the problem actually is not on that formula, is on the other which is

=ARRAYFORMULA(DrivingMeters(C1:C,D1:D))

C1:C is the starting point, let’s say New York  
D1:D is the arrival point, let’s say Los Angeles

The custom script is:

function DrivingMeters(origin, destination) {  
var directions = Maps.newDirectionFinder()  
// .setMode(Maps.DirectionFinder.Mode.TRANSIT)  
.setOrigin(origin)  
.setDestination(destination)  
.getDirections();  
if (directions && directions.error\_message) throw directions.error\_message  
if (directions && directions.routes && directions.routes[0] && directions.routes[0].legs && directions.routes[0].legs[0] && directions.routes[0].legs[0].distance)  
return directions.routes[0].legs[0].distance.value;  
return “”;  
}

---

<div class="post-metadata">

**Author:** ![Spellytics](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/spellytics/32/2770_2.png) [@Spellytics](https://community.glideapps.com/u/Spellytics)\
**Post date:** [May 29, 2020, 6:59am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/14 "2020-05-29T06:59:46Z")

</div>

Thanks for that - do you have a screenshot sample for F1:F?

The ARRAYFORMULA you’re using is correct I believe.  
It indicates something funky with the F1:F data.

For DATEDIF to work, F1:F needs to be after TODAY(), otherwise it would yield a #NUM! error.

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 29, 2020, 7:02am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/15 "2020-05-29T07:02:59Z")

</div>

![Screen Shot 2020-05-29 at 9.01.22 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/d/d6a967cb6c6c1ab6eb08601772ca2c59c66144fb.png)

There you go. I solved this issue and is working as intended.

**My problem is with the other formula! See down here**

It just stop on the cell where the function is added, it doesn’t go all the way to the column

 ![custom-script-not-working](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/b/b0ad601665c646e57c76064cb0970d5973616611.png)

---

<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:** [May 29, 2020, 7:31am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/16 "2020-05-29T07:31:35Z")

</div>

You can’t use Arrayformula with a custom script.

Check out the script I wrote in this post and write a custom one for your case so that it can copy to the new rows.

> [@Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727):
>
> Hi, Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula. But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job. 
> 
> > **1/ Definition**
> >
> > Arrayformula is defined as “a range, mathematical expression using one cell range …

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 29, 2020, 8:34am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/17 "2020-05-29T08:34:42Z")

</div>

Solved! Thanks!

---

<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:** [May 29, 2020, 8:44am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/18 "2020-05-29T08:44:52Z")

</div>

Nice to hear!

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 29, 2020, 9:00am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/19 "2020-05-29T09:00:23Z")

</div>

Actually, it acts weird and I can’t figure out what’s going on. Sometimes it is working, sometimes not. I don’t understand.

The first row doesn’t work

 ![Screen Shot 2020-05-29 at 10.48.31 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/7/7d0d37762b592e38f530c7cec9ee6a90bff827d4.png)

Now, also if I add another row it’s not working

 ![Screen Shot 2020-05-29 at 10.59.32 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/6/682cf0f728f71d3722eb06676a0930138edf8822.png)

This is the script

function fillFormula() {  
var ss = SpreadsheetApp.getActiveSpreadsheet();  
var sheet = ss.getSheetByName(‘Destinations’);  
var vals = sheet.getRange(“N2:N”).getValues();  
var lr = vals.filter(String).length;  
sheet.getRange(“N”+(lr)).autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT\_SERIES);}

---

<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:** [May 29, 2020, 9:55am UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/20 "2020-05-29T09:55:21Z")

</div>

Can you try the other script in that thread, the one above this?

Also, when the N2 cell is showing null, the formula is still there right?

---

<div class="post-metadata">

**Author:** ![mrdobelina](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mrdobelina/32/2308_2.png) [@mrdobelina](https://community.glideapps.com/u/mrdobelina)\
**Post date:** [May 29, 2020, 2:59pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/21 "2020-05-29T14:59:59Z")

</div>

Yes, N2 still has the formula but is not showing any value.

Now I placed the other script, and it works weird. It changes values on every row I create. See below

Original and correct - notice line 7 = 42 (distance)

 ![correct](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/d/d2d7903dac4dc6712eb1bd44fa2ecdf48ebf1058.png)

After the first new row - notice line 7 is now 38 and 8 is 1295

 ![first-add](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/c/c1999a39b6be9c9c2c0fa36effd8bbe428f61700.png)

After the second new row - notice line 7 is again 42, but 8 is now 601. Line 9 is 615 and should be correct since it’s from LA to SF in Km

 ![second-add](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/2/26427de16b7c643641b2cc1a428a10222ec25f58.png)

---

<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:** [May 29, 2020, 4:48pm UTC](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941/22 "2020-05-29T16:48:15Z")

</div>

Can you make a copy of some rows from that sheet so I can try a script on it?

[Next page](https://community.glideapps.com/t/sheet-formula-is-not-working-on-newly-created-row/9941.md?page=2)