# Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

**URL:** <https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727>\
**Category:** Ask for Help\
**Created:** [May 25, 2020, 1:10pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727 "2020-05-25T13:10:24Z")\
**Posts on this page:** 20\
**Page:** 4

<div class="post-metadata">

**Author:** ![iLuck](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/iluck/32/16214_2.png) [@iLuck](https://community.glideapps.com/u/iLuck)\
**Post date:** [November 8, 2020, 4:24pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/62 "2020-11-08T16:24:32Z")

</div>

@ThinhDinh thank you. I changed your script to copy from col D all to col R like below:

function fillFormula() {  
var ss = SpreadsheetApp.getActiveSpreadsheet();  
var sheet = ss.getSheetByName(‘Transactions’);  
var lr = sheet.getLastRow();  
var fillDownRange = sheet.getRange(lr, 8, 1);  
sheet.getRange(“H”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 9, 1);  
sheet.getRange(“I”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 10, 1);  
sheet.getRange(“J”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 11, 1);  
sheet.getRange(“K”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 12, 1);  
sheet.getRange(“L”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 13, 1);  
sheet.getRange(“M”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 14, 1);  
sheet.getRange(“N”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 15, 1);  
sheet.getRange(“O”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 16, 1);  
sheet.getRange(“P”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 17, 1);  
sheet.getRange(“Q”+(lr-1)).copyTo(fillDownRange);  
var fillDownRange = sheet.getRange(lr, 18, 1);  
sheet.getRange(“R”+(lr-1)).copyTo(fillDownRange);

}

Issue is it was looking good yesterday but today I see last row is empty from col D col R. even if I paste manually, it becomes empty after a few seconds. what should I do?

---

<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:** [November 8, 2020, 7:30pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/63 "2020-11-08T19:30:53Z")

</div>

So you want to copy all things from col H to col R down to the next row?

---

<div class="post-metadata">

**Author:** ![iLuck](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/iluck/32/16214_2.png) [@iLuck](https://community.glideapps.com/u/iLuck)\
**Post date:** [November 8, 2020, 7:54pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/64 "2020-11-08T19:54:04Z")

</div>

Yes that is what I am trying to do

---

<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:** [November 8, 2020, 10:33pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/65 "2020-11-08T22:33:54Z")

</div>

What functions do you have in those columns that can’t be set up with an arrayformula?

---

<div class="post-metadata">

**Author:** ![iLuck](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/iluck/32/16214_2.png) [@iLuck](https://community.glideapps.com/u/iLuck)\
**Post date:** [November 9, 2020, 6:43am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/66 "2020-11-09T06:43:25Z")

</div>

I have INDIRECT formula. I tried to apply array formula to sum others but Inthought it would be easierputting all into one script. Should I use arrah where possible and use script as I noted?

---

<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:** [November 9, 2020, 8:52am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/67 "2020-11-09T08:52:26Z")

</div>

Whatever you choose to as long as it works! Has your problem been solved today?

---

<div class="post-metadata">

**Author:** ![iLuck](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/iluck/32/16214_2.png) [@iLuck](https://community.glideapps.com/u/iLuck)\
**Post date:** [November 9, 2020, 9:14am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/68 "2020-11-09T09:14:20Z")

</div>

Actually no. Problem is that it is not stable. It copies down cells but later on I see copied sells empty and then back again.

---

<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:** [November 9, 2020, 10:03am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/69 "2020-11-09T10:03:38Z")

</div>

When you say empty cells, does it mean it does not have anything at all or is there a formula but it returns nothing?

---

<div class="post-metadata">

**Author:** ![iLuck](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/iluck/32/16214_2.png) [@iLuck](https://community.glideapps.com/u/iLuck)\
**Post date:** [November 9, 2020, 10:18am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/70 "2020-11-09T10:18:36Z")

</div>

It is empty. nothing in the cell. I can give you access if you want to check.

---

<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:** [November 9, 2020, 2:15pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/71 "2020-11-09T14:15:56Z")

</div>

If you can send it here or in private message then I will have a look later tomorrow. Thank you.

---

<div class="post-metadata">

**Author:** ![Joseph\_Leblanc](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/joseph_leblanc/32/16002_2.png) [@Joseph\_Leblanc](https://community.glideapps.com/u/Joseph_Leblanc)\
**Post date:** [December 16, 2020, 3:20pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/72 "2020-12-16T15:20:12Z")

</div>

Good morning,

I just discovered the magic of a ArrayFormula yesterday in my spreadsheet :-). I’m 40 and it is never too late. I know 🙂

However, every time I put this type Arrayformula =ArrayFormula(IFNA(VLOOKUP(A1:A,‘SEANCE ATP’!A:C,3,false),""))  
in a google sheet connected to a Glide Apps the data submitted through my glideapps don’t appear on the google sheet and but sometimes in the glide data table.

Do you have any solution or explanation.  
Thank you and by the way it is snowing in Chicago

---

<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:** [December 16, 2020, 3:22pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/73 "2020-12-16T15:22:23Z")

</div>

Scroll down to the bottom of the sheet. I’m sure it’s there.

Glide will not fill rows that have a formula in them, but instead add a new row at the bottom of the sheet. This new row then gets populated with the formula automatically. Arrayformulas put the formula in all rows, although it may appear empty. You need to delete all empty rows in this case.

---

<div class="post-metadata">

**Author:** ![Joseph\_Leblanc](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/joseph_leblanc/32/16002_2.png) [@Joseph\_Leblanc](https://community.glideapps.com/u/Joseph_Leblanc)\
**Post date:** [December 17, 2020, 1:54am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/74 "2020-12-17T01:54:18Z")

</div>

Thank you. I found my data and I deleted the blank rows. It works now.

---

<div class="post-metadata">

**Author:** ![Dibyaranjan\_Senapati](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dibyaranjan_senapati/32/20887_2.png) [@Dibyaranjan\_Senapati](https://community.glideapps.com/u/Dibyaranjan_Senapati)\
**Post date:** [February 1, 2021, 1:52pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/75 "2021-02-01T13:52:55Z")

</div>

Hi there

I’m facing a problem with the use of arrayformula. When ever I am using the arrayformula for a column and after that I try to make an entry from the glideaap, the new row is getting added at the end of sheet leaving so many rows blank. How to avoid this situation?

To avoid this I manually deleted the empty rows. But after this if row with Data is being deleted using glideaap, and new data is entered through aap, it is leaving the deleted row as blank instead of filling the new data in the deleted row. How to solve this issue?

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![Rosewebstudio](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rosewebstudio/32/12261_2.png) [@Rosewebstudio](https://community.glideapps.com/u/Rosewebstudio)\
**Post date:** [February 1, 2021, 2:05pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/76 "2021-02-01T14:05:56Z")

</div>

Hi @Dibyaranjan_Senapati

Welcome to the community, great to meet you.

Just one quick question - did you actually ‘delete’ the rows or did you just clear or remove the data from within the rows?

Maybe not the answer but thought it worth checking.

---

<div class="post-metadata">

**Author:** ![Dibyaranjan\_Senapati](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dibyaranjan_senapati/32/20887_2.png) [@Dibyaranjan\_Senapati](https://community.glideapps.com/u/Dibyaranjan_Senapati)\
**Post date:** [February 1, 2021, 2:09pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/77 "2021-02-01T14:09:46Z")

</div>

Actually I created a new tab and entered the arrayformula in the row header.

When I added a data from the aap, it got added at the last row of the sheet.

I deleted all the blank rows so that the entered data will be present in the second row.

---

<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:** [February 1, 2021, 2:16pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/78 "2021-02-01T14:16:41Z")

</div>

That’s just how it works and I think is a limitation of how the Google API works. Deleting all the empty rows like you did is the recommend method.

If your arrayformula is simple enough, you can most likely create the functionality within glide and you wouldn’t have to deal with arrayformulas at all.

---

<div class="post-metadata">

**Author:** ![Dibyaranjan\_Senapati](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dibyaranjan_senapati/32/20887_2.png) [@Dibyaranjan\_Senapati](https://community.glideapps.com/u/Dibyaranjan_Senapati)\
**Post date:** [February 1, 2021, 2:24pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/79 "2021-02-01T14:24:55Z")

</div>

the arrayformula I’m using here is to concatenate two columns and and I’ll be referring the resultant value to another sheet where I’m doing the other mathematical calculations.

Can I do the concatenation in the glide it self and use it to refer to the other sheet as mention above?

---

<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:** [February 1, 2021, 2:26pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/80 "2021-02-01T14:26:17Z")

</div>

Yes, you can create a Template column to join multiple column values together. I do that quite often to join multiple values to create a key to link sheets together through a relation column.

---

<div class="post-metadata">

**Author:** ![Dibyaranjan\_Senapati](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dibyaranjan_senapati/32/20887_2.png) [@Dibyaranjan\_Senapati](https://community.glideapps.com/u/Dibyaranjan_Senapati)\
**Post date:** [February 3, 2021, 2:14pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/81 "2021-02-03T14:14:23Z")

</div>

thank you so much. It worked.

[Previous page](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727.md?page=3)

[Next page](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727.md?page=5)