I've been struggling with this for a few weeks and have gone as far as I can with my limited knowledge of formulas (and my totally absent knowledge of scripts).
- I'm trying to create a prefilled link to a Google Form filled with data contained in a Google Sheet (done).
- The prefilled link should be easy to replicate over thousands of rows by simply dragging the hyperlink formula, making it unique to the data contained in each row (done).
- The prefilled link should be masked in the sheet by text, the way any other hyperlink is masked by text (not done).
Steps 1 and 2 work like the example below and populate the form quite nicely:
=HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSf7_ZUEXce6RTvHxzsIIMnUrOj2bcdIh9WSGG1V_0LHlM8QDg/viewform?entry.1079682270&entry.13269831&entry.106264669=="&A2&E2)
=HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSf7_ZUEXce6RTvHxzsIIMnUrOj2bcdIh9WSGG1V_0LHlM8QDg/viewform?entry.1079682270&entry.13269831&entry.106264669=="&A3&E3)
But I would like it to also be masked by text as is done in a typical hyperlink formula:
=HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSf7_ZUEXce6RTvHxzsIIMnUrOj2bcdIh9WSGG1V_0LHlM8QDg/viewform","Reserve Now")
If anyone can help, I'd be eternally grateful! For reference, here is a link to the Drive folder showing the actual Sheet and Form I'm working with:
https://drive.google.com/open?id=0B1ww3yce65L2MUs0WVNBOW5LRnM
Best Answer
If you add one more parameter like this:
That will add your text.
You can also do an arrayformula: