Best answer

How can I convert URLs in Google Sheets to the actual post title?

  • 19 March 2021
  • 7 replies
  • 7586 views

I’m having trouble creating a Zap for what seems like a really simple function in Google Sheets.  If a URL is added to a row in an existing spreadsheet, I want the Zap to convert the URL into its relevant ACTUAL post title. 

For example, if https://keviniscooking.com/greek-grilled-chicken/ is inserted into the spreadsheet, I want the URL converted so it has anchor text that says Greek Grilled Chicken (or whatever the meta post title actually is.

Hoping someone can help!

icon

Best answer by nicksimard 22 March 2021, 23:20

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

7 replies

Userlevel 7
Badge +14

Hi @Bec-SSE 

Try adding the HYPERLINK formula into the desired field of the GSheets Zap step.

=HYPERLINK(url, [link_label])

Article: https://support.google.com/docs/answer/3093313?hl=en

Hi @Bec-SSE 

Try adding the HYPERLINK formula into the desired field of the GSheets Zap step.

=HYPERLINK(url, [link_label])

Article: https://support.google.com/docs/answer/3093313?hl=en

Thanks, Troy. I must still be missing something because it’s not working. Not a huge deal, I was just trying to save time by not having to edit the URL anchor link text manually.

Userlevel 7
Badge +14

@Bec-SSE 

Can you please provide screenshots of how your Zap step is currently configured? Thanks.

Hey @Troy Tessalone , so here’s what I’ve got. Hopefully you can see this :laughing:

 

 

The trigger is when column C of the spreadsheet is updated (I had to white out names for confidentiality reasons)

 

When I add a URL into column C of the spreadsheet, I want that same URL to be replaced with hyperlinked anchor text 

When I try to test the action, this the error I’m getting

 

Userlevel 7
Badge +11

Hi @Bec-SSE!

I actually found a way to do this (which I wasn’t aware that Google Sheets could do), but with the example website you gave it doesn’t quite work as you’d want it to. It’s based on the metadata for the website, I imagine.

You can use this formula: =IMPORTXML("http://www.google.com","//title")

This is what happens:

Notice all of the social network names that show up under the cell where I entered the formula. 

In your Zap, you would type out the formula and then map the website URL, as I’ve done here:

Then when the Zap runs…

… you can see that even zapier.com ends up pulling in that extra “Microsoft Logo” text. So it may not be a perfect solution. That’s the only way I know of to pull in the title automatically based on having the website address.

Userlevel 7
Badge +14

Hi @Bec-SSE 

In the Zap step, you’ll need to dynamically replace url with a dynamic data point that is enclosed in double quotes “” and same for the [link_label].

Article: https://support.google.com/docs/answer/3093313?hl=en

 

Userlevel 7
Badge +11

@Troy Tessalone I think the issue with that was that you’d need to know ahead of time what to insert as the label, as opposed to pulling it from the website’s meta data. Otherwise, definitely one way to go.

But if someone wants to provide a URL and then retrieve the title of the site, I don’t think HYPERLINK will work (I’m happy to be proven wrong, since I’m not super familiar with that function).