Retrieve GitHub Project Issues to a Google Spreadsheet

AleGuerra — Fri, 29 Mar 2024 06:48:17 +0000

Hey there, fellow project managers! Have you ever found yourself confidently saying,

“I’m a project manager, and obviously, I have everything under control”?

Well, we all know that managing projects can sometimes be challenging, but fear not! In this article, I’ll walk you through the steps to retrieve GitHub project issues and effortlessly populate them into a Google Spreadsheet using the GitHub GraphQL API. So, let’s dive in and conquer those project issues like the capable managers we are!

Before we embark on this exciting journey, let’s make sure we have everything we need:

GitHub Access Token: To obtain a GitHub Access Token, you can generate a personal access token in your GitHub account settings by navigating to “Settings” -> “Developer settings” -> “Personal access tokens,” and then click on “Generate new token.”
Google Spreadsheet: Create a brand new Google Spreadsheet or dust off an existing one where you’d like to store the GitHub project issues.
GitHub Project ID: Identify the project ID of the GitHub project you want to retrieve issues from. You may need to use a GraphQL query for this:

curl --location 'https://api.github.com/graphql' \
--header 'User-Agent: custom' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer YOUR_ACCESS_TOKEN' \
--data '{"query":"{organization(login:\"YOUR_ORGANIZATION\"){projectV2(number: PROJECT_NUMBER) {id}}}"}'

Or, if you are using a personal project instead of an organizational one:

curl --location 'https://api.github.com/graphql' \
--header 'User-Agent: custom' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer YOUR_ACCESS_TOKEN' \
--data '{"query":"{user(login:\"USER\"){projectV2(number: PROJECT_NUMBER) {id}}}"}'

Remember to replace YOUR_ACCESS_TOKEN, YOUR_ORGANIZATION (or USER), and PROJECT_NUMBER in the code snippet with your actual values.

You should receive back something similar to this:

{"data":{"organization":{"projectV2":{"id":"YOUR_PROJECT_ID"}}}}

{"data":{"user":{"projectV2":{"id":"YOUR_PROJECT_ID"}}}}

Save YOUR_PROJECT_ID for later. You will need it on the Step 3.

Step 1: Prepare the Google Spreadsheet

Open your trusty Google Spreadsheet and head over to the script editor. To access the script editor, simply click on “Extensions” in the menu bar, then select “Apps Script.” This will take you to the lovely kingdom of the Google Apps Script editor.

Step 2: Copy and Paste the Magic Code

Now, copy the spellbinding code snippet provided and paste it into the script editor. Don’t worry, no wands required!

// Replace 'YOUR_ACCESS_TOKEN' with your GitHub access token
var accessToken = 'YOUR_ACCESS_TOKEN';

function onOpen() {
  createMenu();
}

function createMenu() {
   var menu = SpreadsheetApp.getUi().createMenu("Actions");
   menu.addItem("Get GitHub Project Issues", "fetchGitHubData");
   menu.addToUi();
}

function prepareSheet(headers)
{
  var sheet = SpreadsheetApp.getActiveSheet();

  sheet.clear();

  sheet.getRange(sheet.getLastRow() + 1,1,1,headers.length).setValues([headers]);
  sheet.getRange(1,1,1,headers.length).setFontWeight("bold");

  sheet.setFrozenRows(1);
}

