# 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:** 3
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 29, 2020, 9:32am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/42 "2020-07-29T09:32:14Z")
What are the columns in the sheet for this calculation?
> **[ThinhDinh's Glide Lab](https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1033504518)**
>
> Repeated sequence
>
> User,Step,Order,Steps
> Ian Janicki,Consult,1,Consult
> Ian Janicki,Measure,2,Measure
> Ian Janicki,Materials Shipped,3,Materials Shipped
> Ian Janicki,Materials Arrived,4,Materials Arrived
> Ian Janicki,Renovation Complete,5,Renovation...
I tried it again with just the signed up date, the age at onboarding and age now with an input that is today.
---
### Author:  [@HeddaBO](https://community.glideapps.com/u/HeddaBO)
#### Post date: [July 29, 2020, 9:52am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/43 "2020-07-29T09:52:26Z")
Here is my sheet: [https://docs.google.com/spreadsheets/d/1jHcupLt5MoSTD2jSa80cUfUQLRH1AXyJYIbWG\_L\_dCM/edit?usp=sharing](https://docs.google.com/spreadsheets/d/1jHcupLt5MoSTD2jSa80cUfUQLRH1AXyJYIbWG_L_dCM/edit?usp=sharing)
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 29, 2020, 9:57am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/44 "2020-07-29T09:57:02Z")
I added an IFERROR logic so that when the date is today, it returns 0.
> **[ThinhDinh's Glide Lab](https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1033504518)**
>
> Repeated sequence
>
> User,Step,Order,Steps
> Ian Janicki,Consult,1,Consult
> Ian Janicki,Measure,2,Measure
> Ian Janicki,Materials Shipped,3,Materials Shipped
> Ian Janicki,Materials Arrived,4,Materials Arrived
> Ian Janicki,Renovation Complete,5,Renovation...
---
### Author:  [@HeddaBO](https://community.glideapps.com/u/HeddaBO)
#### Post date: [July 29, 2020, 11:37am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/45 "2020-07-29T11:37:17Z")
Wow, thanks! You’re a true hero
---
### Author:  [@AyS\_0908](https://community.glideapps.com/u/AyS_0908)
#### Post date: [September 7, 2020, 6:27pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/46 "2020-09-07T18:27:30Z")
Hi @ThinhDinh, thanks again for this tutorial.
If we use arrayformula with a “script” (that I copied-pasted without understanding anything to it 🙂 ), it does not seem to work.
I found a complement to add to the original script, but it does not seem to work either: is it the proper way to go or do you recommend something else? (for instance, your “When arrayformula doesn’t work” part)
function companylogosite(companyname) {
_if (companyname.map) { // Test whether input is an array.
return companyname.map(companylogosite); // Recurse over array if so.
} else_ {
var url = “[https://autocomplete.clearbit.com/v1/companies/suggest?query=](https://autocomplete.clearbit.com/v1/companies/suggest?query=)” +
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);
return [[data[0].domain, data[0].logo]];
}
}
Thanks.
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [September 8, 2020, 12:42am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/47 "2020-09-08T00:42:46Z")
So you’re trying to fetch JSON data?
---
### Author:  [@AyS\_0908](https://community.glideapps.com/u/AyS_0908)
#### Post date: [September 8, 2020, 7:07am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/48 "2020-09-08T07:07:48Z")
Yes I suppose that it’s the tech word.
I’m trying to collect automatically “www” + “logo” from a company name entered in G-Sheet.
For this, I will use: [https://autocomplete.clearbit.com/v1/companies/suggest?query=](https://autocomplete.clearbit.com/v1/companies/suggest?query=)
It works well, but not with ‘Arrayformula’… hence the piece of code I’ve been proposed to add to the code above:
_if (companyname.map) { // Test whether input is an array.
return companyname.map(companylogosite); // Recurse over array if so.
} else_
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [September 8, 2020, 8:31am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/49 "2020-09-08T08:31:59Z")
What error did it throw back when you use it?
---
### Author:  [@Data\_pixel](https://community.glideapps.com/u/Data_pixel)
#### Post date: [September 8, 2020, 9:38am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/50 "2020-09-08T09:38:57Z")
Hi @ThinhDinh, thanks for this tutorial.
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [September 8, 2020, 9:55am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/51 "2020-09-08T09:55:46Z")
My pleasure, let me know if I can help more.
---
### Author:  [@AyS\_0908](https://community.glideapps.com/u/AyS_0908)
#### Post date: [September 8, 2020, 12:31pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/52 "2020-09-08T12:31:07Z")
When I launch the function “companylogosite” alone, it works.
When I try to put it in arrayformula, it fails:
> [@AyS\_0908](#):
>
> function **companylogosite** (companyname) {
> _if (companyname.map) { // Test whether input is an array.
> return companyname.map(companylogosite); // Recurse over array if so.
> } else_ {
> var url = “[https://autocomplete.clearbit.com/v1/companies/suggest?query=](https://autocomplete.clearbit.com/v1/companies/suggest?query=)” +
> var response = UrlFetchApp.fetch(url);
> var json = response.getContentText();
> var data = JSON.parse(json);
> return [[data[0].domain, data[0].logo]];
> }
> }
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [September 8, 2020, 1:52pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/53 "2020-09-08T13:52:36Z")
To make it clearer, the script I used was for copying the formulas down when array doesn’t work. Seems like you’re trying to use arrayformula with a custom function, which won’t work as well.
---
### Author:  [@AyS\_0908](https://community.glideapps.com/u/AyS_0908)
#### Post date: [September 8, 2020, 2:28pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/54 "2020-09-08T14:28:16Z")
OK thanks, so if I understand well, there is no approach to simulate an arrayformula for ‘custom functions’ (script)?
Thanks
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [September 8, 2020, 2:48pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/55 "2020-09-08T14:48:30Z")
The way to do it is to call your custom function in row 2, then use the script in the post to copy it down when there’s a new row.
---
### Author:  [@AyS\_0908](https://community.glideapps.com/u/AyS_0908)
#### Post date: [September 8, 2020, 2:50pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/56 "2020-09-08T14:50:06Z")
Of course! (with a column value, is that it?)
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [September 8, 2020, 2:52pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/57 "2020-09-08T14:52:20Z")
You must do it all in your sheets, set up the script and create a trigger for it.
---
### Author:  [@mediazombie](https://community.glideapps.com/u/mediazombie)
#### Post date: [September 14, 2020, 5:40am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/58 "2020-09-14T05:40:55Z")
Thank you for this!!!
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [September 14, 2020, 7:41am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/59 "2020-09-14T07:41:58Z")
Hope it helps, do let me know if you need any assistance for this.
---
### Author:  [@iLuck](https://community.glideapps.com/u/iLuck)
#### Post date: [November 7, 2020, 9:29pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/60 "2020-11-07T21:29:11Z")
You made my day with this script @ThinhDinh. I had worked on it for hours before I saw this. Thank you!
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [November 7, 2020, 10:10pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/61 "2020-11-07T22:10:49Z")
My absolute pleasure to help. Do let me know if you need more assistance.
[Previous page](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727.md?page=2)
[Next page](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727.md?page=4)