Arrayformula and importxml

Hello,

I’m working on a crypto portfolio app. Coming soon!

I am using importxml to grab the price of crypto from coinmarketcap. Unfortunately, importxml and arrayformula don’t work together, so when the user adds a coin on the app, the price stays empty on the google sheet, because I can’t use arrayformula. Any solutions? Thank you!

I was able to add the formula as a template on the database but then it won’t let me add it on the “add coin” screen.

@ThinhDinh

You are putting Google Sheet formulas into Glide table columns. That isn’t how Glide works. You do things in GSheets, and some things you can do in Glide :slight_smile:

I tried that as well…nothing worked…open to suggestions! @Mark_Turrell

In GSheets you can put the arrayformula into the top row already - so that will copy down when a new row appears.

Importxml and arrayformula don’t work together…that’s apparently common knowledge but not sure why.

Then you’ll need some script things. Or better, think creatively about your app. For instance….

You make a list of the top 100…. Fine… 1000 coins
And have one sheet that just does IMPORTXML

That means you have a sheet with the latest prices, at least.

Then when a user gets a coin, there is a rel between the coin they have…… and…… the live price sheet :slight_smile:

That’s good advice that I already tried :rofl: BUT the more these importxml rows, the slower it loads and things start breaking because the price doesn’t fetch properly. That’s why I just added like 20 coins and want to allow the end user to add their own coins.

Thank you @Mark_Turrell

Ok, in that case you can let the user select a coin, and if they don’t see it, they could ‘add’ a new one. Then, in the Form submit action (or button), you save as normal PLUS you add a row to the GSheets with your importxml formulas. Then a script in GS to put your formula into the new row.

Maybe…. You’ll have to try things out :wink: I fail a lot with my ideas :slight_smile:

Thanks! I don’t know any scripting so that could be a problem! I think I exhausted all the “no code” solutions! :rofl: :rofl: :rofl:

1 Like

As you have found out, IMPORTXML doesn’t work with arrayformulas. Scripting is the only way to do this, if you want to do it then let me know, I will try to help.

Hola,

The @Mark_Turrell ‘s idea is a good option but I would modify a little:
1- Create your Top 20 cryptocurrency List in a Sheet and set an ID to each one (coin ID). In another column you have the coin price using IMPORTXML()
2- When the user chooses a coin, he will choose a coin ID from a list and this value will be written to your GS
3- Later, use vlookup() with arrayformula() in your GS to find the current coin price associated to coin ID chosen by user.

The problem here is the price update but it is another story meanwhile, It should work with no problem.

Saludos!