PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Thursday, November 3, 2022

[FIXED] How can I auto fill a formula with dynamic value in array in the same cell?

 November 03, 2022     arrays, google-query-language, google-sheets, google-sheets-formula, lambda     No comments   

Issue

so I have a formula inside an array that looks like this ={CRYPTOFINANCE("kraken:"&ARL8&"/USD", "price_history", "10d")} I want to auto fill the array with the same function but with the 'ARL8' reference in an ascending order, just like auto filling cells, but keep it all in one cell it should look like this... ={CRYPTOFINANCE("kraken:"&ARL8&"/USD", "price_history", "10d") ; CRYPTOFINANCE("kraken:"&ARL9&"/USD", "price_history", "10d") ; CRYPTOFINANCE("kraken:"&ARL10&"/USD", "price_history", "10d")} ,SUBSTITUTE(TRANSPOSE(SPLIT(REPT(12,1050),2)),1,"MISTAKE")) etc. the thing is that I have about 1000 values from ARL8 reference to ARL1008 reference so it will take really long for me to write it all manually, so is there a way I can keep the rest of the function but have the ARL cell reference dynamically written while being able to specify the length of the array? It's something I am struggling for a while now and still couldn't find a way so I really appreciate if you could explain the solution as well, and if there is more info I can give let me know, and thanks.

I tried a few things but they gave me different errors I didn't know how to solve and others just didn't work, I will just put it in case it helps

=ARRAYFORMULA(CRYPTOFINANCE("kraken:"&ARL8:ARL1008&"/USD", "price_history", "10d")) the error it gave me is - "error Attribute price_history isn't supported in batch formulas"


Solution

try:

=BYROW(ARL8:ARL20, LAMBDA(x, CRYPTOFINANCE("kraken:"&x&"/USD", "price_history", "10d"))

update

you can generate a formula with a formula like:

={""; INDEX("=ARRAYFORMULA({SPLIT(""Exchange,Base,Quote,Time,Open,High,Low,Close,Quote Volume,Base Volume"", "","")"&
 QUERY(";QUERY(TO_TEXT(CRYPTOFINANCE(""kraken:""&"&C2&
 SEQUENCE(C3, 1, C4)&"&""/USD"",""price_history"",""10d"")), ""offset 1"", )",,9^9)&"})")}

enter image description here

demo sheet



Answered By - player0
Answer Checked By - Katrina (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing