how to create a clickable text hyperlink to a google form or google sheet

[Sanziiv]

i am making a looker studio task checklist report which is connected to google sheets named delegation sheet copy.

i have picked up the reports column from that delegation sheet. currently the columns are name t ask details and task done date and last task status column.

i need to create a task id and in next column "click here" hyperlinked text. the named assigned for any particular task person will click on that particular click here and then that will lead to a google form. there will show task id which will seen autofill. the person will have need to only click submit button and that task will be done.

so i need the hyperlink formula which creates task id column and clickable "click here" text. if task id column can't be created then provide only "click here" hyperlinked text formula.

https://lookerstudio.google.com/u/0/reporting/aa1da12f-3e56-47c0-b4de-8f437fbe551a/page/3CRDE/edit

[lauratilton]

I would suggest adding a column to your Sheet to assign Task ID there.

Once you have Task ID as a field in your source, and you want it to auto-fill in on a Google Form, you can create a prefilled link for one Task ID for your Form. I created a sample one that looked like this using a task ID of 12345: https://docs.google.com/forms/d/e/1FAIpQLSe2GRsEqUzlbF9jEWHhbeM7x30OOfTeA-U7p8U6Q5cRqvwOIA/viewform?...

Then, create a calculated field in your data source called Prefilled Link that concatenates together the prefilled link syntax with the Task ID field. So that would look like this: CONCAT("https://docs.google.com/forms/d/e/1FAIpQLSe2GRsEqUzlbF9jEWHhbeM7x30OOfTeA-U7p8U6Q5cRqvwOIA/viewform?usp=pp_url&entry.19939772= ", Task ID)

Then, you can set that up as a hyperlink like this: HYPERLINK(Prefilled Link, Task ID) which would show the Task ID linked to the prefilled form. (Or you could combine that all into one calculated field, I just broke it up for simplicity.)

Hope that's helpful! I have found linking to prefilled Forms a great way to help the user go directly to the spot where they need to update or add data, without giving them access to the Sheet.

[Sanziiv]

First of all thanks for reply. i have created a prelinked google form but that is showing same task id for all the different task ids. so please tell how to get right task ids pre filled in the form as someone clicks on the calculated field.

and the prelinked form you have shared with me is not opening and showing need to access. and one more thing i want the text "click here" while current hyperlink is applied on task id's number "6590", 6592 etc.https://lookerstudio.google.com/u/1/reporting/62cef5a9-6294-41a1-a120-505f76663b58/page/lWgDE/edit 

[lauratilton]

If Task ID is already a field, then you can use the syntax of a prefilled Google Form link (as explained in my previous response) to combine your Task ID field with the syntax of the prefilled Google Form link using CONCAT (to create a calculated field called Prefilled Link.)

I'm not able to access the link you shared. You don't need to access my form link; I only provided that as an example of syntax.

If you'd prefer the words "Click Here" be linked rather than the Task ID in your table, just change the text in the HYPERLINK formula (that uses the Prefilled Link calculated field explained above) to something like this: HYPERLINK(Prefilled Link, "Click Here")

[Sanziiv]

I have applied hyperlink(prelink form url,task id). i don't want click here as header but as clickable text. currently task id numbers are being shown as hyperlink instead of "click here". and when i am clicking on any particular task id it is showing same task id "6588" which i had used to create the prelink form.

please see the last column new field and the text "click". when we click on that text pre linked google form opens for that particular id and got submitted by user. so please tell me how to make like that.

i have updated the access link of my report please see.

[lauratilton]

Hi, I think all the information you need is in my previous responses but I'll try to explain again. 

If you look in the TASK ID field in your table, you'll see that the URL you're referencing includes 6588. You need to change this so that it concatenates in the Task ID field instead of 6588. Then it will change for each row of the table. You also need to change the "Task ID" reference which is the display part of your hyperlink formula to "Click Here".

What you have now under TASK ID in the table:
HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSefpbzxbxQradF2ISMx79cZbhaI20IKszNQWsIxI_rBZZPUUw/viewform?usp=pp_url&entry.546974750=6588",Task ID)

What you need instead:
HYPERLINK(CONCAT("https://docs.google.com/forms/d/e/1FAIpQLSefpbzxbxQradF2ISMx79cZbhaI20IKszNQWsIxI_rBZZPUUw/viewform?usp=pp_url&entry.546974750=",Task ID), "Click Here")

[Sanziiv]

When I saw the reply I thought that it got done. But i think there is some
error in the hyperlink(concat formula.

I have tried both manually and copy paste what u have provided me in the
forum but nothing is working. It shows invalid formula. I couldn't find the
error. Please check.

[lauratilton · Accepted solution]

(updated to remove live links from the formulas above as they caused it not to display correctly)

What you have now under TASK ID in the table:
HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSefpbzxbxQradF2ISMx79cZbhaI20IKszNQWsIxI_rBZZPUUw/viewform?usp=pp_url&entry.546974750=6588",Task ID)

What you need instead:
HYPERLINK(CONCAT("https://docs.google.com/forms/d/e/1FAIpQLSefpbzxbxQradF2ISMx79cZbhaI20IKszNQWsIxI_rBZZPUUw/viewform?usp=pp_url&entry.546974750=",Task ID), "Click Here")

[Sanziiv]

When I saw the reply I thought that it got done. But i think there is some error in the hyperlink(concat formula. 

I have tried both manually and copy paste what u have provided me in the forum but nothing is working. It shows invalid formula. I couldn't find the error. Please check

[lauratilton]

Please refer back to what I posted ("What you need instead") above. This will work. (Use what is in the forum, not in your email.)

[Sanziiv]

sorry for replying late yesterday it was holiday here. so i got my solution. you are just amazing. thanks a lot. only i want to ask that how to make the report like exactly what i had shared a screenshot. i have applied that black theme and i was trying to apply a filter on planned column but not able to create header drop down of dates like screenshot. please guide. again thanks a lot.

Accept as Solution

[Sanziiv]

sorry for replying late yesterday it was holiday here.i got my solution as i want it to be. you are just amazing. thanks a lot. i have created my report successfully only because of you. i am using looker studio for first time.so i will post againg if i will get stuck anywhere.you are doing great work.again thanks a lot.

[lauratilton]

Glad to help! One other suggestion might be to remove the Record Count from the metric section of the table, since it is probably always 1 you don't really need to include it. You can then resize the columns as you like by following the instructions here.

[Sanziiv]

yes i have removed that. but i am facing a problem. in my original report when i am clicking or share with others the report is not opening. it is showing an error.

i have relinked the calculated field (hyperlink(concat. but nothing is working. please tell why this is happening.