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 communicate 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 cached token and sync 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:
- Be sure to enable Google Sheets API in GCP (APIs & Services > Library from your GCP project menu). The error message will remind you to do that.
- If you get Error: Client error: (403) PERMISSION_DENIED then share your Google sheet with GCP service account. Go to IAM & admin > Service account in GCP project menu and copy the email of service account. It will look like some-name@project-name.iam.gserviceaccount.com. Share the Google sheet with this account as you usually do with regular users.
- You do not have to set or change any roles for service account in GCP (IAM & admin > IAM).