How to Connect R to Google Sheets Using googlesheets4

Manipulating data in sheets can be a tedious task, but if you have at least a bit of a programmer inside you, you can make your work with Google sheets much easier. How - you ask? By connecting R to Google sheets using the <strong>googlesheets4</strong> package provided by <a href="https://www.tidyverse.org/" target="_blank" rel="noopener noreferrer">tidyverse</a>. This article will show you how to easily connect to your Google account, download data from Google spreadsheets to the dataframe in R, create a new sheet, as well as add new records, overwrite data, and delete individual columns. <ul><li><a href="#connection" rel="noopener noreferrer">Connect R to Google Sheets</a></li><li><a href="#read" rel="noopener noreferrer">Read Google sheets with R</a></li><li><a href="#create" rel="noopener noreferrer">Create Google sheets with R</a></li><li><a href="#write" rel="noopener noreferrer">Write Google sheets with R</a></li><li><a href="#conclusion" rel="noopener noreferrer">Summary</a></li></ul> Googlesheets4 is the tidyverse library that allows integrating R workspaces with Google Sheets to enable data manipulation (reading, creating, and writing) in the R environment. As the official <a href="https://googlesheets4.tidyverse.org/index.html" target="_blank" rel="noopener noreferrer">googlesheets4 documentation</a> states, it is a reboot of a previous package called <strong>googlesheets</strong>. The current package connects to the corresponding, most modern version of the Sheets API (<b>v4</b>), and improves on utilizing the shared components of some other tidyverse packages, e.g. authorization using gargle, or ‘whole file’ operations using <strong>googledrive</strong>. <blockquote><strong>Are you limiting your business with Excel? <a href="https://appsilon.com/switch-from-excel-to-r-shiny/" target="_blank" rel="noopener noreferrer">Switch from Excel to R Shiny</a> and build better business intelligence. </strong></blockquote> With <strong>googlesheets4</strong> you can: <ul><li style="font-weight: 400;" aria-level="1">read data from an existing Google spreadsheet</li><li style="font-weight: 400;" aria-level="1">create a new Google spreadsheet</li><li style="font-weight: 400;" aria-level="1">overwrite or add new data in an existing Google spreadsheet</li></ul> <h2 id="connection">Connect R to Google Sheets</h2> To start with, let’s try connecting our brand new R workspace to <strong>googlesheets4</strong>: <script src="https://gist.github.com/MicahAppsilon/377b5633d11e82ff9a5330d1d5ef30f8.js"></script> The command above should prompt the installation process, but it can also throw errors if you have some outdated packages in your environment (e.g. the incompatible version of ‘<strong>rlang</strong>’). Update required packages, and rerun the installation process. You may need to restart the R session afterward. &nbsp; <img class="size-full wp-image-11826 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f6e6bd9d78607d4b4f1_restart-R-session.webp" alt="Restart R session" width="258" height="282" /> You may be asked if it's OK to cache OAuth access credentials in the folder <strong>~/Library/Caches/gargle</strong> between R sessions. In that case, select '<strong>Yes</strong>'. Load the library into the environment: <script src="https://gist.github.com/MicahAppsilon/47514686529cd4ac58416a7b5cf6616d.js"></script> Verify if there are any authorized users using <strong>gs4_auth()</strong>. You can either select a user from a list or create a new token for a different Google account by entering ‘0’. Press <strong>Esc/Ctrl + C</strong> to cancel. Tokens are valid in the current R session. Let’s connect a new user by entering ‘<strong>0</strong>’ in the command line. It prompts a browser to open a list of your available Google accounts.  Select an account, and agree for the app to impersonate you. This means seeing, editing, creating/deleting, uploading, downloading, organizing, sharing/unsharing your Google spreadsheets: &nbsp; <img class="size-full wp-image-11808 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f6fe4bb5dd95328128f_google-sign-ins.webp" alt="" width="819" height="665" /> You should see the following message in your browser afterward: “<strong>Authentication complete. Please close this page and return to R</strong>”. <blockquote><strong>Excel is obsolete for enterprises. Take your business to the next level with these 2<a href="https://appsilon.com/excel-is-obsolete-here-are-the-top-2-alternatives-from-r-and-python/" target="_blank" rel="noopener noreferrer"> excel alternatives</a>. </strong></blockquote> <b>Note:</b> If you do not need to access any private Google spreadsheets use <strong>gs4_deauth()</strong>. <h2 id="read">Read Google Spreadsheet in R</h2> Below we have a Google spreadsheet built using the Microsoft AdventureWorks sample database. The goal is to transfer it to R. <img class="size-full wp-image-11804 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f71817460dff13766c7_example-data.webp" alt="" width="819" height="576" /> &nbsp; You can connect to a particular Google spreadsheet by using read_sheet(). <p class="language-r">Example:</p> <script src="https://gist.github.com/MicahAppsilon/6851bc4e8778f0acbf21806e2a67e437.js"></script> As you can see, R recognized the dataset together with its headings: <img class="alignnone size-full wp-image-11824" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f72cb8cd72a3d4515bc_read_sheet.webp" alt="" width="720" height="304" /> &nbsp; <b>Tip:</b> A Google spreadsheet can also be loaded using <strong>range_read()</strong>. <h2 id="create">Using R to Create Google Sheet</h2> Creating a new Google spreadsheet requires using gs4.create(). If you do not pass the spreadsheet name as a parameter, the library is going to choose a random funny-sounding spreadsheet name for you: <script src="https://gist.github.com/MicahAppsilon/7b09e60f7f67f055b9e3361d9b0da289.js"></script> You can also specify the name of the sheet that you want to create, e.g: <script src="https://gist.github.com/MicahAppsilon/9a0a2e99ae785aadffd703d0b14a8b55.js"></script> As you can see, all newly created sheets are visible among other spreadsheets that belong to the user: <img class="alignnone size-full wp-image-11806 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f74369f5360b2ade89f_file-names.webp" alt="" width="1139" height="267" /> &nbsp; There is an option to populate the spreadsheet with data while creating it. Let’s create a new Google spreadsheet ‘test_sheet2’ that is going to be a copy of  ‘Revenue by Country, State and Category’: <script src="https://gist.github.com/MicahAppsilon/354ac7370ae75f77e0029fb0279e80e5.js"></script> It is possible to define custom metadata if for some reason the default settings are not going to support the content properly: <script src="https://gist.github.com/MicahAppsilon/0fd9994c9c72a5a610bd250c0dbc7199.js"></script> This is how you can name your individual sheets: <script src="https://gist.github.com/MicahAppsilon/ec5e9a37293b875f1da3378ee19d289c.js"></script> &nbsp; <img class="alignnone size-full wp-image-11830 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f7487a667cfae8fc229_test_sheet4.webp" alt="" width="498" height="280" /> <b>Note:</b> It's not possible to delete the entire spreadsheets using gs4. In that case, you either need to manually delete or trash the files using googledrive library. An individual sheet can be removed using <strong>sheet_delete</strong>(). <blockquote><strong>Better business decisions requires better BI tools. <a href="https://appsilon.com/top-3-bi-tools/" target="_blank" rel="noopener noreferrer">Compare these top 3 BI tools</a>. </strong></blockquote> <h2 id="write">Using R to Write in Google Sheet</h2> The <strong>sheet_write()</strong> function is very similar to gs4_create(). It can also be used to copy data from one Google spreadsheet to another, e.g.: <script src="https://gist.github.com/MicahAppsilon/be831583debca9a885765a11580e2294.js"></script> The script created a new sheet, ‘matricidal-cuttlefish’, that contains the same data as ‘Revenue by Country, State, and Category’.  We can now add new sheets and fill them with records. Let’s add another sheet ‘Products’ to ‘matricidal-cuttlefish’ and populate it with data imported to R from a .csv file. <img class="alignnone size-full wp-image-11822 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f7548b83fcd2e5630d4_products.webp" alt="" width="874" height="469" /> &nbsp; <script src="https://gist.github.com/MicahAppsilon/23b9834b2d88e34e1718c282c275e49b.js"></script> &nbsp; <img class="alignnone size-full wp-image-11820 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f76e4bb5dd953281735_productID.webp" alt="" width="718" height="476" /> <b>Tip:</b> You can trigger opening the file in the browser from the console using <strong>gs4_browse()</strong>. A new column that informs analysts about discontinued products was added to Products.csv. Let’s add this column to our Products page. We are going to start by creating a single column data frame that holds ‘SellEndDate’ data: <script src="https://gist.github.com/MicahAppsilon/bf7fdde9a47dbdbd7653bc77da760380.js"></script> Then, we add this duplicated column to a spreadsheet: <script src="https://gist.github.com/MicahAppsilon/9cafbc6a883c6a6c2314268bf1204a62.js"></script> &nbsp; &nbsp; &nbsp; <img class="size-full wp-image-11828 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f7792a26c843b634396_Sell-End-Date.webp" alt="" width="759" height="463" /> We can clear the values in this column by using <strong>range_clear()</strong> or <strong>range_flood()</strong>: <script src="https://gist.github.com/MicahAppsilon/90231634c9a4a4a7797ee7ea60bc18d1.js"></script> &nbsp; <img class="size-full wp-image-11812 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f782ae89d4dce380809_image-10.webp" alt="" width="852" height="407" /> The difference between these two functions is that <strong>range_flood() </strong>may include additional context, e.g. the following request will overwrite all values: <script src="https://gist.github.com/MicahAppsilon/f3f604cd0b61deeb60dbb09044446296.js"></script> &nbsp; &nbsp; <img class="size-full wp-image-11814 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f7a3be1a2252421de3b_image-11.webp" alt="" width="759" height="488" /> This will get rid of an entire column: <script src="https://gist.github.com/MicahAppsilon/0d936365d81dd5aac1da48a0ac5dc837.js"></script> Some new products are supposed to be added to the system, so the ‘Products’ page in the ‘matricidal-cuttlefish’ file has to be updated. See the ProductsNew.csv file that contains information about new products: <script src="https://gist.github.com/MicahAppsilon/98fd69154b2b45c2a0fa2680c7b40bcc.js"></script> <img class="alignnone size-full wp-image-11818 aligncenter" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f7b6bdc8f59ff87225f_new-products.webp" alt="" width="564" height="264" /> Let’s add these new products to the list of already existing products: <script src="https://gist.github.com/MicahAppsilon/96c794db5c859fe16455be60b84b5e46.js"></script> As you can see, the new records were successfully added at the end of the file: <img class="alignnone size-full wp-image-11816" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f7ca23ca51ee6ae38a8_image-13.webp" alt="" width="715" height="469" /> &nbsp; Does your team need more from Tableau? Discover how to merge <a href="https://appsilon.com/r-shiny-shinytableau/" target="_blank" rel="noopener noreferrer"> Tableau and R Shiny with Rstudio's shinytableau R package</a>. <h2 id="conclusion">Summary</h2> To sum up, googlesheets4 is a pretty powerful library that allows a user to automate some of the data manipulation activities in Google spreadsheets, like creating new sheets or adding new columns/records. At the same time, it also enables reading data directly from Google spreadsheets to perform analyses in R.  This package also has well-written documentation supported by examples of use. Although it's sometimes difficult to understand the range of operations that the user can perform with googlesheets4, it's a nice option for novice users who would like to expand their data analysis skills.