An Interest In:
Web News this Week
- April 28, 2024
- April 27, 2024
- April 26, 2024
- April 25, 2024
- April 24, 2024
- April 23, 2024
- April 22, 2024
Some of Our Sources
- Team Treehouse
- Noupe
- Wal You
- Freelance Switch
- Web Resource Source
- Android Dissected
- Codrops
- Daily Now
- Hashedout
- The Verge
Help Webnuz
Referal links:
Retrieve GitHub Project Issues to a Google Spreadsheet
Hey there, fellow project managers! Have you ever found yourself confidently saying,
Im 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, Ill walk you through the steps to retrieve GitHub project issues and effortlessly populate them into a Google Spreadsheet using the GitHub GraphQL API. So, lets dive in and conquer those project issues like the capable managers we are!
Before we embark on this exciting journey, lets 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 youd 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"}}}}Or
{"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. Dont worry, no wands required!
// Replace 'YOUR_ACCESS_TOKEN' with your GitHub access tokenvar 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 APIs 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 dont forget to have some fun along the way!
Original Link: https://dev.to/aleguerra05/retrieve-github-project-issues-to-a-google-spreadsheet-pn5
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To