I need to export a table from R to Excel. One of the Excel columns has to be a hyperlink. So I'm adding a new column to build a string text using the "=HYPERLINK" function so Excel will interpret it as a hyperlink.

Excel HYPERLINK function:

=HYPERLINK(link_location, [friendly_name])

To construct my hyperlink, I need to use 2 variables from the original table.

1. The "Id" variable to build the URL for the "link_location"
2. The "CaseNumber" variable to be displayed as "friendly_name"

Based on my example:

=HYPERLINK(Id, [CaseNumber])

So first I tried:

Import %>%
    select (Id, CaseNumber) %>%
    mutate(CaseLink = glue::glue("=HYPERLINK(https://abc.xyz/{Id}, {CaseNumber})"))

Output example:

But it did not work because Excel requires the "link_location" URL to be under double quotes, like:

=HYPERLINK("https://abc.xyz/5004V000000000000A", 00000001)

So I used the function glue::double_quote to have the "link_location" URL string between double quotes:

Import %>%
    select (Id, CaseNumber) %>%
    mutate(CaseLink = glue::glue('=HYPERLINK({glue::glue_collapse(glue::double_quote("https://abc.xyz/{Id}"))}, {CaseNumber})'))

But it also did not work because it is no longer retrieving the "Id" info, but printing it as text "{Id}" as part of the string!

Output example:

But what I needed is:

Anyone could help me to correct it to accomplish such a result? It doesn't need to be using the glue package or using the HYPERLINK function from Excel. Other options and ideas are also welcome.

CodePudding user response：

Works with stringr::str_glue:

import %>%
    mutate(CaseLink = str_glue('=HYPERLINK("https://abc.xyz/{Id}", {CaseNumber})'))

# A tibble: 1 x 3
  id                 case_num CaseLink                                                  
  <chr>              <chr>    <glue>                                                    
1 5004V000000000000A 00000001 =HYPERLINK("https://abc.xyz/5004V000000000000A", 00000001)