How to trigger a GitHub Actions workflow from Google Sheets

A common problem I’ve seen in newsrooms is that you’ll have a database managed by a reporter, an app managed by a news dev, and no clean way to bridge the gap. Storing the data somewhere accessible to the app is the obvious solution, but in some cases you need to redeploy the app with every update.

Generally, the solution is the reporter pinging the developer to let them know that the data is ready to be pulled. For such cases, I’ve saved time by writing scripts to fetch the latest data and insert it into the project, leaving just a quick gulp deploy – but this was still too manual for my liking. Ideally, the reporter could just update the graphic themself when ready.

This is just one of countless examples where making a script executable by a non-coder would benefit both parties. While you can manually run a GitHub Action in the GUI, this can still be intimidating for some. Using Google Apps Script, we can trigger the workflow via REST API from the Google Sheets menubar, an environment many users already feel comfortable.

Set up the Google Apps Script

In your Google Sheet, navigate to Extensions → Apps Script. Here we will be adding two functions:

• onOpen() adds a custom menu to the Sheet’s toolbar. To expand the custom UI further, refer to the docs.
• triggerGithubAction() makes a POST request to the Github REST API and triggers a workflow within a Github repo.

You will need a personal access token from GitHub to authenticate the request. Make sure you add repo and workflow permissions.

To conceal this token in your Google Apps Script, navigate to Project Settings, scroll down to Script Properties and add the token as new property. You can then access this property via the Properties Service without exposing it in your code. Note that anyone who has access to the Google Action Script can still access the token.

Within the code.gs file, add the following functions:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('⚡ Custom functions')
      .addItem('Publish data', 'publishDataToGithub')
      .addToUi();
}

function triggerGithubAction() {
  SpreadsheetApp.getUi()
    var options = {
      'method' : 'post',
      'contentType': 'application/json',
      'payload' : JSON.stringify({
        'event_type': 'Run flat.yaml from Google Sheets'
      }),
      headers: {
        'Authorization': `Bearer ${PropertiesService.getScriptProperties().getProperty('GITHUB_TOKEN')}`
      },
    };

    UrlFetchApp.fetch('https://api.github.com/repos/<USERNAME>/<REPO>/dispatches', options);
}

Once finished, save and reopen your Google Sheet. It may take a few seconds to load, but you should now have a custom menu option that triggers the Github Action.

Publishing your Google Sheet

Depending on your workflow, you might be done in Google Sheets. If like in my example above you want to import the Google Sheet data into your repo, you will need to make it accessible.

One easy method is to publish the sheet containing your data to the web by selecting File → Share → Publish to web. Publish as a CSV file and make sure you select the option to automatically republish when changes are made. Since this is a public (read only) document, it should only contain the data you intend to publish. Copy the download url and move onto the next step. If you prefer to keep the Google Sheet private, you will need to set up a pull method using the Google Sheets API on the Github Actions side.

On the GitHub side

Github Actions let you run scripts from within your repository in the cloud. They can be scheduled with a cron, triggered on a condition, or run manually. For this demo, we’ll set up a workflow that downloads the latest data from Google Sheets and commits it to the repository.

Go to your repository on GitHub.com and navigate to the Actions tab. If this is the first workflow in the project, you will be prompted to either search for a workflow template or set up one yourself. Do what makes sense for your task, but for this tutorial we’ll be setting one up from scratch.

You’ll then be taken to a skeleton YAML file. Replace the contents with the following and give it a name. This workflow uses Flat Data GitHub Action to fetch and commit data as flat files.

Regardless of what your workflow is doing, the important part is making sure that it includes the repository_dispatch event as a workflow trigger. Back in our Google App Script, our POST request is pinging the /dispatches endpoint, triggering any workflows listening for the repository_dispatch event.

name: Download data

on:
  repository_dispatch:
  workflow_dispatch: {}
jobs:
  scheduled:
    runs-on: ubuntu-latest
    steps:
      - name: Setup deno
        uses: denoland/setup-deno@main
        with:
          deno-version: v1.x
      - name: Check out repo
        uses: actions/checkout@v2
      - name: Fetch data
        uses: githubocto/flat@v2
        with:
          http_url: <CSV_URL>
          downloaded_filename: public/data.csv

What you do next is up to you. In my case, I have a separate workflow listening for the Download data action to finish, which will then build and deploy the project to AWS.

name: Publish new data

on:
  workflow_dispatch: {}
  workflow_run:
    workflows: ["Download data"]
    types: [completed]

jobs:
  build:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v1
      - name: Use Node.js ${{ matrix.node-version }}
        uses: actions/setup-node@v1
        with:
          node-version: '11.x'
      - name: Configure AWS Credentials
        uses: aws-actions/configure-aws-credentials@v1
        with:
          aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
          aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          aws-region: us-east-2
      - name: Install
        run: npm ci
      - name: Build
        run: npm run build --if-present
      - name: publish
        run: npm run publish

And done! Now you have a GitHub Action workflow triggered from Google Sheets via REST API 🎉

I hope you enjoyed this writeup. Questions, suggestions, and criticisms can be directed to jared.m.whalen@gmail.com. Happy coding!