Spreadsheets are still a fairly popular tool for working with data, and among the various spreadsheet processors, Google Sheets are the most popular. Firstly, this is a free tool, and secondly, the functionality of Google Sheets is quite wide, and they provide you with the opportunity to access data online.

In this article, we will understand how to work with the Google Sheets API v4 in the programming language R using the package googlesheets4, or rather:

• How to get authorized for working with Google Sheets by API;
• Consider the main functions of the package;
• Let's look at some code examples for reading data, creating new tables and performing other manipulations with Google Sheets by API.

This article will also help users of an obsolete package googlesheetsmigrate to a new one googlesheets4.

For those who are too lazy to read the article, here is a link to a 10-minute, Russian-language video lesson on YouTube. The rest welcome under cat.

Content

, R, telegram youtube . R.

1. googlesheet4
2. googlesheets googlesheets4
3. googlesheets4
4. 
   4.1.
   4.2.
   4.3.
   4.4.
5. googlesheets4
6. API Google R
   6.1. Google
   6.2. Google API
   6.3. Google
   6.4.
   6.5. Google Google

googlesheet4

R, Google Sheets API googlesheet. , — " googlesheets4 ?".

, googlesheets , Google Sheets API V3. Google , 3 API 3 2020 .

, googlesheets, 3 googlesheets4, .

googlesheets googlesheets4

googlesheets googlesheets4 .

googlesheets4

API Google , , googlesheets4, RStudio.

, dev GitHub.

#   devtools
if ( !require(devtools) ) install.packages("devtools")
#   googlesheets4
devtools::install_github("tidyverse/googlesheets4")

#  
library(googlesheets4)

googlesheets4 3 :

1. gs4_auth().
2. .
3. .

gs4_auth() .

, , 90% . , .

Gogle Cloud, .

?

:

1. Google Cloud Console , . .
2. « » > «API » > « ».
3. « » > « OAuth».
4. .
5. id secret .
6. JSON , id .

JSON

ID

Google Console, " OAuth", email, .

, Google Sheets API, API.

RStudio, .

JSON :

#  
library(googlesheets4)

#    JSON ,   
gs4_auth_configure(path = "C:/path/to/app_credential.json")

#  
gs4_auth(email = "your_email@gmail.com")

ID :

#  
library(googlesheets4)

#    JSON ,   
httr::oauth_app(appname = "app_name", 
                  key    = "00000000-abcdefghk.apps.googleusercontent.com", 
                  secret = "qwertyuiopasdf")

#  
gs4_auth(email = "your_email@gmail.com")

gs4_auth(), , . RStudio .

, :

1. Google Cloud, .
2. Google Sheets API, .
3. .
4. ( ) "".
   
5. , "".
6. " " > JSON > .
7. JSON , .
8. "".

, .

Google . , .

1. Google .
2. " ".
3. .

:

#  
library(googlesheets4)

#     
gs4_auth(path = "C:/path/to/service_credential.json")

, , Google , API.

:

1. .
2. Google . Google .

. , - JSON , .

googlesheets4

googlesheets4.

• gs4_auth() — ;
• as_sheets_id() — Google , URL Google ;
• range_read() — Google ;
• gs4_create() — Google ;
• sheets_write() — Google , . ;
• sheets_append() — ;
• sheet_add() — Google ;
• sheet_delete() — Google ;
• sheet_names() — Google ;
• sheets_browse() — Google .

, Google .

API Google R

Google .

- . Google R iris, mtcars ToothGrowth.

Google

Google .

, , .

as_sheet_id(), URL Google .

URL: https://docs.google.com/spreadsheets/d/1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE/
: 1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE

URL , /d/ .

googlesheets4 range_read().

Google

#  
library(googlesheets4)

#      
gs4_auth(email = "your_mail@gmail.com")

#    
my_dox <- as_sheets_id("1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE")
#     test_data
data   <- range_read(my_dox, sheet = "test_data")

.

data <- as_sheets_id("1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE") %>%
        range_read("test_data")

Google API

gs4_create() .

• name — Google ;
• locale — ;
• timeZone — ;
• sheets — , , , .

Google

#  
library(googlesheets4)

#      
gs4_auth(email = "your_email@gmail.com")

#  Google 
ss <- gs4_create(name = "my_new_dox", 
                    locale = "ru_RU",
                    sheets = list(mtcars = mtcars,
                                  iris   = head(iris)))

#     
sheets_browse(ss)

Google "my_new_dox", 2 : mtcars, iris.

Google

, Google , , ss.

, .

#     
sheets_write(ToothGrowth, ss, 
             sheet = "tooth_growth")

Writing to 'my_new_dox'
Writing to sheet "tooth_growth"

sheets_write() 3 :

• data — , .
• ss — Google , as_sheets_id() gs4_create().
• sheet — Google , .

— .

sheets_append(), 3, .

• data — .
• ss — Google , as_sheets_id() gs4_create().
• sheet — .

my_new_dox iris 6 , .

#     iris
sheets_append(data = iris[7:150,], ss, 
              sheet = "iris")

Writing to 'my_new_dox'
Appending 144 row(s) to 'iris'

Google Google

, googlesheets4 googledrive.

googledrive

install.packages("googledrive")

Google drive_mv(). drive_auth().

googledrive , .. gargle. drive_auth() googlesheets4 : gs4_auth(token = drive_token()).

Google URL . drive_mkdir().

as_id().

Google Google

#  
library(googlesheets4)
library(googledrive)

# 
##     ,    
## gs4_auth(email = "your_email@gmail.com")
## drive_auth(email = "your_email@gmail.com")

##      google drive,         google sheets
drive_auth(email = "your_email@gmail.com")
gs4_auth(token = drive_token())

#      
##      
ss     <- as_sheets_id("1BNrYUajVSR3wuGySY0ybXrqH3-Jjq-eIS5_f_a6kt_c")

##        Google 
folder <- as_id("1x94xChfZwSCPFzHvWqwk6HyF85_ASDFW")

#    
## folder <- drive_mkdir("my_folder")

#  Google    
drive_mv(file = ss, 
         path = folder)

File moved:
  * my_new_dox -> my_folder/my_new_dox

:

• YouTube googlesheets4 ()
• googlesheets4 (english)
• GitHub (english)
• CRAN (english)

googlesheets4 , Google Sheets API.

googlesheets4 . 2020 , .

, , R . , youtube , R.