Introduction
When a Pull Request in Github gets approved I wanted to write some information about it in a Google Sheet.
In particular I would like to store
- the date that the PR was approved
- The PR's name
- The Author's github username
- The Reviewer's github username
Github offers webhooks on specific events, and I thought I could utilize that and setup a small webservice that receives the webhook and by using the Google Sheets API it will append a row in the google Sheet.
Instead of deploying a web server to do that I chose to write a Cloud Function that does the processing. This requires less maintenance and I only have to pay per invocaton.
Considering the time to manage a web server and the monthly cost to run it, I concluded that a Cloud Function suits better to the problem.
You can find the implementation here .
Let's build that
First create a Google Cloud project if you do not have one.
Follow instructions here
Then enable the APIs instructions here .
Enable Google Sheets API.
Also enable the Cloud Functions in your Account.
Keep in mind that you need also to have a Billing Account (add your payment details in other words). Add it if you don't have already one.
Finally, enable the Cloud Build.
To find these in the UI just use the search.
Service Account
Create a new Service Account or use an existing one.
Just note down the email of the acccount. You are going to need it later to give access to your Spreadsheet. Also, create a Key for your account and keep the json provided somewhere secure in your computer.
Create a Google Sheet
Create a normal Google Sheet and share it with your google's service account email.
This is the normal Share button in the top right.
Let's deploy it
git clone https:
there are instructions in the README .
Pay attention to the WEBHOOK_SECRET, pick something secure and note it
since we are going to use it when we setup our github webhook.
To deploy you may need to download the gcloud CLI tool.
Follow the instructions to deploy the cloud function.
Make sure that your function is using the runtime of your Service Account.
To see that go to your cloud function details and see the Runtime menu.
Once you deploy you will see in the output a section httpsTrigger which has a url.
Keep this url, you are going to need it for setting up your github webhook.
Go to your github repo and in Settings select Webhooks.
Create a new webhook with Content-Type application/json and put the correct ur (the one from the google function). Also add as secret the value of WEBHOOK_SECRET you used previously.
Additionally, from the section
Which events would you like to trigger this webhook?
select only the Pull request reviews.
Now we are done, if you have deployed properly then you can make a Pull Request and Assign a Reviewer (just make a second github account or ask a friend).
Once the reviewer approves that then a new row will be added in the google sheet.
References
- https://cloud.google.com/functions/docs/create-deploy-gcloud
- https://docs.github.com/en/github-ae@latest/developers/webhooks-and-events/webhooks
- https://developers.google.com/sheets/api/samples/writing#append_values
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
