Url field type

[MauriceWhelan]

Hi. I am trying to get the url field type working. I have a table called Product_Master_File which has a column [Cubic Metres]. When adding a product not everyone will know the cubic metre value so I have added a virtual column, type url. I have another table called External_Links_Index with 2 columns i.e. [Url] & [Link Text]. So i want to use the app formula in the VC to pull out a specific row and make it a url link to the Cubic Metre Calculator Google Sheet.

I have something like this so far but it is returning a null value.

HYPERLINK(
ANY(SELECT(External_Links_Index[Url],CONTAINS([Url],1019016821))),
“Cubic Metres Calculation”)

The number in the CONTAINS part of the expression being the id of the tab in the google sheet. Am I on the right track here. Any help would be great, thank you.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

I believe you may wish to mention a few more details so that the community members may help you better.

1. You have mentioned URL link to Google Sheet. Could you please mention if you trying to share Google sheets through URL? If so, could you please mention what is the number “1019016821” in the expresssion. Is it GID that each Google sheet has?

2. Could you please elaborate based on what condition the VC in the Product_Master_File is expected to pull up the particular Google sheet URL out of several in table External_Links_Index. From where the keyword "1019016821"in CONTAINS() function is expected to be pouplated?

[MauriceWhelan]

Hi @Suvrutt_Gurjar. Thank you for your reply. I want the url to open the Google Sheet. The sharing feature within google sheets will determine whether the user actually sees the detail.

As mentioned in the original comment the number in the expression is the ID of the google sheet.

Your observation in point 2 is correct. I will pass through a condition in the expression which is the hardcoded ID of the tab in the Google Sheet.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

Thank you. I am sorry that somehow I missed the mention about ID part in your earlier comment.

As per my understanding, you may not access a shared Google sheet by using that URL displayed and the one that ends with the GID pattern that you mentioned.

Please use the share option of the Google sheet and please copy the sharing URL shown in the Google sheet and use it to access(share) the Google sheet through your URL column.

Both URLs are almost samebut the one picked up from “Share"option has " =Sharing” option at the end.

You may then utilize suitable part in this URL , for example the part after to identify the Google sheet.
https://docs.google.com/spreadsheets/d/

Please refer images below

Image 1

Image 2

On second part , on passing the condition, I believe you are on right track.

Hope this helps.

[MauriceWhelan]

Thanks a million for your help on this @Suvrutt_Gurjar

[Suvrutt_Gurjar]

@MauriceWhelan,

You are welcome. I request you to update, if you can , if the suggested approach helpsed you in achieving your stated requirement.

[MauriceWhelan]

Hi @Suvrutt_Gurjar. Unfortunately I am still getting the same null result with my expression which now has the relevant part of the google sheet sharing link that you advised on. The expression is HYPERLINK(
ANY(SELECT(External_Links_Index[Url],CONTAINS([Url],“1r-C7-PwG1dLr8fAf0tmiby_LbvJ6pTm5lAB3cslqRqw/edit?usp=drivesdk”))),
“Cubic Metres Calculation”)

Is there anything else you could advise as a possible solution? If not perhaps someone else on the community could.

Thank you.

[Steve]

Does the SELECT() expression return the expected results when evaluated separately?

[MauriceWhelan]

No @Steve. Doesn’t appear to be. Not sure why though because the the value in the CONTAINS part of the expression is copied directly from the Google Sheet share link.

If I provide access to app and google sheet would you have a minute to review my setup?

[Steve]

Sure. sc2758g@gmail.com

[MauriceWhelan]

Thank you @Steve. Both invites on way. The appformula only contains the select function now. Haven’t put the HYPERLINK piece back in yet.

[Steve]

The External_Links_Index table is empty. Any spreadsheet row without a key column value is ignored by AppSheet. Therefore, the one row in the External_Links_Index worksheet–which doesn’t have a Record ID column value–does not exist as far as AppSheet is concerned.

[MauriceWhelan]

Ahh ok. Thank you for the heads up on that @Steve. I pressume that’s why I was getting the ‘null’ value being returned when my original HYPERLINK expression was being executed.

[Steve]

Yeah, you were getting an empty list from SELECT() because there were no rows in the External_Links_Index table.

Note that I haven’t investigated beyond this. If fixing this doesn’t solve the problem, clearly I have more to look at.

[MauriceWhelan]

Thank you for that @Steve. I am no longer getting a null value since I added a Record ID value for that row.

However when I load the form for Product Master File I am just getting the value in the [url] cell of that record ID being returned as plain text even though i have set the Virtual Column type to url. I thought my expression below would work now. HYPERLINK(ANY(SELECT(External_Links_Index[Url],CONTAINS([Url],“1r-C7-PwG1dLr8fAf0tmiby_LbvJ6pTm5lAB3cslqRqw”))),“Calculate Cubic Metres”)

Does it look correct to you?

[Steve · Accepted solution]

For the Cubic Metres Calculation column:

1. Change the column type to Show.
2. Set the Category to Url.
3. Copy the current app formula to Content.
4. Set App formula to "".

The reason you’re getting the current behavior is because you’re in a form. Forms exist to allow the user to edit column values, so the form is presenting the column value for editing. But Url column types aren’t editable (for some reason, perhaps because they could have both link and label components), so the value is grayed-out. You want to present the value as a navigation element, not as an editable field, which is what the Show column type Url category is for.

[MauriceWhelan]

Brilliant. Thanks so much @Steve. Worked perfectly.