9 minute read

The last post showed the resource that we created to enable speakers to let events know that they have content for pre-cons/training days. This post will describe how the automation was created using a GitHub Issue and two GitHub Actions.

What do we need?

The idea was to have a form for user input that could easily allow a person to add themselves and some information to a web page. The page holds a list of speakers who can present training day sessions for data platform events. The web page can be found here. This page is generated from a JSON file.

A new repository

It was decided to use a GitHub repository to hold this information so that it is available publicly as well as via the website.

Create a dev container

It’s a brand new repository .devcontainer directory was created and the files from the Microsoft VS Code Remote / GitHub Codespaces Container Definitions repository PowerShell containers added. This means that whenever I or anyone else wants to work on the repo the development experience will be the same.

Add extensions

There are a number of default extensions that I install for PowerShell or generic development

I also added two more for this repository as we are using GitHub Actions

the view in codespaces of the GitHub Actions

Gather the Information

People can update repositories using Pull Requests but this needed to be a little more guided and it was decided that it was to be done with forms via GitHub Issues

Where to put it?

You can create custom GitHub Issues using YAML files in the .github/ISSUE_TEMPLATE directory. An Add Speaker issue template file was created. The name and the description will be seen on the new issues page.

name: Add Speaker
description: Add New Speaker information
  - type: markdown
      value: |
       Please follow the instructions to create a new speaker entry.
       We wil display this on callfordataspeakers.com

There are a number of -type entries. You can find the definitions in the docs or you can use the intellisense from the extensions. The types are checkboxes, dropdown, input, markdown, textarea

The intellisense showing the type options

I used the intellisense to build a quick simple form to gather 5 pieces of information

  • full name
  • topics
  • regions
  • sessionize profile URL
  • languages

You can find the YAML file here and the issue here

Process the information

Now that we have a method of gathering the information, the next stage is to process it automagically. For this we are going to be using GitHub Actions


GitHub Actions is a platform that can run automated processes called workflows that are defined as YAML files and triggered by events in the repository. We create another directory called workflows also in the .github directory.

Triggering the workflow

Many people are comfortable with a DevOps process that will build, test and deploy code when a pull request is raised and approved, GitHub Actions are able to do more as they can be triggered by any events in the repository.

You can automatically add labels, close stale issues and much more. There are a large number of events open to you as can be seen here . Even looking at just issues there are a number of activities types that can be used

  • opened
  • edited
  • deleted
  • transferred
  • pinned
  • unpinned
  • closed
  • reopened
  • assigned
  • unassigned
  • labeled
  • unlabeled
  • locked
  • unlocked
  • milestoned
  • demilestoned

(and there are separate ones for issue comments)

The beginning of the workflow YAML file has the name and then the trigger. This triggers the workflow when an issue is opened.

name: Add a new speaker json

     - "opened"

Getting all the source

The workflow consists of one or many jobs that can be run on different runners. The first job is named AddNewSpeaker and runs on the latest ubuntu version. Each job can have a number of steps and the first step in this scenario is to checkout the latest version of the repository.

We use a default action to checkout and because we push changes back to the repository (more on that later) we choose a fetch-depth of 0 to get all of the history and the ref main as that is the branch we are working with.

    runs-on: ubuntu-latest
    - uses: actions/[email protected]
        fetch-depth: 0
        ref: main

Being polite

costs nothing so this action from Peter Evans can be used to add or update a comment

    - name: Add comment to the issue
      uses: peter-evans/[email protected]
        issue-number: $
        body: |
          Hi @$,
          Thank you so much for your Speaker submission.
          The Action should be running now and adding it to the webpage. It should should update here.
          If it doesn't - get in touch with Rob on Twitter https://twitter.com/sqldbawithbeard

wait a minute, how did you work that out?

The say thank you comment uses github.event.issue.number and github.event.issue.user.login to ensure that the comment goes on the issue that triggered the workflow and thanks the user that created it. To work out what is available, I used this PowerShell step to write out the GitHub context to the logs as JSON

# You also can print the whole GitHub context to the logs to view more details.
    - name: View the GitHub context
      run: Write-Host "$GITHUB_CONTEXT"
      shell: pwsh 

Get the info into a file

Whilst developing, I first saved the issue body to a file so that I could work with it. As I moved forward I forgot and just left the code in and it works. The issue form creates ### <label> and then a blank line and then the data that was entered. This enabled me to use some regex and capture each label, grab the data and put it in a pscustomobject

Then I could convert it to Json and save it to a file. I chose to save each speakers information in their own file in case anything else would be needed in the future and also so that if the process failed it only affected this speakers information.

