Create and Format a Google Sheet Within R: Solutions

####################
#                  #
#    Exercise 1    #
#                  #
####################
library(googlesheets)
suppressMessages(library(dplyr))

####################
#                  #
#    Exercise 2    #
#                  #
####################
gs_auth(new_user = TRUE)

####################
#                  #
#    Exercise 3    #
#                  #
####################
# Assignment is important to be able to continue working with sheet
iris_r <- gs_new(title = "iris_r", ws = "test1")

####################
#                  #
#    Exercise 4    #
#                  #
####################
iris_r <- gs_edit_cells(ss = iris_r, ws = "test1", input = iris)

####################
#                  #
#    Exercise 5    #
#                  #
####################
iris_r <- gs_ws_new(ss = iris_r, ws_title = "test2", input = iris)

####################
#                  #
#    Exercise 6    #
#                  #
####################
iris_r <- gs_ws_new(ss = iris_r, ws_title = "test3")
iris_r <- gs_edit_cells(
  ss = iris_r,
  ws = "test3",
  input = mtcars[1, ],
  byrow = TRUE
)
iris_r <- gs_add_row(ss = iris_r, ws = "test3", input = mtcars[2:5, ])

####################
#                  #
#    Exercise 7    #
#                  #
####################
url <- '"https://www.r-exercises.com/2017/12/11/easy-web-scraping-with-rvest/"'
nws <- "My favorite R-ex"
iris_r <- gs_ws_new(ss = iris_r, ws_title = nws)
iris_r <- gs_edit_cells(
  ss = iris_r, ws = nws,
  input = sprintf('=HYPERLINK(%s, "this")', url)
)

####################
#                  #
#    Exercise 8    #
#                  #
####################
nws <- "test1b"
iris_r <- gs_ws_new(ss = iris_r, ws_title = nws)
iris_r <- gs_edit_cells(
  ss = iris_r, ws = nws,
  input = sprintf("=test1!%s1", LETTERS[1:4]),
  anchor = "A2"
)

iris_r <- gs_edit_cells(
  ss = iris_r, ws = nws,
  input = "Average", anchor = "B1"
)


iris_r <- gs_edit_cells(
  ss = iris_r, ws = nws,
  input = sprintf("=AVERAGE('test1'!%1$s2:%1$s151)", LETTERS[1:4]),
  anchor = "B2"
)

####################
#                  #
#    Exercise 9    #
#                  #
####################
url <- iris_r[["browser_url"]]

####################
#                  #
#    Exercise 10   #
#                  #
####################
gs_download(iris_r, to = paste0("c:/temp/iris_r", Sys.Date(), ".xlsx"))