<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/how-to-use-googlesheets-to-connect-r-to-google-sheets/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://datascienceplus.com/how-to-use-googlesheets-to-connect-r-to-google-sheets/"> R Programming – DataScience+</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Often I use R to handle large datasets, analyze the data and filter out the data I don’t need.</p>
<p>When all this is done, I usually use <code>write.csv() to print my data off and reopen it in Google Sheets.
<p>My workflow would look something like this:</p>
<pre>full_data &lt;- read.csv(&quot;some_dataset.csv&quot;)
#R analysis ending up with relevant_data
write.csv(relevant_data, &quot;relevant_data.csv&quot;)
#continue work in Google Sheets

However, there’s an R package that provides a bridge between your Google account and your R environment: <a rel="nofollow" href="https://github.com/jennybc/googlesheets" target="_blank">googlesheets</a>.

Using this package we can read data directly from Google, modify it and create new files within our Google Drive.

<h2>Step 1: Install googlesheets</h2>

<pre>install.packages(googlesheets)
library(googlesheets)</pre>
<h2>Step 2: Authenticate your Google account</h2>
<p>Before we can do anything we need to allow google sheets to access our account.<br>
We can do this by running this:</p>
<pre>gs_auth(new_user = TRUE)</pre>
<p>Have a browser open (Google Chrome worked for me) and it should open a new tab asking you to connect via an account:<br>
<a rel="nofollow" href="https://i0.wp.com/datascienceplus.com/wp-content/uploads/2017/12/googlesheets1.png?ssl=1" target="_blank"><img sizes="(max-width: 324px) 100vw, 324px" src="https://i0.wp.com/datascienceplus.com/wp-content/uploads/2017/12/googlesheets1.png?resize=324%2C124&ssl=1" alt="" width="324" class="wp-image-10170" data-recalc-dims="1" height="124"></a></p>
<p>Click on an account below this message and then ‘allow’ and it should take you to a page saying it has worked and to go back to R.<br>
<a rel="nofollow" href="https://i1.wp.com/datascienceplus.com/wp-content/uploads/2017/12/rfj-googlesheets.png?ssl=1" target="_blank"><img sizes="(max-width: 384px) 100vw, 384px" src="https://i2.wp.com/datascienceplus.com/wp-content/uploads/2017/12/rfj-googlesheets-384x490.png?resize=384%2C490&ssl=1" alt="" width="384" class="wp-image-10171" data-recalc-dims="1" height="490"></a></p>
<p>You can rerun this command any time you want to change accounts.</p>
<p>Sometimes if you don’t use the token for a while it will run out and you will have to refresh it, which it will <a rel="nofollow" href="https://github.com/jennybc/googlesheets/blob/master/vignettes/managing-auth-tokens.md" target="_blank">initiate automatically</a> if you run a command that requires you to connect to the Google API (i.e. any of the specialised googlesheets functions).</p>
<h2>Step 3: See what’s in your Google Account</h2>
<p>Calling the function <strong>gs_ls()</strong> will show you spreadsheets in your account.</p>
<pre>
gs_ls()
<em># A tibble: 15 x 10
sheet_title author perm version updated sheet_key
1 for googlesheets rforjournali… rw new 2017-12-11 09:44:54 1Y0WCfTW…
2 Avon and Somerset Septe… rforjournali… rw new 2017-11-19 12:46:55 1TfC5Fs6…
3 Mid year 2015 UK popula… rforjournali… rw new 2017-10-25 21:19:52 1Vqg560s…
4 Cleveland 2016-09 rforjournali… rw new 2016-11-26 10:18:16 19xBr8nU…
5 Rankings of US presiden… rforjournali… rw new 2016-11-08 04:39:55 11PZxq7y…
6 Tennis #1s rforjournali… rw new 2016-11-06 20:04:42 1Riz8GRs…
7 Young persons railcard rob.grant rw new 2016-11-06 13:05:55 1XZsjJxu…
8 Copy of Young persons r… rforjournali… rw new 2016-11-05 18:14:38 1oUpRS-D…
9 defective rforjournali… rw new 2016-11-05 11:40:30 1jWZBILC…
10 Asylum rforjournali… rw new 2016-10-27 19:04:05 1CRMl2_1…
11 Buses rforjournali… rw new 2016-10-24 20:07:41 1qy9Z-sn…
12 Untitled spreadsheet rforjournali… rw new 2016-10-24 19:22:42 1_f_FI5n…
13 Population rforjournali… rw new 2016-10-24 18:29:17 1rrOQuV5…
14 Drugs rforjournali… rw new 2016-10-18 21:37:29 1UTsnGM6…
15 Food rforjournali… rw new 2016-10-15 13:24:30 1aWEAPR4…</em></pre>
<h2>Step 4: Read a spreadsheet</h2>
<p>I am going to select the first spreadsheet ‘for googlesheets’ by its title. It’s a selection of 50 random numbers between 0 and 1 (you can recreate this function with <code>runif()</code> in R).</p>
<pre>for_gs &lt;- gs_title(&quot;for googlesheets&quot;)</pre>
<p>You can also locate the sheet by the key (the letters, numbers and characters after the /d/ in the URL) for the same result</p>
<pre>for_gs &lt;- gs_key(&quot;your_key_here&quot;)</pre>
<p>This gives us a list, which we can turn into a data frame using the <code>gs_read()</code> command.</p>
<pre>for_gs_sheet &lt;- gs_read(for_gs)
str(for_gs_sheet)
<em>Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 50 obs. of 2 variables:
$ Number.x: num 0.4696 0.1587 0.0949 0.1823 0.0885 ...
$ Number.y: num 0.67551 0.7041 0.00167 0.51302 0.20114 ...
- attr(*, &quot;spec&quot;)=List of 2
..$ cols :List of 2
.. ..$ Number.x: list()
.. .. ..- attr(*, &quot;class&quot;)= chr &quot;collector_double&quot; &quot;collector&quot;
.. ..$ Number.y: list()
.. .. ..- attr(*, &quot;class&quot;)= chr &quot;collector_double&quot; &quot;collector&quot;
..$ default: list()
.. ..- attr(*, &quot;class&quot;)= chr &quot;collector_guess&quot; &quot;collector&quot;
..- attr(*, &quot;class&quot;)= chr &quot;col_spec&quot;</em></pre>
<h2>Step 5: Modify the spreadsheet</h2>
<p>Next up, we modify our spreadsheet using the <code>gs_edit_cells() function.
<p>This function has several arguments that we need to employ to edit our spreadsheet properly.</p>
<pre>gs_edit_cells(for_gs, ws = &quot;Sheet1&quot;, anchor = &quot;A2&quot;, input = c(1,2), byrow = TRUE)</pre>
<p>The <code>ws argument refers to the sheet name in the spreadsheet. The <code>anchor</code> argument refers to the cell from which the modification will begin. In my example, I am editing two cells, where the first one will be the anchor cell A2. The <code>byrow</code> argument indicates that the modification will apply horizontally (change to FALSE for vertical editing).
<p>Note that this won’t change our data frame <code>for_gs_sheet</code> that is based on this spreadsheet; just the spreadsheet itself.</p>
<p>Cell A2 now has a value of 1. A3 is 2.</p>
<h2>Step 6: Create a Google Sheets file using R</h2>
<p>We can create new spreadsheets using this package using <code>gs_new()</code>.</p>
<p>We’ll use the <code>mtcars</code> dataset as a test:</p>
<pre>gs_new(title = &quot;mtcars&quot;, ws_title = &quot;first_sheet&quot;, input = mtcars)</pre>
<p><a rel="nofollow" href="https://i2.wp.com/datascienceplus.com/wp-content/uploads/2017/12/mtcars-spreadsheet-1.png?ssl=1" target="_blank"><img sizes="(max-width: 490px) 100vw, 490px" src="https://i1.wp.com/datascienceplus.com/wp-content/uploads/2017/12/mtcars-spreadsheet-1-490x308.png?w=450&ssl=1" alt="" class="wp-image-10172" data-recalc-dims="1"></a></p>
<p>It worked, except it didn’t include the rownames, which contains the cars.</p>
<p>That doesn’t matter, we can add them using <code>gs_edit_cells()</code>, changing the <code>byrow</code> argument to FALSE this time.</p>
<pre>#register the new mtcars sheet in R
gs_new(title = &quot;mtcars&quot;, ws_title = &quot;first_sheet&quot;, input = mtcars)