I also add the speaker file name to a text file that I may make use of at some future point.

    - name: Get Speaker Information to file
      run: |
        Write-Host "What do we have?"
        # gci -recurse = this is for troubleshooting because paths are hard
        $IssueBody =  "$"
        # Write-Host $IssueBody 
        $IssueBody | Out-File speakers/temp.txt
        # get the temp file contents - I do this so I don't lose anything
        $file = Get-Content ./speakers/temp.txt -Raw
        # parse the issue
        $regexResult = [regex]::Matches($file, '(?ms)fullname\n\n(?<fullname>.*)\n\n### topics\n\n(?<topics>.*)\n\n### regions\n\n(?<regions>.*)\n\n### Sessionize\n\n(?<Sessionize>.*)\n\n### language\n\n(?<language>.*)\n')
        # create an object
        $speakerObject = [PSCustomObject]@{
            name =  $regexResult[0].Groups['fullname'].Value
            topics = $regexResult[0].Groups['topics'].Value
            regions = $regexResult[0].Groups['regions'].Value
            sessionize = $regexResult[0].Groups['Sessionize'].Value
            language = $regexResult[0].Groups['language'].Value
        #save it to a file
        $speakerFileName = $SpeakerObject.name -replace ' ', '-' -replace '''','-' -replace '/','-' -replace '\\','-' -replace ':','-' -replace '\*','-' -replace '\?','-' -replace '"','-' -replace '\|','-'
        $filePath = './speakers/{0}.json' -f $speakerFileName
        $SpeakerObject |ConvertTo-Json | Out-FIle -FilePath $filePath
        $speakerFileName | OUt-File ./speakers/list.txt -Append
      shell: pwsh  

Because Ben is a fantastic tester

All the best testers will do unexpected but valid actions and my wonderful friend Ben Weissman (Twitter Blog) added some characters into the full name option that made the file save fail. He added his pronouns, which is awesome but not what I expected for a full name option. This is totally my fault for not considering either using pronouns or that as a user input field that is used in code the data should be validated. I used a few replaces to ensure the file name is acceptable.

$speakerFileName = $SpeakerObject.name -replace ' ', '-' -replace '''','-' -replace '/','-' -replace '\\','-' -replace ':','-' -replace '\*','-' -replace '\?','-' -replace '"','-' -replace '\|','-'

Let the user know and commit the new file

Next up is another comment, this time to show some progress but also add a link to the created files directory so that the speaker can see it. They can also edit this file if they wish to make any changes. (yes, maybe I should have thought of a way to do it with issues but this is an iterative process).

I love the EndBug/add-and-commit action as it enables me to make changes in a workflow and commit those changes safely back to the repository.

    - name: Add another comment to the issue
      uses: peter-evans/[email protected]
        issue-number: $
        body: |
          The Speaker Json has been added https://github.com/dataplat/DataSpeakers/tree/main/speakers
    - name: Add & Commit
      uses: EndBug/[email protected]
        author_name: Beardy McBeardFace
        author_email: [email protected]
        message: 'The Beard says hooray we have another speaker @$ - This is an automated message'


Don’t repeat yourself. The idea is to create the JSON file for the web-page from each of the speakers individual json files. People will want to change what they have entered or they will make mistakes, future functionality might require the same steps. With this in mind I created a separate workflow file to create the speaker-list.json file. This used two different triggers

  • workflow_calls so that it can be called from another workflow
  • workflow_dispatch so that it can be run manually

The other workflow cannot be triggered manually as it relies on an issue to create the required file.


Only run if

The second workflow file uses a PowerShell action to combine the individual JSONs into a single one and commits that to the repository. It also comments on the issue but it can only do this if the workflow was triggered from the add speaker job and not manually so some conditional logic was required. There were a number of options that I could choose to decide if to run this step but I decided on using the event issue number if: github.event.issue.number != null as if there was no issue, there was nothing to comment and this would leave this step open to be used in future coding if required.

- name: Add another comment to the issue
  uses: peter-evans/[email protected]
  if: github.event.issue.number != null
    issue-number: $
    body: |
      The speaker-list.json file has been recreated ready for the website https://github.com/dataplat/DataSpeakers/blob/main/website/speaker-list.json
      https://callfordataspeakers.com/precon should be updated now

Calling another workflow

To call another workflow in a job you use the uses: field and the path to the yaml file and the branch. We also added the needs: so that this job will run after the addNewSpeaker has completed.

  needs: addNewSpeaker
  uses: dataplat/DataSpeakers/.github/workflows/[email protected]

Close the issue

This process needed to be completely automatic and so we use Peter Evans close issue action and tag the speaker and say thankyou as well as closing the issue. We have a needs: property so that this job will only run following the successful run of the previous two jobs.

    needs: [addNewSpeaker,createSpeakerListJson]
    runs-on: ubuntu-latest
    - name: Close Issue
      uses: peter-evans/[email protected]
        issue-number: $
        comment: |
          Hey @$,
          Closing this issue now that the Action has run successfully.
          Thank you so much for adding your information to the list.
          It will be active on callfordataspeakers.com shortly.
          Please share on social media.
          Love and Hugs
          Rob and Daniel
          @SqlDbaWithABeard @dhmacher

Show me what it looks like

You can take a look at the repo there are a number of issues like this one from Monica Rathbun (Twitter - Blog)

Monicas Image

you can see the workflows running here

workflow run

Happy Automating!