googlesheets in r (test)

gistfile1.txt

library(googlesheets)
library(tidyverse)

# Each Google Spreadsheet has a limit of 400,000 cells, with a maximum of 256 columns per sheet. 
# There are also other limitations: Number of Formulas: 40,000 cells containing formulas. 
# Number of Tabs: 200 sheets per workbook

##################################################
# gs_auth로 계정 인증 받기
##################################################
gs_auth(new_user = TRUE) # 오른쪽 Files section에 .httr-oauth와 .gitignore 생성

##################################################
# gs_ls()로 구글시트 리스트보기
##################################################
my_sheets <- gs_ls(regex = "^업무추진비") # regex 파라미터 정규표현식 활용 가능
my_sheets$sheet_key # 해당 시트키 확인

##################################################
# gs_title()로 특정 시트 선택하기
##################################################
df_sample <- gs_title("업무추진비_부산광역시 강서구")

# list에서 확인할 수 있는 요소들
df_sample$browser_url # 공유 url
df_sample$n_ws # 시트 갯수
df_sample$sheet_title # 구글시트명

df_sample %>% 
  gs_browse(ws = 1) # 시트별로 볼 수 있음

##################################################
# gs_read() 특정 시트 R로 가져오기 
##################################################
df_sample_edit <- df_sample %>% 
  gs_read(ws = 1)

glimpse(df_sample_edit)

#범위 설정 가능 / 가져와서 select 가능
df_sample %>%
  gs_read(ws = 1, 
          range = cell_cols(1:5))

##################################################
# gs_copy() 특정 시트 복사 
##################################################
dup_df <- df_sample %>% 
  gs_copy(to = "업무추진비_부산광역시 강서구_duplicated")

dup_df %>%
  gs_delete() # 삭제

##################################################
# gs_download() 특정 시트 다운로드
##################################################
df_sample %>% 
  gs_download(ws = 1, 
              to = "업무추진비_부산광역시 강서구.csv")

df_sample %>%
  gs_download(ws = 1, 
              to = "업무추진비_부산광역시 강서구.xlsx")

df_sample %>% 
  gs_download(ws = 1, 
              to = "업무추진비_부산광역시 강서구.pdf")

##################################################
# gs_edit_cells() 특정값 수정 
##################################################
df_sample %>% 
  gs_edit_cells(ws = 1, 
                anchor = "A2", 
                input = "부산광역시 동래구")

##################################################
# gs_new() R에서 작업한 걸 구글시트로 새롭게 생성
##################################################
df_new <- gs_new(title = "test_업무추진비2", 
                 input = df_sample_edit, 
                 trim = TRUE)

##################################################
# gs_upload() 컴퓨터(local) 파일을 Googledrive로
##################################################
gs_upload("test_업무추진비_부산광역시_강서구.csv")