All Projects → jennybc → Googlesheets

jennybc / Googlesheets

Licence: other
Google Spreadsheets R API

Programming Languages

r
7636 projects

Projects that are alternatives of or similar to Googlesheets

Googlesheets4
Google Spreadsheets R API (reboot of the googlesheets package)
Stars: ✭ 232 (-69.91%)
Mutual labels:  spreadsheet, google-sheets, google-drive
Pygsheets
Google Sheets Python API v4
Stars: ✭ 1,116 (+44.75%)
Mutual labels:  spreadsheet, google-sheets
Cuba
🇨🇺 Google Sheets + SQL = JSON
Stars: ✭ 45 (-94.16%)
Mutual labels:  spreadsheet, google-sheets
Robinhood Google Sheets
Robinhood Custom Functions for Google Sheets 📈
Stars: ✭ 117 (-84.82%)
Mutual labels:  spreadsheet, google-sheets
Gspread
Google Sheets Python API
Stars: ✭ 5,676 (+636.19%)
Mutual labels:  spreadsheet, google-sheets
Mintable
🍃 Automate your personal finances – for free, with no ads, and no data collection.
Stars: ✭ 849 (+10.12%)
Mutual labels:  spreadsheet, google-sheets
Importjson
Import JSON into Google Sheets, this library adds various ImportJSON functions to your spreadsheet
Stars: ✭ 1,705 (+121.14%)
Mutual labels:  spreadsheet, google-sheets
Google Sheet S3
Google Apps Script that publishes a Google Sheet to Amazon S3 as a JSON file. Auto-updates on edit & maintains data types. Creates an array of objects keyed by column header.
Stars: ✭ 81 (-89.49%)
Mutual labels:  spreadsheet, google-sheets
code
Google Apps Script - Code Snippets 👩🏻‍💻
Stars: ✭ 108 (-85.99%)
Mutual labels:  google-drive, google-sheets
HTML-templating-with-Google-Apps-Script
Use data from your spreadsheets to build a webpage or a client-side app ✨
Stars: ✭ 55 (-92.87%)
Mutual labels:  spreadsheet, google-sheets
BakingSheet
Easy datasheet management for C# and Unity. Supports Excel, Google Sheet, JSON and CSV format.
Stars: ✭ 144 (-81.32%)
Mutual labels:  spreadsheet, google-sheets
Drive Db
📊 Use Google Drive spreadsheets as a simple database
Stars: ✭ 782 (+1.43%)
Mutual labels:  spreadsheet, google-drive
Holysheet
A program to store arbitrary files in Google Sheets
Stars: ✭ 59 (-92.35%)
Mutual labels:  google-sheets, google-drive
Luckysheet
Luckysheet is an online spreadsheet like excel that is powerful, simple to configure, and completely open source.
Stars: ✭ 9,772 (+1167.44%)
Mutual labels:  spreadsheet, google-sheets
PasswordKeeper
Web-app to help you securely store your encrypted passwords in your Google Drive.
Stars: ✭ 43 (-94.42%)
Mutual labels:  google-drive, google-sheets
garmdown
Download Garmin Connect Data
Stars: ✭ 14 (-98.18%)
Mutual labels:  google-drive, google-sheets
lifebot
Use Google Sheets to log your life by texting it Emojis and pulling in data from Fitbit automatically.
Stars: ✭ 15 (-98.05%)
Mutual labels:  spreadsheet, google-sheets
Rclone
"rsync for cloud storage" - Google Drive, S3, Dropbox, Backblaze B2, One Drive, Swift, Hubic, Wasabi, Google Cloud Storage, Yandex Files
Stars: ✭ 30,541 (+3861.22%)
Mutual labels:  google-drive
Sqlitebiter
A CLI tool to convert CSV / Excel / HTML / JSON / Jupyter Notebook / LDJSON / LTSV / Markdown / SQLite / SSV / TSV / Google-Sheets to a SQLite database file.
Stars: ✭ 601 (-22.05%)
Mutual labels:  google-sheets
Swiftspreadsheet
Spreadsheet CollectionViewLayout in Swift. Fully customizable. 🔶
Stars: ✭ 590 (-23.48%)
Mutual labels:  spreadsheet

Build Status Coverage Status DOI CRAN version lifecycle


Google Sheets R API


googlesheets is going away fairly soon (in 2020)! It is not a good idea to write new code that uses it!

googlesheets (this package) wraps the Sheets v3 API, which is quite old and is gradually going offline, starting in March 2020. Full shutdown is planned for September 2020. When that happens, this R package will be of no use and will presumably be archived on CRAN in due course.

googlesheets4 is a new package that wraps the current Sheets v4 API. It is the successor to the googlesheets package. It is strongly advised to adopt googlesheets4 going forward. See the website for how to install googlesheets4 and to see basic usage:

https://googlesheets4.tidyverse.org


Access and manage Google spreadsheets from R with googlesheets.

Features:

  • Access a spreadsheet by its title, key or URL.
  • Extract data or edit data.
  • Create | delete | rename | copy | upload | download spreadsheets and worksheets.
  • Upload local Excel workbook into a Google Sheet and vice versa.

googlesheets is inspired by gspread, a Google Spreadsheets Python API.

The exuberant prose in this README is inspired by Tabletop.js: If you’ve ever wanted to get data in or out of a Google Spreadsheet from R without jumping through a thousand hoops, welcome home!

Install googlesheets

The released version is available on CRAN

install.packages("googlesheets")

googlesheets is no longer under active maintenance, development and support has shifted to:

  • googledrive, available on CRAN. This package can handle all “whole file” operations for documents on Google Drive, including Sheets. It can work with Team Drives, it can upload/download entire Sheets (with conversions to/from other formats, such as csv and xlsx), and it can upload new media to an existing Sheet ID.
  • googlesheets4, available on CRAN. This package wraps the Sheets API v4 and does “Sheets-aware” operations that involve concepts specific to Sheets, such as worksheets and cells. It is the successor to googlesheets.

Regard everything below here as legacy content.

Vignettes

GitHub versions:

Talks

Load googlesheets

googlesheets is designed for use with the %>% pipe operator and, to a lesser extent, the data-wrangling mentality of dplyr. This README uses both, but the examples in the help files emphasize usage with plain vanilla R, if that’s how you roll. googlesheets uses dplyr internally but does not require the user to do so. You can make the %>% pipe operator available in your own work by loading dplyr or magrittr.

library("googlesheets")
suppressPackageStartupMessages(library("dplyr"))

Function naming convention

To play nicely with tab completion, we use consistent prefixes:

  • gs_ = all functions in the package.
  • gs_ws_ = all functions that operate on worksheets or tabs within a spreadsheet.
  • gd_ = something to do with Google Drive, usually has a gs_ synonym, might one day migrate to a Drive client.

Quick demo

Here’s how to get a copy of a Gapminder-based Sheet we publish for practicing and follow along. You’ll be sent to the browser to authenticate yourself with Google at this point.

gs_gap() %>% 
  gs_copy(to = "Gapminder")
## or, if you don't use pipes
gs_copy(gs_gap(), to = "Gapminder")

Register a Sheet (in this case, by title):

gap <- gs_title("Gapminder")
#> Sheet successfully identified: "Gapminder"

Here’s a registered googlesheet object:

gap
#>                   Spreadsheet title: Gapminder
#>                  Spreadsheet author: gspreadr
#>   Date of googlesheets registration: 2018-06-28 20:31:39 GMT
#>     Date of last spreadsheet update: 2018-06-28 20:28:33 GMT
#>                          visibility: private
#>                         permissions: rw
#>                             version: new
#> 
#> Contains 5 worksheets:
#> (Title): (Nominal worksheet extent as rows x columns)
#> Africa: 625 x 6
#> Americas: 301 x 6
#> Asia: 397 x 6
#> Europe: 361 x 6
#> Oceania: 25 x 6
#> 
#> Key: 1vz6eeNH_rutBS2z6QtMq_rffRpqq3R_8Qevw7-vETC0
#> Browser URL: https://docs.google.com/spreadsheets/d/1vz6eeNH_rutBS2z6QtMq_rffRpqq3R_8Qevw7-vETC0/

Visit a registered googlesheet in the browser:

gap %>% gs_browse()
gap %>% gs_browse(ws = "Europe")

Read all the data in a worksheet:

