R with Google sheets / Excel

R with Google sheets / Excel

Sometimes it can be quite annoying to get the content of an R table directly into Excel. Until recently, I usually exported the table into a textfile (.csv) and then imported it back to google sheets respective Excel. This can be rather annoying and laborious, hence I started to look for alternatives and I found one in this StackOverflow post.

The trick is to use the clipboard. However, if we use e.g. the solution presented here on StackOverflow like this

clipboard <- function(x,row.names=FALSE,col.names=TRUE,...) {
  write.table(x,"clipboard",sep="\t",row.names=row.names,col.names=col.names,...)
}

clipboard(my.df)

we receive the following error under Linux:

Error in file(file, ifelse(append, "a", "w")) : 
  'mode' for the clipboard must be 'r' on Unix

To avoid this issue, the easiest is to use the bash tool xclip instead of the clipboard from within R directly. For that, we need to run first in the terminal

sudo apt-get install xclip

and then we can use an alternative command to copy a table into the clipboard like this

clipboard <- function(x, sep="\t", row.names=FALSE, col.names=TRUE){
     con <- pipe("xclip -selection clipboard -i", open="w")
     write.table(x, con, sep=sep, row.names=row.names, 
     col.names=col.names)
     close(con)
}

Now we can copy a data table or a vector into the clipboard with the command

clipboard(my.df)

and afterwards, it can be copied very comfortable with CTRL+V to Excel or Google sheets.

Leave a Reply