How to evaluate a concatenated formula in Google Sheets?

1
votes

Is there any way, using script or otherwise, to evaluate a concatenated formula in Google Spreadsheets?

I have created a concatenated formula for the Google Finance function to fetch daily exchange rates. This is because my date and currencies keep changing; instead of "usdeur", I at times am interested in fetching "usdinr" or "euraud". Using concatenate and some basis scripting, I was able to get the below formula pasted in a cell as text. All that is required is a way to execute the below string. 

"=GoogleFinance("usdeur","price",date(2013,12,1),date(2013,12,16))"

From what I understand, Sheets does not have an "EVAL" function like Excel does. In sheets, pressing "Enter" twice will do that trick. Is there any way I can write a script for that?

3
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaevaluate
setFormula() and possibly an onEdit trigger – Robin Gertenbach
what does your concatenate function look like?- you can achieve all this without the need for scripting at all - you can join pieces of the function together dynamically – Aurielle Perlmann
@Aurielle: Thanks for the reply. I was able to join the pieces dynamically. I need help scripting its execution. This is how my concatenate looks like: =CONCATENATE("=GoogleFinance(""",A10,""",""","price"",date(",E3,"),date(",E6,")") With some basic scripting I was able to paste the formula as a text string in one of the cells. The only piece missing is execution. Pressing "Enter" twice should do that trick, though I am open to trying other alternatives. – Mirza Zia
@RobinGertenbach: setFormula + onEdit will definitely work. I'm trying to figure out how to get the updated string into the editor every time I change currencies. Isn't setFormula static? – Mirza Zia
Does this answer your question? Is there a way to evaluate a formula that is stored in a cell? – Rubén

3 Answers

2
votes

You can do this without concatenate: 

=GoogleFinance(A10,"price",E3,E6)

enter image description here

1
votes

Thank you for your workaround and it does indeed work. Based off your instructions it looks like it can be simplified even further.

  1. In Cell B1 Enter the following:="=sum(A1:A5)"
  2. In Cell C1 Set a data validation and select B1 with dropdown option.

Now select C1 and select the formula from the dropdown, it will sum any values between A1 through A5 automatically.

I have a sheet where I was creating a complicated formula for multiple values and this process worked!

Thank you once again as I was trying to avoid a script since I have data that is being pulled by another program on my worksheet. Script function do not always run automatically in those situations.

-1
votes

Awesome work around for google not having evaluate(). I have looked all around and besides script have found no other way to have a formula as a string on one sheet then use that formula on another. In fact everything I've seen says you can't. Would be helpfull if anyone reading this could repost around if they come to an appropriate question since I must have read a half dozen posts saying it wasn't possible before I just rolled up my sleaves and done done it. :) It still has a little clunkyness since you need two cells in the spreadsheet you want the formula to execute, but here goes.

Ok, some set up. We'll call the spreadsheet with the formula as string SpreadsheetA, call the tab the formula is on TabAA, the Spreadsheet you want to call and execute said formula SpreadsheetB. I'll use a multi-tab example, so say you want the sum of A1:A5 on SpreadsheetB tab: TabBA to be calculated on SpreadsheetB tab: TabBB cell A1. Also call the URL of spreadsheet A: URLA.

So, in Spreadsheet A Tab: TabAA cell A1 put ="=sum(TabBB!A1:A5)", therefore the cell will display: =sum(A1:A5). Note: you don't need any $ in formula. Then in Spreadsheet B, Tab: TabBB, cell A2 put: =Query(Importrange("URLA","TabAA!A1"),"select Col1 where Col1 <> ''"). That cell will now display =sum(TabBA!A1:A5). Next to that, cell A1 of Spreadsheet B tab: TabBB, create a dropdown of the cell with the formula in B2 (right click cell A1, select data validation, for Criteria select: List from range, enter B2 in box to right). That cell should now be summing SpreadsheetB, TabBA, range A1:A5.

Hope that was clear, im rather novice at this. Also important, obviously you would only do this in cases where you wanted to choose from multiple formulas on spreadsheetA, instead of TabAA!A1 say you had another formula in A2 also so your query would be =Query(Importrange("URLA","TabAA!A1:A2"). I understand in the simplistic case given you would simply put the formula where you needed the sum.

Edit: Something I noticed, was when I wanted to use a formula with double quotes the above scenario didn't work because when you wrapped the formula with double quotes in double quotes you get an error since you need single quotes inside double quotes. The example I was trying: if(counta(iferror(query(B15:C,"select C where C = 'Rapid Shot' and B = true")))>0,Core!$C$18+$C$10&" / ",)&Core!$C$18+$C$10&if(Core!$C$18>5," / "&Core!$C$18-5+$C$10,)&if(Core!$C$18>10," / "&Core!$C$18-10+$C$10,)&if(Core!$C$18>15," / "&Core!$C$18-15+$C$10,) In that case I put another formula into Spreadsheet A TabAA cell A2 that read ="="&A1. Then, ajusted the importrange referance in spreadsheet B to reference that cell instead.

BTW, this absolutly works so if you can't get it let me know where your having problems, I don't do a lot of colaboration so maybe I'm not saying something clear or using the right / best terminollagy but again I've seen many posts saying this was impossible and no one saying they had found another way. Thanx ~ K to the D zizzle.