africa <- gs_read(gap)
#> Accessing worksheet titled 'Africa'.
#> Parsed with column specification:
#> cols(
#>   country = col_character(),
#>   continent = col_character(),
#>   year = col_double(),
#>   lifeExp = col_double(),
#>   pop = col_double(),
#>   gdpPercap = col_double()
#> )
glimpse(africa)
#> Observations: 624
#> Variables: 6
#> $ country   <chr> "Algeria", "Algeria", "Algeria", "Algeria", "Algeria...
#> $ continent <chr> "Africa", "Africa", "Africa", "Africa", "Africa", "A...
#> $ year      <dbl> 1952, 1957, 1962, 1967, 1972, 1977, 1982, 1987, 1992...
#> $ lifeExp   <dbl> 43.077, 45.685, 48.303, 51.407, 54.518, 58.014, 61.3...
#> $ pop       <dbl> 9279525, 10270856, 11000948, 12760499, 14760787, 171...
#> $ gdpPercap <dbl> 2449.008, 3013.976, 2550.817, 3246.992, 4182.664, 49...
africa
#> # A tibble: 624 x 6
#>    country continent  year lifeExp      pop gdpPercap
#>    <chr>   <chr>     <dbl>   <dbl>    <dbl>     <dbl>
#>  1 Algeria Africa     1952    43.1  9279525     2449.
#>  2 Algeria Africa     1957    45.7 10270856     3014.
#>  3 Algeria Africa     1962    48.3 11000948     2551.
#>  4 Algeria Africa     1967    51.4 12760499     3247.
#>  5 Algeria Africa     1972    54.5 14760787     4183.
#>  6 Algeria Africa     1977    58.0 17152804     4910.
#>  7 Algeria Africa     1982    61.4 20033753     5745.
#>  8 Algeria Africa     1987    65.8 23254956     5681.
#>  9 Algeria Africa     1992    67.7 26298373     5023.
#> 10 Algeria Africa     1997    69.2 29072015     4797.
#> # ... with 614 more rows

Some of the many ways to target specific cells:

gap %>% gs_read(ws = 2, range = "A1:D8")
gap %>% gs_read(ws = "Europe", range = cell_rows(1:4))
gap %>% gs_read(ws = "Africa", range = cell_cols(1:4))

Full readr-style control of data ingest – highly artificial example!

gap %>%
  gs_read(ws = "Oceania", col_names = paste0("Z", 1:6),
          na = c("1962", "1977"), col_types = "cccccc", skip = 1, n_max = 7)
#> Accessing worksheet titled 'Oceania'.
#> # A tibble: 7 x 6
#>   Z1        Z2      Z3    Z4    Z5       Z6      
#>   <chr>     <chr>   <chr> <chr> <chr>    <chr>   
#> 1 Australia Oceania 1952  69.12 8691212  10039.6 
#> 2 Australia Oceania 1957  70.33 9712569  10949.65
#> 3 Australia Oceania <NA>  70.93 10794968 12217.23
#> 4 Australia Oceania 1967  71.1  11872264 14526.12
#> 5 Australia Oceania 1972  71.93 13177000 16788.63
#> 6 Australia Oceania <NA>  73.49 14074100 18334.2 
#> 7 Australia Oceania 1982  74.74 15184200 19477.01

Create a new Sheet from an R object:

iris_ss <- gs_new("iris", input = head(iris, 3), trim = TRUE)
#> Warning: At least one sheet matching "iris" already exists, so you may
#> need to identify by key, not title, in future.
#> Sheet "iris" created in Google Drive.
#> Range affected by the update: "R1C1:R4C5"
#> Worksheet "Sheet1" successfully updated with 20 new value(s).
#> Accessing worksheet titled 'Sheet1'.
#> Sheet successfully identified: "iris"
#> Accessing worksheet titled 'Sheet1'.
#> Worksheet "Sheet1" dimensions changed to 4 x 5.
#> Worksheet dimensions: 4 x 5.

Edit some arbitrary cells and append a row:

iris_ss <- iris_ss %>% 
  gs_edit_cells(input = c("what", "is", "a", "sepal", "anyway?"),
                anchor = "A2", byrow = TRUE)
#> Range affected by the update: "R2C1:R2C5"
#> Worksheet "Sheet1" successfully updated with 5 new value(s).
iris_ss <- iris_ss %>% 
  gs_add_row(input = c("sepals", "support", "the", "petals", "!!"))
#> Row successfully appended.

Look at what we have wrought:

iris_ss %>% 
  gs_read()
#> Accessing worksheet titled 'Sheet1'.
#> Parsed with column specification:
#> cols(
#>   Sepal.Length = col_character(),
#>   Sepal.Width = col_character(),
#>   Petal.Length = col_character(),
#>   Petal.Width = col_character(),
#>   Species = col_character()
#> )
#> # A tibble: 4 x 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>   <chr>        <chr>       <chr>        <chr>       <chr>  
#> 1 what         is          a            sepal       anyway?
#> 2 4.9          3           1.4          0.2         setosa 
#> 3 4.7          3.2         1.3          0.2         setosa 
#> 4 sepals       support     the          petals      !!

