‘Error: Can't get Google credentials’ when trying to use googlesheets4 in a GitHub Action

[AnneMTreasure]

Hi there, I’m having a problem with GitHub Actions, and using googlesheets4 to read an encrypted .json file using a password saved as a GitHub Actions secret.

I have written an R script that reads in data from a google sheet, does some data manipulation steps, then writes this new data set to a separate google sheet. For this, I have used the package googlesheets4. To run the script and get authorisation to be able to read from & write to google sheets in a non-interactive way, I have set up a service account on Google Cloud Platform, created the credentials, and a service account token key in .json format, and shared the google sheets with the email given in the .json file. The .json key file is stored in my R project directory, and now I can run my script without interaction, using the following for authorisation:

gs4_auth(email = "my email address", path = "~/path_to_file/sheets_service_account_key.json")

This works fine locally on my computer. However, I now want to run my script as a GitHub Action using a cron scheduler. My problem is the .json key file, which should not be made public, and I am struggling to figure out how to either keep it as a secret on GitHub, which can somehow be called into the GitHub Action using googlesheets4 in my R script, or to encrypt the file.

I was pointed to the tokencodr package and, using this, I have encrypted the .json file and put this in a .secret folder in my GitHub repo (this encrypted file is called MY_GOOGLE). Committing this encrypted file to GitHub is apparently ok, as long as the password (created when the file is encrypted) is kept secret. I have copied this password to an Actions secret in my GitHub repo.

Then, in my R script, I try to use the gs4_auth function to tell it to look for the encrypted .json in the .secret folder in my GitHub repo using:

gs4_auth(email = "*@email.co.za", path = "~/repo_name/.secret/MY_GOOGLE")

Then, in my GitHub Action .yaml, I have the following:

name: run_r_script_test
on:
 schedule:
   - cron: '22 04 * * *'
jobs:
 build:
   runs-on: macos-latest

   env:
     MY_GOOGLE_PASS: ${{ secrets.MY_GOOGLE_PASSWORD }}

   steps:
     - uses: actions/checkout@v2

     # set up R
     - uses: r-lib/actions/setup-r@master
       with:
         r-version: ${{ matrix.config.r }}
         http-user-agent: ${{ matrix.config.http-user-agent }}

     # set up Pandoc
     - uses: r-lib/actions/setup-pandoc@master

     - name: step-1-install-packages
       run: |
         install.packages('tidyverse')
         install.packages('googlesheets4')
       shell: Rscript {0}

     - name: step-2-run-script
       run:
         Rscript -e "source('scripts/convert.R')"

etc

The GitHub Action does not work. This is the error message part of the GitHub Action build log:

step-2-run-script
5s
1
Run Rscript -e "source('scripts/convert.R')"
10
── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──
11
:heavy_check_mark: ggplot2 3.3.5     :heavy_check_mark: purrr   0.3.4
12
:heavy_check_mark: tibble  3.1.5     :heavy_check_mark: dplyr   1.0.7
13
:heavy_check_mark: tidyr   1.1.4     :heavy_check_mark: stringr 1.4.0
14
:heavy_check_mark: readr   2.0.2     :heavy_check_mark: forcats 0.5.1
15
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
16
:heavy_multiplication_x: dplyr::filter() masks stats::filter()
17
:heavy_multiplication_x: dplyr::lag()    masks stats::lag()
18
Error: Error: Can't get Google credentials.
19
:information_source: Are you running googlesheets4 in a non-interactive session? Consider:
20
• Call `gs4_deauth()` to prevent the attempt to get credentials.
21
• Call `gs4_auth()` directly with all necessary specifics.
22
:information_source: See gargle's "Non-interactive auth" vignette for more details:
23
:information_source: <https://gargle.r-lib.org/articles/non-interactive-auth.html>
24
Backtrace:
25
   █
26
1. ├─base::source("scripts/convert.R")
27
2. │ ├─base::withVisible(eval(ei, envir))
28
3. │ └─base::eval(ei, envir)
29
4. │   └─base::eval(ei, envir)
30
5. └─googlesheets4::gs4_auth(email = "*@email.co.za", path = "~/repo_name/.secret/MY_GOOGLE")
31
6.   └─googlesheets4:::gs4_abort(...)
32
7.     └─cli::cli_abort(...)
33
Execution halted
34
Error: Process completed with exit code 1.

Can googlesheets4 be used in a GitHub Action in this way / Is it possible to use the googlesheets4 package in my R script to look for the encrypted .json file in the .secret folder in my GitHub repo, and the necessary password in the GitHub Action secret? I am also assuming that the password is enough to decrypt the file, which is possibly not the case.

Any advice would be much appreciated. Many thanks.

[jennybc]

The official way to to this is documented here, which is also exactly what's done in googlesheets4, googledrive, and bigrquery, all of which run tests with a service account token on GHA. So in addition to the article you can also look at those repos for how things are put together.

https://gargle.r-lib.org/articles/articles/managing-tokens-securely.html

[AnneMTreasure]

Hi Jenny, thanks for your reply. Does the https://gargle.r-lib.org/articles/articles/managing-tokens-securely.html article apply to non-package environments as well, which is what I have? I have seen this page, and tried to work through it, but have not been sure of the package versus non-package environment to set this up?

[jennybc]

It's true that the helpers like gargle:::secret_path() are anticipating a package directory structure. You would need to execute the "business end" of the key functions yourself, without the package-y path bits.

But the general principles all hold up, regardless of whether the project is a package.

That encryption approach has now been baked into the httr2 package and written up in a more general way:

https://httr2.r-lib.org/articles/wrapping-apis.html#secret-management-1

Maybe those instructions for encrypting / decrypting will be easier to transfer to a non-package project.

If this ends up playing out on GitHub and I can see your code and GHA config, I will try to help.

[AnneMTreasure]

Hi Jenny, many thanks for your advice. I have managed to get my GitHub Action to work (just a test repo for now).

I used the tokencodr package for the encryption, and got some very good advice and pointers for this from the package developer, and this demo: https://github.com/jdtrat/tokencodr-google-demo The authorisations using googlesheets4 are all working now.

Many thanks for your help!