Create a new GitHub Issue from a Google Form submission

google-form-to-github-issue.md

Wiring up a Google Form to GitHub is not that difficult with a little bit of Apps Script automation. All you need is a Google account, a GitHub account, and a web browser...

Set up your GitHub Personal Access Token

Personal access tokens provide an easy way to interact with the GitHub API without having to mess with OAuth. If you don't already have a personal access token with repo or public_repo access, visit your GitHub settings page and generate a new token.

Be sure to copy your token some place safe and keep it secure. Once generated, you will not be able to view or copy the token again.

Set up the Form & Spreadsheet

1. Create a Google Form.
2. From the Responses tab, click the More icon.
3. Select Choose a response destination.
4. Select New spreadsheet: Creates a new spreadsheet in Google Sheets for responses.
5. Click Create to create and open the sheet.

Configure the App Script Logic

1. You should have a newly created blank spreadsheet with headers automatically generated from your form.
2. Click Tools > Script editor... to launch the App Script editor coding environment. This Script will be bound to your sheet, so you can listen for form submissions and fire off a new issue to your GitHub repo.
3. Delete the boilerplate code in the Code.gs file and replace it with something similar to this:

var ghToken = "my-personal-access-token";

function onFormSubmit(e) {

  var title = e.values[2] + ": " + e.values[4];
  
  var body = "| Contact Email | Organization Name | Change Date | Change Type | Plan | Licenses | Comments |\n" +
             "|---|---|---|---|---|---|---|\n" +
             "| "+e.values[1]+" | "+e.values[2]+" | "+e.values[3]+" | "+e.values[4]+" | "+e.values[5]+" | "+e.values[6]+" | "+e.values[7]+" |" ;
  
  var payload = {
    "title": title,
    "body": body
  };
   
  var options = {
    "method": "POST",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  
  var response = UrlFetchApp.fetch("https://api.github.com/repos/bmcbride/my-repo/issues?access_token="+ghToken, options);
}

4. The onFormSubmit function includes an event object e, which includes the form/spreadsheet field values as a simple array with values in the same order as they appear in the spreadsheet. e.values[0] is the first spreadsheet column (typically Timestamp).
5. In my example, I'm collecting information from users who wish to make license changes to a web service. I'm building a simple markdown table, which includes some of the form data in the body of my issue.
6. Reference the GitHub Issues API for a full list of options for programmatically creating a new issue.
7. Once we've built the title and body of the issue, we can build the HTTP request using App Script's URL Fetch Service.
8. Give your app script project a name and save it .

Set up the Trigger

1. From within the app script editor, click Resources > Current project's triggers.
2. Click to add a trigger
   • Run: onFormSubmit
   • Events: From spreadsheet, On form submit
3. Click Save and accept any authorizations to access your forms and access web services on your behalf.
4. This trigger will listen to form submissions and pass the data to your function, which POSTs the new issue to your GitHub repo.

Conclusion

This exercise demonstrates how to utilize Google Forms as a front-end for capturing information, which can then be passed on to other services, such as GitHub, CartoDB, Fulcrum, etc.

don't work
TypeError: Cannot read property "values" from undefined. (line 5, file "Code")

Can you fix ?

@Thomanphan, I was getting the same thing and switched out the e.values.

Looks like this now:

function onFormSubmit(e) {

  var itemResponses = e.response.getItemResponses();
  var item1 = itemResponses[1].getResponse();
  var item2 = itemResponses[2].getResponse();
  var item3 = itemResponses[3].getResponse();
  var item4 = itemResponses[4].getResponse();
  var item5 = itemResponses[5].getResponse();
  var item6 = itemResponses[6].getResponse();
  var item7 = itemResponses[7].getResponse();
  var item8 = itemResponses[8].getResponse();

  var title = "Bug: " + item2;

  var body = 
      "<strong>Submitted By: </strong>" + item1 +
        "\n<strong>url: </strong>" + item3 +
        "\n<strong>device: </strong>" + item4 +
        "\n<strong>browser: </strong>" + item5 +
        "\n<strong>expected behavior: </strong>" + item6 +
        "\n<strong>actual behavior: </strong>" + item7 +
        "\n<strong>steps to reproduce: </strong>" + item8;

  var payload = {
    "title": title,
    "body": body
  };

  var options = {
    "method": "POST",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };

  var response = UrlFetchApp.fetch("https://api.github.com/repos/EXAMPLE?access_token="+ghToken, options);
}

Hey y'all. How would you recommend hiding your GitHub token?

Just a little tweak on this - I needed to bind the script to the Google Form in order for it to work. But once working it's a great script! Thanks for posting.

@bmcbridge Simple question. Is the hard coded key safely hidden? Where will this script run? google servers, so probably yes.

Thanks @bmcbride!
@Thomanphan, @tylerswartz : I encountered the same error, but as far as I can tell it is only because there is no actual FormSubmit event happening when you run the debugger. It worked when I used the form preview and submitted real data.

A few things that would have saved me some time if I knew them in advance:
There is no console.log() in Google App scripts. Instead you can use Logger.log() while debugging, then view what you logged at View > Logs
While setting up the trigger, there is an option to alter the notification frequency. For debugging it was helpful to change the notifications to 'email immediately'.
Github uses a special domain and path ("https://api.github.com/repos") for api requests. If you are an idiot like me and try to use "https://github.com" you will get a confusing 403 "Cookies must be enabled to use GitHub" response.

@troystarwalt - little late, but it would be hidden by default, since you're actually placing the Google Apps Script on the Google Sheet that the Form stores data on. The only way it would be visible to anyone but you is if you shared that Sheet with someone else.

I want to correct this part: https://gist.github.com/bmcbride/62600e48274961819084#set-up-the-trigger

The Resources > Current project's triggers is actually Edit > Current project's triggers

@tylerswartz with your code I still get cannot read property 'getResponse' of undefined

Worked great for me! Thanks for the guide!

@tylerswartz @Rup1 I also am getting the cannot read property 'getResponse' of undefined error.

This is the code:

var itemResponses = e.itemResponses.getItemResponses();
var item1 = itemResponses[1].getResponse();
var item2 = itemResponses[2].getResponse();
var item3 = itemResponses[3].getResponse();

is there a fix?

does anybody know how to set the authorization in the header?

also, i think this should be in googles app script samples. i can do this if you would like me to @bmcbride

does anybody know how to set the authorization in the header?

Adding the token to the header like this worked for me:

  var options = {
    "method": "POST",
    "headers": {
        "authorization": "token "+ghToken
    },
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };

Not sure if the other parameters should also be under headers, but at least it's working.

I had placed headers outside and then reference it within the options variable, but considering headers is static all the time @Minty123's way is a bit more clean

Thanks for this, can you tell me how to have the body values in rows rather tan columns?

👋 Tried this out,

Some updates to the code is needed since the GitHub API now accepts authorisation token in the headers only.

• Add authorization moved in the header
• Change the fetch URL

  ...
  ...
  
    var options = {
        "method": "POST",
        "headers": {
            "authorization": "token "+token,
            "Accept": "application/vnd.github.v3+json",
        },
        "contentType": "application/json",
        "payload": JSON.stringify(payload)
      };

    Logger.log(payload)

    var response = UrlFetchApp.fetch("https://api.github.com/repos/"+handle+"/"+repo+"/issues", options);

Reading the log file helped to debug 🙂 also reading through feedbacks from these folks helped @Minty123 @bellerbrock

thanks!

What if the GitHub enterprise server is behind a VPN?

Some of the commented changes lead me to writing an updated code snippet. See it here or on my forked gist:

I store the GitHub token under Settings > Script Properties as "github-token".

It should be a fairly generic script.

let props = PropertiesService.getScriptProperties().getProperties();
let ghToken = props["github-token"];
let ghlink = "https://api.github.com/repos/myname/myrepo/issues?access_token="

function onFormSubmit(e) {
  let itemResponses = e.response.getItemResponses();
  let titles = itemResponses.map((response) => response.getItem().getTitle())
  let items = itemResponses.map((response) => response.getResponse())

  Logger.log("response items: %s", items)

  let title = items[0];

  let body = "";
  for (var i = 0; i < itemResponses.length; i++) {
    body += "### " + titles[i] + "\n\n";
    body += items[i] + "\n\n";
  }
  
  let payload = {
    "title": title,
    "body": body
  };
   
  let options = {
    "method": "POST",
    "headers": {
      "authorization": "token "+ghToken,
      "Accept": "application/vnd.github.v3+json",
    },
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };

  Logger.log("Payload: %s", payload)
  
  var response = UrlFetchApp.fetch(ghlink + ghToken, options);
}