Automatically HyperLink URL that's pasted into a form

Hi Smartsheet community. I am trying to figure out how to directly link a URL (pasted into a form !!) to a cell with the exact same hyperlink click-word for that link type. Let me explain the situation better: My company has a sort-of project database spreadsheet to keep track of "leads" or projects that could progress further into development stages. Every new lead needs to be researched with very specific criteria before submitting into the lead list, and we track the progress over time. The initial stage requires gathering unique links for that specific project, and generating a new row for that project. Columns represent the item that's needed to be gathered (for example a column will say google maps, and every project will have its unique link in its respective cell). To make life easy, we created a form to more easily fill out the values for each new project (or row). Here is the step I can't figure out: I want to paste the URL for that google maps link DIRECTLY into the form and move on with my research. When I click submit and enter all that data into the spreadsheet, I want the Google Maps cell to be hyperlinked with that URL simply reading "map". This is a fundamental step because there are lots of other links I want to operate in the exact same way. Our current workflow is extremely clunky and this will increase our lead intake efficiency so much. Please help me get creative here, I know you guys can come up with some super clever solutions! Thanks Guys!!

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers

    Unfortunately there isn't a way to drive a hyperlink with a formula. You have to interact with the sheet and use CTRL-K to create a hyperlink with text.

    Once you have a hyperlink with text, you can cell link it to other sheets, but you still have to create it the first time by hand.

    A HYPERLINK function would be amazing here, or support for some basic HTML tags like <a>

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Gianni
    Gianni ✭✭

    @Brian_Richardson

    Maybe there is no way to do this easily.. How hard do you think it would be to write some code and pop that into the back end? I have a very basic understanding of writing code and this could turn out to be a tough one for me i think. If you think its a fools errand I'll take the expert's advice, however this would drastically increase our workflow so I would be willing to take a shot at it.

    One idea I had was to write a kind of leader function that incorporates the URL strip. The URLs would be populated from the form (upon submission) directly into a hidden, adjacent column to the hyperlinked visible column in the spreadsheet. The function would look something like: IF cell_left begins with "https//" THEN perform function CNTRL_K(text: "map", URL: find cell_left). This function could be just like any other basic cell formulas, where it gets applied to all values in that column, even for future unassigned rows.

    The tricky part would be scripting the function for CNTRL_K, however, given that its already a formula in smartsheets, i imagine it wouldnt be that hard to grab it, modify it so it demands a set of arguments (text: "map", URL: find cell_left) and use that script in the hyperlink column. Do you have any experience altering the function bank in the back end at all? Or maybe better, do you have a posted example within this forum for back-end function modification? I will appreciate all the help i can get on this one! Thanks + Ciao- Gianni.

  • Brian_Richardson
    Brian_Richardson Overachievers

    I have to be honest, I don’t know a thing about customizing a function as you describe. However, if you are advanced to that degree, you might find a way to leverage the API to do this for you. Particularly Update Row method allows you to post a hyperlink to a cell and provide a displayvalue for that cell also.

    https://smartsheet.redoc.ly/tag/rows#operation/update-rows

    If you have Bridge, or another method for catching a webhook from Smartsheet then you can trigger this update on a row add when the form posts the new record.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!