Import and Export into Googlesheet and AWS using R

Import and Export Googlesheet in R using GoogleSheet4 package
Recently I was working to sync some data from R to AWS S3 bucket and Googlesheet.

Problem Statement – R has retired the package googlesheet package and now you don’t have to use google authenticator each time you sync a sheet in R for any reporting.
Import and Export Googlesheet and AWS S3 bucket

Import and Export Googlesheet and AWS S3 bucket

Solution – Use googlesheet4 package

Problem 2 – There is a very little documentation for googlesheet4 package

Lets understand Import and Export Googlesheet and AWS S3 bucket in R using GoogleSheet4 package.

Here is a tried and tested solution from my side 🙂

Below are the packages you need for Google sheet

install.packages("googlesheets4")
library(googlesheets4)
library(googlesheets)
install.packages("RCurl")
library(RCurl)

Master_Sheet <- read_sheet("https://docs.google.com/spreadsheets/******",sheet = 'All')

url <- getURL(Master_Sheet)

Once you read the sheet, you can do your computation. Make sure that the final set which you want to upload in your Google sheet or AWS S3 Bucket is in a data frame

Creating my final set
Marketing_Cost <- rbind(A,B,S,C,V,A)

Rule 1
Change everything into a character, this is where you might get frustrated

Marketing_Campaign_Cost <- apply(Marketing_Campaign_Cost,2,as.character)

Rule 2
Convert the above in a data frame

MCC <- data.frame(Marketing_Campaign_Cost)

Rule 3
If you want to create a new sheet every time you run a code, then use
gs4_create
But rarely you would do so, most of the time you will be updating a sheet

xx <- write_sheet(
MCC,
ss = "https://docs.google.com/spreadsheets/d/TargetsheetLink",
sheet = "Global")

The above will write your data frame in your desired Googlesheet.
You can also write it in your local

write.csv(MCC,file=”/Users/oyo/abc.csv”)

What about AWS?

library(aws.s3)
Sys.setenv("AWS_ACCESS_KEY_ID" = "Your AWS Key ID","AWS_SECRET_ACCESS_KEY" = "Your AWS Secret Key Id")

s3write_using(MCC, FUN = write.csv,bucket = "Database Name", object = toString(paste("Link to folder/abc.csv",sep="")))

This way you will be able to import and export Googlesheet and AWS in R

Try to solve these questions, other members will evaluate your answer and provide sufficient support.

If you want any guidance or want to make a career in Data Science, then you can take a look on our proposed Data science path

Connect with us on Linkedin for latest updates and Data Science e-books giveaways- Nitin Kamal (Co-Founder, The Data Monk)

Our Linkedin Page for latest updates and giveaways- https://www.linkedin.com/company/14573769

Keep Learning!