Created
September 20, 2023 07:08
-
-
Save nulconaux/fd8271440f993eaf1767b5e2824a5937 to your computer and use it in GitHub Desktop.
Jira attachments size report
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| var DOMAIN = '<DOMAIN>.atlassian.net'; | |
| var EMAIL = '<EMAIL>'; | |
| var TOKEN = '<TOKEN>'; | |
| function fetchJiraData() { | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| // Remove all sheets except the first one | |
| removeAllSheetsExceptFirst(ss); | |
| // Detailed Sheet | |
| var detailedSheet = ss.getSheetByName("Detailed") || ss.insertSheet("Detailed"); | |
| detailedSheet.clearContents(); | |
| detailedSheet.appendRow(['Ticket Link', 'Attachment', 'Size (MB)', 'Status', 'Date Created', 'Date Closed']); | |
| // Summary Sheet | |
| var summarySheet = ss.getSheetByName("Summary") || ss.insertSheet("Summary"); | |
| summarySheet.clearContents(); | |
| summarySheet.appendRow(['Project', 'Total Size (MB)']); | |
| var startAt = 0; | |
| var maxResults = 100; | |
| var tickets = []; | |
| while (true) { | |
| var data = getJiraDataFromAPI(startAt, maxResults); | |
| if (data.length === 0) { | |
| break; | |
| } | |
| tickets = tickets.concat(data); | |
| startAt += maxResults; | |
| } | |
| var projectSummary = {}; | |
| var detailedData = []; | |
| tickets.forEach(function(ticket) { | |
| var projectKey = ticket.ticket_id.split("-")[0]; // Extract project key from ticket ID | |
| var ticketLink = '=HYPERLINK("https://' + DOMAIN + '/browse/' + ticket.ticket_id + '", "' + ticket.ticket_id + '")'; | |
| detailedData.push([ticketLink, ticket.attachment, bytesToMegaBytes(ticket.size), ticket.status, ticket.dateCreated, ticket.dateClosed]); | |
| if (!projectSummary[projectKey]) { | |
| projectSummary[projectKey] = 0; | |
| } | |
| projectSummary[projectKey] += ticket.size; | |
| }); | |
| // Sort the detailedData array by size in descending order | |
| detailedData.sort(function(a, b) { | |
| return b[2] - a[2]; // Compare the "Size (MB)" column | |
| }); | |
| detailedSheet.getRange(2, 1, detailedData.length, 6).setValues(detailedData); | |
| // Highlight the top 150 rows in red based on attachment size | |
| var rangeToHighlight = Math.min(150, detailedData.length); | |
| detailedSheet.getRange(2, 1, rangeToHighlight, 6).setBackground("red"); | |
| var summaryData = []; | |
| for (var project in projectSummary) { | |
| summaryData.push([project, bytesToMegaBytes(projectSummary[project])]); | |
| } | |
| summarySheet.getRange(2, 1, summaryData.length, 2).setValues(summaryData); | |
| // Create Pie Chart for Project Summary | |
| var chartBuilder = summarySheet.newChart(); | |
| var chart = chartBuilder | |
| .setChartType(Charts.ChartType.PIE) | |
| .addRange(summarySheet.getRange(2, 1, summarySheet.getLastRow(), 2)) | |
| .setPosition(5, 5, 5, 5) | |
| .build(); | |
| summarySheet.insertChart(chart); | |
| // Create Pie Chart for attachment types | |
| var attachmentTypeSummary = {}; | |
| tickets.forEach(function(ticket) { | |
| var attachmentType = ticket.attachment.split('.').pop(); // Extract file extension to determine type | |
| if (!attachmentTypeSummary[attachmentType]) { | |
| attachmentTypeSummary[attachmentType] = 0; | |
| } | |
| attachmentTypeSummary[attachmentType] += ticket.size; | |
| }); | |
| // Create Pie Chart for attachment types | |
| var attachmentTypeSummary = {}; | |
| var attachmentSizesByType = {}; | |
| tickets.forEach(function(ticket) { | |
| var attachmentType = ticket.attachment.split('.').pop(); // Extract file extension to determine type | |
| if (!attachmentTypeSummary[attachmentType]) { | |
| attachmentTypeSummary[attachmentType] = 0; | |
| attachmentSizesByType[attachmentType] = []; | |
| } | |
| attachmentTypeSummary[attachmentType] += ticket.size; | |
| attachmentSizesByType[attachmentType].push(ticket.size); | |
| }); | |
| // Create Pie Chart for Attachment Types | |
| var attachmentTypeSheet = ss.getSheetByName("Attachment Types") || ss.insertSheet("Attachment Types"); | |
| attachmentTypeSheet.clearContents(); | |
| attachmentTypeSheet.appendRow(['Attachment Type', 'Total Size (MB)', 'Median Size (MB)']); | |
| var attachmentTypeData = []; | |
| for (var type in attachmentTypeSummary) { | |
| var medianSize = calculateMedian(attachmentSizesByType[type]); | |
| attachmentTypeData.push([type, bytesToMegaBytes(attachmentTypeSummary[type]), bytesToMegaBytes(medianSize)]); | |
| } | |
| attachmentTypeSheet.getRange(2, 1, attachmentTypeData.length, 3).setValues(attachmentTypeData); | |
| var attachmentTypeChartBuilder = attachmentTypeSheet.newChart(); | |
| var attachmentTypeChart = attachmentTypeChartBuilder | |
| .setChartType(Charts.ChartType.PIE) | |
| .addRange(attachmentTypeSheet.getRange(2, 1, attachmentTypeSheet.getLastRow(), 2)) | |
| .setPosition(5, 5, 5, 5) | |
| .build(); | |
| attachmentTypeSheet.insertChart(attachmentTypeChart); | |
| } | |
| function removeAllSheetsExceptFirst(ss) { | |
| var sheets = ss.getSheets(); | |
| for (var i = sheets.length - 1; i > 0; i--) { | |
| ss.deleteSheet(sheets[i]); | |
| } | |
| } | |
| function calculateMedian(values) { | |
| if (values.length === 0) return 0; | |
| values.sort(function(a, b) { | |
| return a - b; | |
| }); | |
| var half = Math.floor(values.length / 2); | |
| if (values.length % 2) { | |
| return values[half]; | |
| } | |
| return (values[half - 1] + values[half]) / 2.0; | |
| } | |
| function bytesToMegaBytes(bytes) { | |
| return (bytes / (1024 * 1024)).toFixed(2); | |
| } | |
| function getJiraDataFromAPI(startAt, maxResults) { | |
| var url = "https://" + DOMAIN + "/rest/api/3/search?jql=attachments%20is%20not%20empty&fields=attachment,status,created,resolutiondate&maxResults=" + maxResults + "&startAt=" + startAt; | |
| var headers = { | |
| "Accept": "application/json", | |
| "Authorization": "Basic " + Utilities.base64Encode(EMAIL + ":" + TOKEN) | |
| }; | |
| var options = { | |
| "method": "GET", | |
| "headers": headers | |
| }; | |
| var response = UrlFetchApp.fetch(url, options); | |
| var json = JSON.parse(response.getContentText()); | |
| var tickets = []; | |
| json.issues.forEach(function(issue) { | |
| var ticket_id = issue.key; | |
| var status = issue.fields.status.name; | |
| var dateCreated = issue.fields.created; | |
| var dateClosed = issue.fields.resolutiondate; | |
| issue.fields.attachment.forEach(function(attachment) { | |
| tickets.push({ | |
| ticket_id: ticket_id, | |
| attachment: attachment.filename, | |
| size: attachment.size, | |
| status: status, | |
| dateCreated: dateCreated, | |
| dateClosed: dateClosed | |
| }); | |
| }); | |
| }); | |
| return tickets; | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment