Read embedded url in Google Sheet cell?

Hi everybody,

My client has a spreadsheet that contains a list of Google Map urls embedded in text, inside cells in a Google Sheet. Using the Google Sheet  V4 reader, I am unable to read the url of the embedded text into the workspace. For example, I cannot read the url for the Google Map url for Safe’s location in this spreadsheet.  I’m really hoping to mine the lat/long coordinates from these urls.

I use FME Desktop V2021.2.2.0 in production, but have Form V 2023.2.1.0 available, if needed.

Thanks!

Hello @karol 

If the hyperlink is entered into Google Sheets using the hyperlink formula, you can get the value of the URL by setting the Render Value As parameter in the Google Sheets V4 reader as “Formula”.

You can then process the formula to extract the URL.

Based on your test spreadsheet, the URLs are simply pasted as hyperlinks. To read the hyperlinks, you can call the Google Sheets API using an HTTPCaller and specify you want the hyperlink values. You can use the same web connection as the Google Sheets reader for authentication in the HTTPCaller.

The hyperlink information is then returned as JSON. Process the JSON to get the hyperlink URL and merge the hyperlink information with the feature from the Google Sheets reader based on the row number.

I have attached an example workspace made in 2023 demonstrating this approach and I hope it helps.