function fetchGitHubData() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  projectId = sheet.getName().split('/')[0]

  var sheet_headers = ["number","repo","title","author","assignees","assignee_count","status","iteration","duedate","weight","spent","priority","total_weight"]
  //clean active sheet, set headers, forzen first row
  prepareSheet(sheet_headers)

  var url = 'https://api.github.com/graphql';

  var headers = {
    Authorization: 'Bearer ' + accessToken,
    'Content-Type': 'application/json'
  };

  var query = `query($project_id: ID!, $after: String){
    node(id: $project_id) {
        ... on ProjectV2 {
          items(first: 100, after:$after) {
            pageInfo {
                hasNextPage
                endCursor
            }
            nodes{
              creator{
                  login
              }
              id
              fieldValues(first: 20) {
                nodes{                
                  ... on ProjectV2ItemFieldTextValue {
                    text
                    field {
                      ... on ProjectV2FieldCommon {
                        name
                      }
                    }
                  }
                  ... on ProjectV2ItemFieldUserValue {
                    users(first: 10){
                        nodes{
                            login
                        }
                    }
                    field {
                      ... on ProjectV2FieldCommon {
                        name
                      }
                    }
                  }
                  ... on ProjectV2ItemFieldIterationValue {
                    title
                    field {
                      ... on ProjectV2FieldCommon {
                        name
                      }
                    }
                  }
                  ... on ProjectV2ItemFieldRepositoryValue {
                    repository {
                        name
                    }
                    field {
                      ... on ProjectV2FieldCommon {
                        name
                      }
                    }
                  }
                  ... on ProjectV2ItemFieldNumberValue {
                    number
                    field {
                      ... on ProjectV2FieldCommon {
                        name
                      }
                    }
                  }
                  ... on ProjectV2ItemFieldDateValue {
                    date
                    field {
                      ... on ProjectV2FieldCommon {
                        name
                      }
                    }
                  }
                  ... on ProjectV2ItemFieldSingleSelectValue {
                    name
                    field {
                      ... on ProjectV2FieldCommon {
                        name
                      }
                    }
                  }
                }              
              }
              content{              
                ... on DraftIssue {
                  title
                  body
                  assignees(first: 10) {
                    nodes{
                      login
                    }
                  }
                }
                ...on Issue {
                  number
                  title
                  body
                  assignees(first: 10) {
                    nodes{
                      login
                    }
                  }
                }
              }
            }
          }
        }
      }
    }`;

  var items = [];
  var hasNextPage = true;
  var endCursor = '';

  while(hasNextPage){

    const variables = {
      project_id: projectId,
      after: endCursor
    };

    var options = {
      method: 'post',
      headers: headers,
      payload: JSON.stringify({ query: query, variables: variables }),
    };

    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response.getContentText());

    var tableData = [];
    items = items.concat(data.data.node.items.nodes);
    hasNextPage = data.data.node.items.pageInfo.hasNextPage;
    endCursor = data.data.node.items.pageInfo.endCursor;
  }



  // Extract the desired fields from the GraphQL response
  for (var i = 0; i < items.length; i++) {
    var item = items[i];

    var fieldValueNodes = item.fieldValues.nodes;
    var fieldValues = {};

    var repository='-';
    var assignees='';
    var due='';
    var status='';
    var priority='';
    var iteration='';
    var weight='';
    var spent='';

    for (var j = 0; j < fieldValueNodes.length; j++) {
      var fieldValue = fieldValueNodes[j];
      if(fieldValue.field){
        if(fieldValue.field.name == "Assignees"){
          assignees = fieldValue.users.nodes.map(function(assignee) {
          return assignee.login;
        }).join(', ');
        } else if (fieldValue.field.name == "Repository"){
          repository = fieldValue.repository.name;
        } else if (fieldValue.field.name == "Due date" || fieldValue.field.name == "Due Date"){
          due = fieldValue.date;
        } else if (fieldValue.field.name == "Status"){
          status = fieldValue.name;
        } else if (fieldValue.field.name == "Priority"){
          priority = fieldValue.name;
        } else if (fieldValue.field.name == "Iteration"){
          iteration = fieldValue.title;
        } else if (fieldValue.field.name == "Weight"){
          weight = fieldValue.number;
        } else if (fieldValue.field.name == "Spent time"|| fieldValue.field.name == "Hours Spent"){
          spent = fieldValue.number;
        }
      }
    }

    var content = item.content;

    var assignee_count = (assignees === null || assignees == "") ? 0 : assignees.split(",").length;
    var total_weight = weight * assignee_count;

    var row = [
      content.number,
      repository,
      content.title,
      item.creator.login,
      assignees,
      assignee_count,
      status,
      iteration,
      due,
      weight,
      spent,
      priority,
      total_weight,
    ];

    tableData.push(row);
  }

  // Set the values in the table range
  if (tableData.length > 0) {
    sheet.getRange(2, 1, tableData.length, tableData[0].length).setValues(tableData);
  }

  //resize columns
  sheet.autoResizeColumns(2, 25);
  sheet.setColumnWidth(3,400); 
}

Remember to replace YOUR_ACCESS_TOKEN in the code snippet with your actual access token.

The code contains a function called “prepareSheet.” This function prepares the spreadsheet by clearing the active sheet, setting up headers, and freezing the first row. You can customize the headers to your liking by tweaking the ‘sheet_headers’ array in the code.

A GraphQL query unleashes its powers to retrieve the mystical project items from GitHub. This query snags essential fields such as issue number, repository, title, author, assignees, status, iteration, due date, weight, and more. It even handles pagination to tame the mightiest of project item collections!

If you like, you can review the script and make any necessary modifications to suit your specific needs. For example, you might want to add additional fields or customize the column widths.

Step 3: Run!

With the script saved, return to your Google Spreadsheet and prepare for the grand finale.

Rename the active sheet with YOUR_PROJECT_ID and you can add at the end ‘/YouProjectName’ for easy reference:

YOUR_PROJECT_ID/MyProjectName

You may need to reload the page and wait a couple of seconds to see how a new menu is revealed just next to “Help” called “Actions”; you will find there a “Get GitHub Project Issues” option.

As if by magic, the function will spring to life, retrieving your GitHub project issues and elegantly populating them into the spreadsheet.

The first time you run the script, you may be prompted to authorize the script to access your Google Sheets. Follow the instructions to grant the necessary permissions.

Step 4: Take some rest, you deserve it

Congratulations, mighty project managers and software developers! By harnessing the GitHub API’s incredible powers and the mystical abilities of the Google Spreadsheet, you can effortlessly retrieve project issues and manage them with confidence.

Remember, even though we may joke about having everything under control, this code snippet empowers you to stay on top of your project management game. So go forth, conquer those issues, and may your projects always run smoothly!

Now, let the magic unfold as you embark on this epic adventure of GitHub project issue retrieval. And remember, stay confident, keep coding, and don’t forget to have some fun along the way!

DEV Community: AleGuerra

Retrieve GitHub Project Issues to a Google Spreadsheet

Step 1: Prepare the Google Spreadsheet

Step 2: Copy and Paste the Magic Code

Step 3: Run!

Step 4: Take some rest, you deserve it