<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/reading-data-from-google-sheets-into-r/"></div></div><div><a data-url="https://www.r-bloggers.com/reading-data-from-google-sheets-into-r/" data-counturl="https://www.r-bloggers.com/reading-data-from-google-sheets-into-r/" data-count="horizontal" data-text="Reading data from google sheets into R" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.opiniomics.org/reading-data-from-google-sheets-into-r/"> R – Opiniomics</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<p>Reading data from <a target="_blank" rel="nofollow" href="https://www.google.co.uk/sheets/about/">google sheets</a> into R is something you imagine should be really simple, but often is anything but. However, package <a target="_blank" rel="nofollow" href="https://github.com/jennybc/googlesheets">googlesheets</a> goes a long way to solving this problem.</p>
<p>Let’s crack on with an example.</p>
<p>First, install the software:</p>
<pre>
install.packages(&quot;googlesheets&quot;)
</pre>
<p>We then need an example sheet to work with, and I’m going to use one from Britain Elects:</p>
<blockquote data-lang="en" class="twitter-tweet"><p dir="ltr" lang="en">For the latest council by election results and polls, be they Westminster or <a target="_blank" rel="nofollow" href="https://twitter.com/hashtag/IndyRef?src=hash">#IndyRef</a>, see: <a target="_blank" rel="nofollow" href="https://t.co/dPyiwIqJ3O">https://t.co/dPyiwIqJ3O</a> <a target="_blank" rel="nofollow" href="https://t.co/mvyoCuIJgd">pic.twitter.com/mvyoCuIJgd</a></p>
<p>— Britain Elects (@britainelects) <a target="_blank" rel="nofollow" href="https://twitter.com/britainelects/status/766642002754699264">August 19, 2016</a></p>
</blockquote>
<p></p>
<p>So go <a target="_blank" rel="nofollow" href="https://docs.google.com/spreadsheets/d/1X85PgCqavImGZPGdbrEfQc6uzNP98KXwZcf-8JsKZto/edit#gid=1194771573">here</a> and add this to your own collection (yes you’ll need a google account!) using the little “Add to my drive” icon to the right of the title, just next to the star.</p>
<p>Right, let’s play around a bit!</p>
<pre>
# load package
library(googlesheets)

# which google sheets do you have access to?
# may ask you to authenticate in a browser!
gs_ls()

# get the Britain Elects google sheet
be &lt;- gs_title(&quot;Britain Elects / Public Opinion&quot;)
</pre>
<p>This should show you all of the sheets you have access to, and also select the one we want.</p>
<p>Now we can see which worksheets exist within the sheet:</p>
<pre>
# list worksheets
gs_ws_ls(be)
</pre>
<p>We can &quot;download&quot; one of the sheets using gs_read()</p>
<pre>
# get Westminster voting
west &lt;- gs_read(ss=be, ws = &quot;Westminster voting intentions&quot;, skip=1)

# convert to data.frame
wdf &lt;- as.data.frame(west)
</pre>
<p>And hey presto, we have the data. Simples!</p>
<p>Now let's plot it:</p>
<pre>
# reverse so that data are forward-sorted by time
wdf &lt;- wdf[2769:1,]

# treat all dates as a single point on x-axis
dates &lt;- 1:2769

# smooth parameter
fs &lt;- 0.04

# plot conservative
plot(lowess(dates, wdf$Con, f=fs), type=&quot;l&quot;, col=&quot;blue&quot;, lwd=5, ylim=c(0,65), xaxt=&quot;n&quot;, xlab=&quot;&quot;, ylab=&quot;%&quot;, main=&quot;Polls: Westminster voting intention&quot;)

# add labels
axis(side=1, at=dates[seq(1, 2769, by=40)], labels=paste(wdf$&quot;Fieldwork end date&quot;, wdf$Y)[seq(1, 2769, by=40)], las=2, cex.axis=0.8)

# plot labour and libdem
lines(lowess(dates, wdf$Lab, f=fs), col=&quot;red&quot;, lwd=5)
lines(lowess(dates, wdf$LDem, f=fs), col=&quot;orange&quot;, lwd=5)

# add UKIP, and we treat absent values as -50 for plotting purposes
ukip &lt;- wdf$UKIP
ukip[is.na(ukip)] &lt;- -50
lines(lowess(dates, ukip, f=fs), col=&quot;purple&quot;, lwd=5)

# add legend
legend(1, 65, legend=c(&quot;Con&quot;,&quot;Lab&quot;,&quot;LibDem&quot;,&quot;UKIP&quot;), fill=c(&quot;blue&quot;,&quot;red&quot;,&quot;orange&quot;,&quot;purple&quot;))
</pre>
<p><a target="_blank" rel="nofollow" href="http://www.opiniomics.org/wp-content/uploads/2016/11/westminster.png"><br>
<img sizes="(max-width: 1489px) 100vw, 1489px" alt="westminster" class="wp-image-1321" src="https://i1.wp.com/www.opiniomics.org/wp-content/uploads/2016/11/westminster.png?resize=456%2C257" data-recalc-dims="1"><br>
</a></p>
<p>
</div>
<p> </p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/reading-data-from-google-sheets-into-r/"></div></div><div><a data-url="https://www.r-bloggers.com/reading-data-from-google-sheets-into-r/" data-counturl="https://www.r-bloggers.com/reading-data-from-google-sheets-into-r/" data-count="vertical" data-text="Reading data from google sheets into R" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://www.opiniomics.org/reading-data-from-google-sheets-into-r/"> R – Opiniomics</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 title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/wCY61PSNw_Y"></a></div>