[SOLVED] In R using googlesheets4 how do you write a new spreadsheet to an existing workbook?

In R using googlesheets4 how do you write a new spreadsheet to an existing workbook?

First let me apologize for using Excel terms for Google Sheets. I want to grab data from a single sheet in a workbook, do something, then write a new sheet in the same workbook. For example;

library(googlesheets4)

pot <- read_sheet("https://docs.google.com/spreadsheets/d/1tw0tK0RQf1F4WXA5eo24wDjOR0jrqoRp91SI4x7YnqU/edit#gid=0")

pot.sum<-pot%>%
  group_by(Treat) %>%
  summarise(Mean = mean(Value))

Now I want want to write pot.sum to a new spreadsheet in the same workbook. I know the functions gs4_create() and write_sheet(), but I am missing how to direct where the new sheet is written. Thank you for any help you could provide.

Solution

The following will help you do it:

# Load packages

library(googlesheets4)

library(tidyverse)

link <- "https://docs.google.com/spreadsheets/d/1tw0tK0RQf1F4WXA5eo24wDjOR0jrqoRp91SI4x7YnqU"

pot <- read_sheet(link)

pot.sum <- pot %>%
  group_by(Treat) %>%
  summarise(Mean = mean(Value))


pot.sum %>% write_sheet(ss = link, sheet = "pot.sum")

Then you can check the sheet in the main Google Sheets, https://docs.google.com/spreadsheets/d/1tw0tK0RQf1F4WXA5eo24wDjOR0jrqoRp91SI4x7YnqU