#insert the rownames vertically in column L
gs_edit_cells(mtcars_sheet, ws = &quot;first_sheet&quot;, anchor = &quot;L2&quot;, input = rownames(mtcars), byrow = FALSE)</pre>
<h2>Final thoughts</h2>
<p>That was a quick overview of the most basic functions of the google sheets package.</p>
<p>This is a really useful package. A lot of my work involves reading data in Google Sheets either before or after using R.</p>
<p>Googlesheets means I won’t have to bother with <code>read.csv()</code> or <code>write.csv()</code> as much in the future, saving me time.</p>
<p>So thanks to <a rel="nofollow" href="https://github.com/jennybc" target="_blank">Jenny Bryan</a> for creating it!</p>
<ul></ul>
<p><strong>
<p>Related Post</p>
<p></p></strong>
<ol>
<li><a rel="nofollow" href="https://datascienceplus.com/dataframes-vs-rdds-in-spark-part-1/" title="Permanent Link toDataFrames Vs RDDs in Spark – Part 1" target="_blank">DataFrames Vs RDDs in Spark – Part 1</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/parallel-vectorized-operations/" title="Permanent Link toParallel  Operations" target="_blank">Parallel Operations</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/r-markdown-how-to-number-and-reference-tables/" title="Permanent Link toR Markdown: How to number and reference tables" target="_blank">R Markdown: How to number and reference tables</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/rdbl-manipulate-data-in-database-with-r-code-only/" title="Permanent Link toRDBL – manipulate data in-database with R code only" target="_blank">RDBL – manipulate data in-database with R code only</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/r-markdown-how-to-format-tables-and-figures-in-docx-files/" title="Permanent Link toR Markdown: How to format tables and figures in .docx files" target="_blank">R Markdown: How to format tables and figures in .docx files</a></li>
</ol>

				
		
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://datascienceplus.com/how-to-use-googlesheets-to-connect-r-to-google-sheets/"> R Programming – DataScience+</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/-1-EiNnu2S4" height="1"></a></div></code></code></pre></code>