Generate GitHub Issue from Google Form submission

google-form-to-github-issue.md

Generate GitHub Enterprise Issue from Google Form submission

To generate a nicely-formatted GitHub issue (even for GitHub Enterprise accounts) from a Google Form submission, you can use Google's script editor along with a GitHub personal access token that connects to the API. This is particularly useful when you need to triage bugs or feature requests directly to developers, but those who are submitting issues do not have access to your GitHub Enterprise instance.

Once this is up and running, on the development end, you can do some cool things within the body of each issue, like automatically closing GitHub issues via your commit messages and CCing your dev group or individual team members for each issue.

Here's how to set it up.

Step 1: Create Your Form

• Go to Google Drive and create a form with fields
• Click the Responses tab
• Click the green spreadsheet icon
• Create a new spreadsheet

The spreadsheet will open in a new tab or window.

Step 2: Add a Google Script

• Go to Tools > Script Editor
• Remove everything in the default Code.gs file
• Paste the following in the Code.gs file:

var ghToken = "YOUR_PERSONAL_ACCESS_TOKEN";

function onFormSubmit(e) {

  var title = e.values[9];
  var type = e.values[6].toLowerCase(); // for example: Bug, Enhancement, Question
  var priority = "priority-" + e.values[7];

  var body = "# Details \n" + e.values[3] + "\n\n" +
             "| Submitted by | Priority | URL | Screenshot | Viewport(s) | Device(s)/Browser(s) |\n" +
             "|---|---|---|---|---|---|\n" +
             "| " +e.values[8] + " | " + e.values[7] + " | " + e.values[1] + " | " + e.values[2] + " | " + e.values[4] + " | " + e.values[5] + " |\n\n";

  var payload = {
    "title": title,
    "body": body,
    "labels": [
         type,
         priority
     ]
  };

  var options = {
    "method": "POST",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  var response = UrlFetchApp.fetch("https://YOUR_GITHUB_ENTERPRISE_DOMAIN/api/v3/repos/YOUR_REPO_PATH/issues?access_token="+ghToken, options)
}

The body variable creates a table within each issue. See GitHub help for more info about formatting tables.

Step 3: Customize the Google Script

In our example, e.values is a zero-based array, where each index maps to a spreadsheet column's value (for example, the issue's title is column J in our spreadsheet, which corresponds to e.values[9]).

• Remap the above script to work with your spreadsheet's columns and e.values index numbers. Delete anything in payload or body you don't need (title is the only required value for creating an issue). See the GitHub docs for more values you can associate with newly-created issues.
• Go to GitHub Enterprise, click on your user icon in the header, and click Settings. Click Personal access tokens. Click Generate new token. Enter a description and uncheck everything but repo. Click Generate token. Click the Copy Token icon to the right of your new personal access token. (Note: Treat this personal access token like a password — don't share it with anyone!)
• In the ghToken variable of the Google Script, replace YOUR_PERSONAL_ACCESS_TOKEN with your copied GitHub Enterprise personal access token.
• In the response variable of the Google Script:
  • Replace YOUR_GITHUB_ENTERPRISE_DOMAIN with your GitHub Enterprise domain (e.g. git.awesometeam.com)
  • Replace YOUR_REPO_PATH with your repository's path (e.g. Awesome/awesome_assets)
• Save your Google Script

Step 4: Setup a Trigger to Automatically Create Issues when a Form is Submitted

Now that the API connection is sorted out, we need to add a trigger to our Google Form so that, each time the form is submitted, there's a call to the GitHub API and an issue is created.

• Within the Script Editor, click Resources > Current project's triggers
• Click the link to add a new trigger
• Use the following dropdown values:
  • Run: onFormSubmit
  • Events: From spreadsheet
  • On form submit
• Save and/or authenticate as necessary

Fin

That's all! Test away.

Here's my take on this:

const token = "XXX";
const owner = "some-org";
const repo = "some-repo";

function onFormSubmit(event) {
  // in-order columns from sheet
  const [timestamp, title, name, description] = event.values;

  // issue content, in markdown
  const body = `
## Your name

${name}

## Issue Description

${description}
`;

  const payload = { title, body, };

  // https://docs.github.com/en/rest/issues/issues?apiVersion=2022-11-28#create-an-issue
  const url = `https://api.github.com/repos/${owner}/${repo}/issues`;

  // latest recommended headers, auth, options, and such
  const headers = {
    "Accept": "application/vnd.github+json",
    "Authorization": "Bearer " + token,
    "X-GitHub-Api-Version": "2022-11-28",
  }

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

  const response = UrlFetchApp.fetch(url, options)
}