Solved

Read embedded url in Google Sheet cell?

  • 6 May 2024
  • 5 replies
  • 212 views

Badge +3

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!

icon

Best answer by debbiatsafe 8 May 2024, 02:01

View original

5 replies

Userlevel 5
Badge +32

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!

Hi @karol 

 

In the Parameters you can select the option:

 

 

Link enabled:

 

Badge +3

Hi @danilo_fme , thanks for your help!

 

I don’t see the ability to manually set the data types in either the Google Sheets V4 reader or feature reader. This screenshot is from the reader, and I cannot edit or add anything in this screen. I’m not sure how to get to the menu you sent a screenshot of.

 

 

Userlevel 4
Badge +19

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.

Badge +3

Thank you, @debbiatsafe ! This will work. Very cool :) 

Userlevel 5
Badge +32

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.

Great solution @debbiatsafe 

Reply