Please Add Hyperlink Formula

[George_Conlow]

Google Sheets has a formula called Hyperlink.
HYPERLINK(url, [link_label])
Example
HYPERLINK(“http://www.google.com/”, “Google”)
Where url is The full URL of the link location enclosed in quotation marks, or a reference to a cell containing such a URL.

and link_label - [optional] is the text to display in the cell as the link, enclosed in quotation marks, or a reference to a cell containing such a label.

It would be helpful if airtable could do this too.
My use case: Zapier finds new google docs in a special folder and adds records to an airtable which includes TITLE and URL. I have a view of that table that is embedded on my internal google site so that all new content in that folder (medical protocols) are available to my staff. It would be quite nice to have one field (a clickable link) rather than two - the title and an ugly url.

Please like this post to help it raise to the top!

[Edward_Grenzig]

[Justin_Barrett] & [kuovonne]
I have installed the scripting app and started by looking to see if a script actually exists for my application, since I am really not a programmer and capable overwriting my own script at this point in time.

I found the following from one of Justin’s previous posts
--------------script found-------------

Just ran a quick test with the scripting block, and it works.

let tb = base.getTable(“URL Test”);
let query = await tb.selectRecordsAsync();
for (let record of query.records) {
let url = record.getCellValue(“URL”);
let name = record.name;
let text = record.getCellValue(“Text”);
if(url) {
tb.updateRecordAsync(record, {“Rich”: [${text}](${url}) - ${name}})
}
}
Screen Shot 2020-08-26 at 2.12.09 PM
Screen Shot 2020-08-26 at 2.12.09 PM
713×172 7.25 KB
This will only make the hyperlink when there’s a URL in the {URL} field. With a little more twiddling, this could be modified to work via Airtable’s automation system, to automatically create the hyperlink as the record is being edited.
-----------------end script post-------

Do you think this script will work if I just replace the 3 fields called URL, TEXT, and RICH with the names of my associated fields? I am just not sure if I need the NAME field and what this field is actually doing?

When run, Will this script run through all 1000 records in my table?

Ed

[kuovonne]

In addition to replacing the field and table names, this script needs a few adjustments before it will work on your 1000 records.

tb.updateRecordAsync(record, {'Rich': `[${text}](${url}) - ${name}` })

This line will get you in trouble with Scripting App’s rate limit, and the updates will probably fail after the first 15. There are two workarounds:

(1) Simply put await in front of the line. This will make the entire script take longer to run, but it is a very simple fix.

await tb.updateRecordAsync(record, {'Rich': `[${text}](${url}) - ${name}` })

(2) Rework the script to update the records in bulk after the loop. This requires a little bit more work, but is quite easy for someone familiar with JavaScript and the Scripting API.

---

Other notes:

if(url) {

This line means that the record will be updated only if there is a url. Note that if there is a url but no text, the link won’t work because there will be no text.

let name = record.name;

record.name is the value of the primary field. You don’t really need it. If you get rid of it, get rid of it in both places:

await tb.updateRecordAsync(record, {'Rich': `[${text}](${url})` })

[Edward_Grenzig]

Kuovonne

Once again I appreciate your help, but the very sad happenings at the Capitol yesterday set me back from answering you sooner.

The following is the script I just tried, but did not work

let tb = base.getTable(‘Race Listing copy’);
let query = await tb.selectRecordsAsync();
for (let record of query.records) {
let url = record.getCellValue(‘Photos Link’);
let text = record.getCellValue(‘Description 2’);
if(url) {
await tb.updateRecordAsync(record, {‘Description 3’: ‘${text}’ })
}
}

The result is not correct

My result is: ${text}

in every record of field Description 3

Any ideas?

Photos Link is a URL field

Description 2 is a Formula field ( I also tried a short text field instead)

Description 3 is a long text with rich text enabled

[Justin_Barrett]

Edward_Grenzig:

[Justin_Barrett] & [kuovonne]

To tag a user (i.e. alert them to your comment), type the @ symbol, then just wait a moment. If you’re in the middle of an existing thread, the forum will show a popup containing the usernames of everyone who has contributed to that thread. Use the cursor up and down keys to navigate the list and select the person you’d like to tag, then hit Enter. (If you’re starting a new thread, you’ll need to type the first couple of characters of the username, and the forum will show a similar list of users matching your partial tag.)

[Edward_Grenzig]

@Justin_Barrett Got it, thanks!

[kuovonne]

Thank you for including the screen capture.
It looks like you are using a straight quote ' instead of a backtic ` in this line:

await tb.updateRecordAsync(record, {'Rich': `[${text}](${url})` })

When use the backtic character around the string, ${variable name} is replaced by the variable value. If you use straight quotes, it is not.

This forum sometimes garbles the display of quotes and backtics.

[Edward_Grenzig]

@kuovonne
That was it! Works Great!
9 min run time, and then I use automation for updates…

Thanks to both of you, Justin also!
Have a great day! :thumbs_up:

[Drew_Lentz]

Just wanted to let you know this saved me hours of work :grinning_face_with_big_eyes:
Thank you!

[spirit_loveroot]

So what’s the current best way to import a column of hyperlinks from a google sheet given all the updates since the initial posting of this thread?

[Kailyn_Nelson]

Yes, please! We stash links from SharePoint which are ugly.