How to Grant an Access for R googlesheets4 package Using Google Cloud Platform Service Account

One of my weekly tasks brought me to Google Cloud Platform instance to deploy a tiny R script as a cron job. This script makes some data transformations and writes the results to Google Sheets. I use googlesheets4 package to comunicate with Google Sheets and it works frictionless on my local machine.

But Ubuntu cloud instance is not a local Windows machine. I do not use interactive mode on instances and browser as well so the common OAuth authorization is not the case here. Yes, you can play with chached token and synck it between local machine and cloud instance but there is much easear way to solve this problem.

Say hello to service accounts. I won’t cover here all the details what it is and how to get it because some kind people have already done that. Here is a good description from gargle package documentation. gargle is the guy who handles authorization under the hood in googlesheets4.

After creating the service account in GCP you will get a JSON file. To work in non-interactive mode with googlesheets4 you’ll have to do something like this:

googlesheets4::sheets_deauth()
googlesheets4::sheets_auth(path = '/path/to/service/account/JSON/file')
googlesheets4::sheets_write(
  data  = your_data_frame,
  ss    = 'google_sheets_document_id',
  sheet = 1
)

Everything seems simple, right? These are the lessons that I’ve learned running this code again and again for a few hours:

Comments

comments powered by Disqus