1

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).

  1. I'm trying to create a prefilled link to a Google Form filled with data contained in a Google Sheet (done).
  2. 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).
  3. 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

0

If you add one more parameter like this:

=HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSf7_ZUEXce6RTvHxzsIIMnUrOj2bcdIh9WSGG1V_0LHlM8QDg/viewform?entry.1079682270&entry.13269831&entry.106264669=="&A2&E2,"Reserve Now")

That will add your text.

You can also do an arrayformula:

=ARRAYFORMULA(IF(ISTEXT(A2:A),HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSf7_ZUEXce6RTvHxzsIIMnUrOj2bcdIh9WSGG1V_0LHlM8QDg/viewform?entry.1079682270&entry.13269831&entry.106264669=="&A2:A&E2:E,"Reserve Now"),))

enter image description here

0

Assume that A1 constains

https://docs.google.com/forms/d/e/1FAIpQLSf7_ZUEXce6RTvHxzsIIMnUrOj2bcdIh9WSGG1V_0LHlM8QDg/viewform?entry.1079682270&entry.13269831&entry.106264669==

Then try something like the following:

=HYPERLINK(A1&A2&E2,"Reserve Now")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.