-
Notifications
You must be signed in to change notification settings - Fork 4
/
update_JIRA_google_sheet.py
181 lines (139 loc) · 6.08 KB
/
update_JIRA_google_sheet.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
'''
This code is used to produce a Google sheet with the list of open JIRA ticket.
1) download the JIRA tickets from https://its.cern.ch/jira
2) preparare the file to be uploaded
3) update the google sheet with the new file
Example:
python3 queryJIRA.py STEAM
'''
# JIRA username and password
token = "XXXXX"
tokenFile = open("/afs/cern.ch/user/s/sdonato/private/jira.txt")
token = tokenFile.read()[:-1]
maxResults = 100000 # max results in JIRA query
dateLimit = "2020-01-01" # get JIRA tickets created after dateLimit
deleteAll = False ##delete all worksheets, useful for use the same style among all worksheets
# JIRA ticket query
jql_query = "project = CMSHLT AND status != Closed AND createdDate >%s"%dateLimit
# JIRA ticket API url, corresponding to https://its.cern.ch/jira
url = "https://its.cern.ch/jira/rest/api/2/search?jql=" + jql_query + "&maxResults=%d"%maxResults
# Google API credentials:
api_json_credentials = '/home/sdonato/CMS/TSG/sdonato-tsg-d2a8d20b682c.json'
## Credentials done from https://console.cloud.google.com/apis/credentials -> Service Accounts -> Keys -> Add key -> Create keys -> JSON
# spreadsheet to be edited
spreadsheetId = '16zfg5fJXey9li4z7MJB9qonVTKfRKTtOHDNY-lrrhZg' # Please set spreadsheet ID. ([email protected] needs to have the rights to edit it)
##################################################################
import requests
import json
import sys
## get selected groups. [] = all
selectedGroups = []
if __name__ == "__main__":
if len(sys.argv)>1:
selectedGroups = ' '.join(sys.argv[1:])
selectedGroups = selectedGroups.split(",")
print(selectedGroups)
excludeComponents = ["FOG Completed"]
# Run JIRA Ticket API query
print("Query: %s"%url)
response = requests.get(url, headers={"Authorization": "Bearer " + token})
#response.headers.update({"Authorization", "Bearer " + token})
# Check the response
if response.status_code != 200:
raise ValueError("Failed to retrieve results from JIRA: {}".format(response.text))
# Convert json in list of jira tickets
data = json.loads(response.text)
print(len(data['issues']), " JIRA tickets found.")
JIRAs = {}
# make a list of JIRA tickets per each component. Save [key , status, created , lastView, summary, ", ".join(components)] per each JIRA
for issue in data['issues']:
components = issue['fields']['components']
components = [i['name'] for i in components]
lastView = issue['fields']['lastViewed']
created = issue['fields']['created']
summary = issue['fields']['summary']
status = issue['fields']['status']['name']
created = created.split("T")[0]
key = issue['key']
key = '=hyperlink("https://its.cern.ch/jira/browse/%s", "%s")'%(key, key)
if not lastView: lastView = str(created)
else:
lastView = lastView.split("T")[0]
for component in components:
if not component in JIRAs: JIRAs[component] = list()
JIRAs[component].append([key , status, created , lastView, summary, ", ".join(components)])
################ prepare worksheets #########################################
## each group will have a worksheet. This is the list of components corresponding to each group
groups = {
"STEAM": ['STEAM tasks', 'PerformancePlots', 'HLT prescales'],
"STORM": ['HLT configurations', 'STORM tasks', 'ConfDB GUI Updates'],
"FOG": ['FOG', 'L1 prescales', 'RateMon'],
"L1T": ['L1 trigger', 'L1 prescales'],
"DPG": ['ECAL DPG', 'HCAL DPG', 'PPS DPG', 'Tracker DPG', 'CT-PPS PAG', 'FSQ PAG'],
"AlCa": ['AlCa'],
"Scouting": ['Scouting'],
}
for comp in JIRAs:
if "POG" in comp or "PAG" in comp:
groups[comp] = [comp]
## remove minor/old PAGs
if 'CT-PPS PAG' in groups: del groups['CT-PPS PAG']
if 'FSQ PAG' in groups: del groups['FSQ PAG']
## define "Others group"
groups['Others'] = []
for comp in JIRAs:
if not (comp in [i for a in groups.values() for i in a]):
groups['Others'].append(comp)
## first row of the worksheet:
labelRow = ["JIRA", "Status", "Created", "Modified", "Title", "Components"]
#make worksheets
sheets = {}
for group in groups:
jiras = []
sheets[group]=[labelRow]
for comp in groups[group]:
JIRAs[component].sort(key=lambda x: x[0], reverse=True)
if comp in JIRAs:
for jira in JIRAs[comp]:
if not (jira in jiras):
jiras.append(jira)
sheets[group] .append(jira)
################ upload on Google Drive #########################################
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# Utilizzare le credenziali dell'account di servizio per autenticarsi con Google Sheets API
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(api_json_credentials, scope)
gc = gspread.authorize(credentials)
sh = gc.open_by_key(spreadsheetId)
worksheets = [w.title for w in sh.worksheets()]
if deleteAll: ## delete all sheets except the first one.
for w in sh.worksheets()[1:]:
sh.del_worksheet(w)
worksheets = [w.title for w in sh.worksheets()]
## edit all worksheets, create them if missing
for group in sheets.keys():
if selectedGroups and not (group in selectedGroups):
# print(group, " not selected")
continue
if not (group in worksheets):
print("Creating "+group)
sh.duplicate_sheet(sh.worksheets()[0].id,new_sheet_name=group)
else:
print("Updating "+group)
ws = sh.worksheet(group)
ws.clear()
sh.values_update(
group,
params={'valueInputOption': 'USER_ENTERED'},
body={'values': sheets[group]}
)
## order worksheets
order = ['STORM', 'FOG', 'STEAM', 'Scouting', 'AlCa', 'L1T', 'DPG', 'Tracking POG', 'B-Tagging POG', 'E/Gamma POG', 'Jets/MET POG', 'Muons POG', 'Taus POG', 'B and Quarkonia PAG', 'Exotica PAG', 'B2G PAG', 'Higgs PAG', 'Standard Model PAG', 'SUSY PAG', 'Top PAG', 'Heavy Ions PAG', 'Others']
sorting = []
for el in order:
if el in worksheets:
sorting.append(sh.worksheet(el))
sh.reorder_worksheets(sorting)
print(sh.url)