I am trying to upload my csv files to my google drive/sheets.
Working code: a window pop-up, I login into my google account, then code works perfectly, file is uploaded.
library(googledrive)
library(googlesheets4)
dff <- drive_upload('dff.csv', type = "spreadsheet")
drive_browse(dff)
I want to avoid this step. So I went to my googlesheets API and created from that window a service account with owner permission.
library(googledrive)
library(googlesheets4)
sheets_auth(scope = "https://www.googleapis.com/auth/drive",
path = 'myjson.json')
drive_auth(token = sheets_token())
dff <- drive_upload('dff.csv', type = "spreadsheet")
drive_browse(dff)
And this does not work. Moreover, the drive_browse(dff) opens a browser window with a message that I have no access to the file.
How can I solve this? Maybe there are other options? Ideally I need this script to run without any logins to google at all.
Service accounts behave like any other normal account - they can own files and have permissions to them. i.e. if you create a file with your personal account and attempt to access it using the service account, without giving the appropriate permissions prior to that, it will result in an error.
I suggest you use the service account for both of your communications to the Sheets and Drive APIs. In order to do that, you should replace drive_auth(token = sheets_token()) to drive_auth(service_token='myjson.json') (in case you are using googledrive library v0.1.3) or drive_auth(path = 'myjson.json') for v1.0.0 of the library.
Bear in mind that after creating the files using the service account, if you plan on accessing them from any other account you will have to share them to your account beforehand. You can use the drive_share() function in your R code for that.
Further to that, you can consider the use of delegation in case you are using G Suite in your domain. This feature will allow the service account to act as (or "impersonate") another user of the domain. Every action that it performs will be as if the subject of the "impersonation" were executing it, anything permissions related included.