CODEWITHSUNDEEP
rgoogle-drive-api
TheGoat
TheGoat

Reputation: 2867

Download xlsx file from Google Drive in R

I have publicly shares a small dataset on Google Drive and I have made the file accessible to anyone with the link.

I wish to download this file into R for analysis but I am having difficulty with unzipping the file from the temp directory.

My code looks as follows:

install.packages("pacman")
library(pacman)
#Load Libraries
pacman::p_load(tidyverse,tidymodels,modeltime,timetk,googledrive)

temp <- tempfile(fileext = ".zip")

dl <- drive_download(
  as_id("https://drive.google.com/file/d/17ZhE3nxqtGYNzeADMzU02YzfKU9H9f5j/view?usp=sharing"),
  path = temp, 
  overwrite = TRUE, 
  type = "xlsx")

out <- unzip(temp, exdir = tempdir())

#Import Data
Three_Time_Series <- read_excel(out[1])

When I examine the out variable I see it's a character vector of size 1:10 but each character string references and xml file. In the final line I have tried reading in out[1:10] but each time it says:

Error: Can't establish that the input is either xls or xlsx.

Any tips would be greatly appreciated.

Upvotes: 2

Views: 1737

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389047

What you have is a URL for viewing, you should obtain the URL for editing/downloading the file.

The following works for me.

library(googledrive)

dl <- drive_download(
 as_id("https://docs.google.com/spreadsheets/d/17ZhE3nxqtGYNzeADMzU02YzfKU9H9f5j/edit#gid=1748893795"),
  path = 'temp1.xlsx', 
  overwrite = TRUE, 
  type = "xlsx")


Three_Time_Series <- readxl::read_excel('temp1.xlsx')
Three_Time_Series

# A tibble: 528 x 3                                                                                                
#   DATE_TIME           CELL  AVG_SIGNAL_LEVEL
#   <chr>               <chr>            <dbl>
# 1 04.21.2017 10:00:00 CELL1            -106.
# 2 04.21.2017 10:00:00 CELL2            -105.
# 3 04.21.2017 10:00:00 CELL3            -105.
# 4 04.21.2017 11:00:00 CELL1            -106.
# 5 04.21.2017 11:00:00 CELL3            -105.
# 6 04.21.2017 11:00:00 CELL2            -105.
# 7 04.21.2017 12:00:00 CELL2            -105.
# 8 04.21.2017 12:00:00 CELL1            -106.
# 9 04.21.2017 12:00:00 CELL3            -105.
#10 04.21.2017 13:00:00 CELL1            -106.
# … with 518 more rows

Upvotes: 7

Related Questions