0

I have created text templates with a few placeholders for keywords. The keywords are in separated cells and there are a lot of them. The idea is, that I select a text template with a dropdown menue on a different sheet and it fills the selected text with all the keywords automatically. I get my template via VLOOKUP and using SUBSTITUTE and ROW to replace the row numbers to get the right keywords from the same row. So far so good. What I dont't get is the final evaluation. The output is always a formula that is just a text and not a functional formula.

I know that there is no EVAL function for Google spreadsheets like in Excel. So I need a workaround.

Tried so many things, like INDIRECT functions or some scripts from the internet for a self made EVAL function, but still couldn't get a working thing. I'm going crazy.

//MY TEMPLATES:
A               B
Version No.1    ""&CXXXXX&" & "&DXXXXX&" presents for your "&EXXXXX&"."
Version No.2    "Perfect for "&CXXXXX&" & "&DXXXXX&". Do some "&EXXXXX&"!"
Version No.3    "Cool "&CXXXXX&" & "&DXXXXX&"."

//MY KEYWORDS:
C             D                 E
Business      Money             Motivation
Fitness       Bodybuilding      Sports
Music         Songs             Sounds


//MY CODE FOR THE EXPECTED OUTPUT:
=CONCATENATE("=";SUBSTITUTE(VLOOKUP($A3;$A$16:$B$18;2;0);"XXXXX";ROW(B3)))

(Templates are placed at A16:B18)

//WHAT I ACTUALLY GET:

Dropdown        Output
Version No.2    ="Perfect for "&C3&" & "&D3&". Do some "&E3&"!"
Version No.3    ="Cool "&C4&" & "&D4&"."
Version No.1    =""&C5&" & "&D5&" presents for your "&E5&"."

//WHAT I NEED:

Dropdown        Output
Version No.2    Perfect for Business & Money. Do some Motivation!
Version No.3    Cool Fitness & Bodybuilding.
Version No.1    Music & Songs presents for your Sounds.

The output has to be evaluated but I didn't figured out how I can do that in Google Spreadsheets...

Here you can find the example file:

https://docs.google.com/spreadsheets/d/11vvkhgsbPqDz5sPCkPzNcGNvlHEBhRE72O8pE_keDs0/edit?usp=sharing

Rubén
  • 34,714
  • 9
  • 70
  • 166
Erc
  • 11

1 Answers1

1

Use Google Apps Script or the Google Sheets API to add your formula to a cell.

If you go for using Google Apps Script, use setFormula or setFormulaR1C1 to add a single formula.

Perhaps you should start by reading https://developers.google.com/apps-script/guides/sheets

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thank you for your answer, but still I didnt found a working solution. Is this one with the socialcalc still relevant and working? https://stackoverflow.com/questions/16303680/is-there-a-way-to-evaluate-a-formula-that-is-stored-in-a-cell/16329364#16329364 – Erc May 18 '19 at 00:55