Download this precious thing as csv (other formats are possible):

iris_ss %>% 
  gs_download(to = "iris-ish-stuff.csv", overwrite = TRUE)
#> Sheet successfully downloaded:
#> /Users/jenny/rrr/googlesheets/iris-ish-stuff.csv

Download this precious thing as an Excel workbook (other formats are possible):

iris_ss %>% 
  gs_download(to = "iris-ish-stuff.xlsx", overwrite = TRUE)
#> Sheet successfully downloaded:
#> /Users/jenny/rrr/googlesheets/iris-ish-stuff.xlsx

Upload a Excel workbook into a new Sheet:

gap_xlsx <- gs_upload(system.file("mini-gap", "mini-gap.xlsx",
                                  package = "googlesheets"))
#> File uploaded to Google Drive:
#> /Users/jenny/resources/R/library/googlesheets/mini-gap/mini-gap.xlsx
#> As the Google Sheet named:
#> mini-gap

Clean up our mess locally and on Google Drive:

gs_vecdel(c("iris", "Gapminder"))
file.remove(c("iris-ish-stuff.csv", "iris-ish-stuff.xlsx"))

Remember, the vignette shows a lot more usage.

Overview of functions

fxn description
gs_ls() List Sheets
gs_title() Register a Sheet by title
gs_key() Register a Sheet by key
gs_url() Register a Sheet by URL
gs_gs() Re-register a googlesheet
gs_browse() Visit a registered googlesheet in the browser
gs_read() Read data and let googlesheets figure out how
gs_read_csv() Read explicitly via the fast exportcsv link
gs_read_listfeed() Read explicitly via the list feed
gs_read_cellfeed() Read explicitly via the cell feed
gs_reshape_cellfeed() Reshape cell feed data into a 2D thing
gs_simplify_cellfeed() Simplify cell feed data into a 1D thing
gs_edit_cells() Edit specific cells
gs_add_row() Append a row to pre-existing data table
gs_new() Create a new Sheet and optionally populate
gs_copy() Copy a Sheet into a new Sheet
gs_rename() Rename an existing Sheet
gs_ws_ls() List the worksheets in a Sheet
gs_ws_new() Create a new worksheet and optionally populate
gs_ws_rename() Rename a worksheet
gs_ws_delete() Delete a worksheet
gs_delete() Delete a Sheet
gs_grepdel() Delete Sheets with matching titles
gs_vecdel() Delete the named Sheets
gs_upload() Upload local file into a new Sheet
gs_download() Download a Sheet into a local file
gs_auth() Authorize the package
gs_deauth() De-authorize the package
gs_user() Get info about current user and auth status
gs_webapp_auth_url() Facilitates auth by user of a Shiny app
gs_webapp_get_token() Facilitates auth by user of a Shiny app
gs_gap() Registers a public Gapminder-based Sheet (for practicing)
gs_gap_key() Key of the Gapminder practice Sheet
gs_gap_url() Browser URL for the Gapminder practice Sheet

What the hell do I do with this?

Think of googlesheets as a read/write CMS that you (or your less R-obsessed friends) can edit through Google Docs, as well via R. It’s like Christmas up in here.

Use a Google Form to conduct a survey, which populates a Google Sheet.

  • The googleformr package provides an R API for Google Forms, allowing useRs to POST data securely to Google Forms without authentication. On CRAN and GitHub (README has lots of info and links to blog posts).

Gather data while you’re in the field in a Google Sheet, maybe with an iPhone or an Android device. Take advantage of data validation to limit the crazy on the way in. You do not have to be online to edit a Google Sheet! Work offline via the Chrome browser, the Sheets app for Android, or the Sheets app for iOS.

There are various ways to harvest web data directly into a Google Sheet. For example:

  • IFTTT, which stands for “if this, then that”, makes it easy to create recipes in which changes in one web service, such as Gmail or Instagram, trigger another action, such as writing to a Google Sheet.
  • IMPORTXML(), IMPORTHTML(), IMPORTFEED(): Google Sheets offer functions to populate Sheets based on web data.
    • Aylien.com blog post on using =IMPORTXML() to populate a Google Sheet with restaurant reviews and ratings from TripAdvisor.
    • Martin Hawksey blog post, Feeding Google Spreadsheets, shows how to scrape web data into a Google Sheet with no programming.
  • Martin Hawksey offers TAGS, a free Google Sheet template to setup and run automated collection of search results from Twitter.

Use googlesheets to get all that data into R.

Use it in a Shiny app! Several example apps come with the package.

What other ideas do you have?

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].