No API, No Problem

# No API, No Problem
## Automating content delivery with R and Excel
<hr>
### Reda Mastouri @redamastouri
### slides at <a href="https://github.com/redamastouri/no-api-no-problem">github.com/redamastouri/no-api-no-problem</a>

---

# We are in the future...

---

# So we can move on, right?

---

---

# Your boss wants 49 Excel sheets

---

# Data dump

```r
sales_data
```

```
## # A tibble: 9,994 x 21
## Person `Order ID` `Order Date` `Ship Date` `Ship Mode`
## <chr> <chr> <dttm> <dttm> <chr> 
## 1 Cassa… CA-2016-1… 2016-11-08 00:00:00 2016-11-11 00:00:00 Second Cla…
## 2 Cassa… CA-2016-1… 2016-11-08 00:00:00 2016-11-11 00:00:00 Second Cla…
## 3 Anna … CA-2016-1… 2016-06-12 00:00:00 2016-06-16 00:00:00 Second Cla…
## 4 Cassa… US-2015-1… 2015-10-11 00:00:00 2015-10-18 00:00:00 Standard C…
## 5 Cassa… US-2015-1… 2015-10-11 00:00:00 2015-10-18 00:00:00 Standard C…
## 6 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
## 7 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
## 8 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
## 9 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
## 10 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
## # … with 9,984 more rows, and 16 more variables: `Customer ID` <chr>,
## # `Customer Name` <chr>, Segment <chr>, Country <chr>, City <chr>,
## # State <chr>, `Postal Code` <dbl>, Region <chr>, `Product ID` <chr>,
## # Category <chr>, `Sub-Category` <chr>, `Product Name` <chr>,
## # Sales <dbl>, Quantity <dbl>, Discount <dbl>, Profit <dbl>
```

---

---

# Excel interface

What can XLConnect do?

- Create/load Excel workbooks

- Add/read data from Excel

- Format worksheets (some limitations)

- Include images

- And much more... see [here](https://cran.r-project.org/web/packages/XLConnect/XLConnect.pdf)

All programatically!

---

# Make a template

---

# Create cell styles

---

# Load/create workbook and styles

```r
wb <- loadWorkbook("template.xlsx")
```

```r
date_cellstyle <- getCellStyle(wb, "date")
num_comma_cellstyle <- getCellStyle(wb, "number_comma")
dec_cellstyle <- getCellStyle(wb, "decimal")

setStyleAction(wb, XLC$STYLE_ACTION.NONE)
```

---

# Write data/format data

```r
writeWorksheet(wb, data = data,
               sheet_name,
               startRow = 2,
               header = FALSE)

walk(
  2:(row_count + 1),
  ~ setCellStyle(
    wb,
    sheet = sheet_name,
    row = .x,
    col = 2:3,
    cellstyle = date_cellstyle
  )
)

walk(
  2:(row_count + 1),
  ~ setCellStyle(
    wb,
    sheet = sheet_name,
    row = .x,
    col = c(sales, discount, profit),
    cellstyle = num_comma_cellstyle
  )
)
```

---

# Add a plot to Excel

```r
createName(wb, "plot", formula = glue("Summary!{idx2cref(c(1, 6))}") %>% as.character())

p <- data %>%
 group_by(Segment, Category) %>% 
 summarise_if(is.numeric, ~ sum(.x, na.rm = TRUE)) %>% 
 gather(key = key, value = value, -c("Segment", "Category", "Postal Code")) %>% 
 filter(!key %in% c("Quantity", "Discount")) %>% 
 ggplot(aes(Category, value, fill = key)) +
 geom_col(position = "dodge") +
 scale_fill_brewer(name = "Metric", type = "qual", palette = 2) +
 scale_y_continuous(label = scales::comma) +
 facet_wrap( ~ Segment) +
 ggthemes::theme_tufte() +
 theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
 labs(title = "Profit/Sales by Segment/Category",
 y = NULL)

ggsave(p, filename = "segment-category.png", width = 4, height = 3)

addImage(wb, filename = "segment-category.png", name = "plot", originalSize = TRUE)

saveWorkbook(wb, file = file_name)
```

---

# Functional

```r
make_plot()

style()

build_excel()
```

```r
walk(sales_split, function(split) {
  build_excel(split, "reports")
})
```

```r
plan(multiprocess)
furrr::future_map(sales_split, ~ build_excel(.x, "reports"))
```

---

---

---

---

# Compose a message

```r
subject = "Sales State Coverage Report"

message <- paste0(
 '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
 <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
 <title>Sales Coverage</title>
 <style type="text/css">
 </style>
</head>
<body>
 Hello,

Attached is sales data for your state coverage.
A visualization and summary are available on the second tab.
Let us know if you have questions.
Kind regards,
Analysis Group,
</body>
</html>'
)
```

---

# mailR/sendmailR

```r
send.mail(
  from = "user@domain.com",
  to = "user@domain.com",
  subject = subject,
  body = message,
  html = TRUE,
  smtp = list(
    host.name = "smtp.gmail.com",
    port = 465,
    user.name = rstudioapi::askForPassword(),
    passwd = rstudioapi::askForPassword(),
    ssl = TRUE
  ),
  attach.files = "file_path",
  authenticate = TRUE,
  send = TRUE
)
```

---

# Nice to know

- Java/rJava are required to use XLConnect and mailR/sendmailR.

- Latest version of java: https://java.com/en/download/.

- If you have trouble installing Java on Mac, see https://www.chrisjmendez.com/2018/11/16/installing-xlconnect-and-rjava-on-macos/.

- It's typically required you set your JAVA_HOME, such as `Sys.setenv(JAVA_HOME = "C:\\Program Files\\Java\\jdk1.8.0_102")`. The path is relative to your machine, so you must locate your directory and version of Java.

- Useful: `vignette("XLConnect")`.

- XLConnect runs on memory until `saveWorkbook()` is called. If you experience out-of-memory errors, try allocating more memory with `options(java.parameters = "-Xmx1024m")`. See vignette above.