def content():
org_info = list_orgs.main()
num_orgs = len(org_info)
rows = str(num_orgs + 2)
spreadsheet = base.get_spreadsheet()
# open sheet
try:
worksheet = spreadsheet.add_worksheet(title="Content By Org",
rows=(num_orgs + 4),
cols=10)
# label
worksheet.update_acell('A2', 'Organization')
worksheet.update_acell('B2', 'Documents')
worksheet.update_acell('C2', 'Maps/Infographics')
worksheet.update_acell('D2', 'Events')
worksheet.update_acell('E2', 'Assessments')
worksheet.update_acell('F2', 'Total Content')
except APIError:
worksheet = spreadsheet.worksheet("Content By Org")
# organizations
name_cells = worksheet.range('A3:A' + rows)
index = 0
for cell in name_cells:
cell.value = org_info[index][1]
index += 1
worksheet.update_cells(name_cells)
return num_orgs, org_info, worksheet, rows
def spaces(url):
content = base.open_url(url)
spaces = []
times = []
for data in content['data']:
last_modified = int(data['changed'])
last_modified = time.strftime("%Y %d %b", time.localtime(last_modified))
spaces.append(data['label'])
times.append(last_modified)
spreadsheet = base.get_spreadsheet()
try:
worksheet = spreadsheet.add_worksheet(title="Global Spaces", rows=len(spaces)+10, cols="5")
except APIError:
worksheet = spreadsheet.worksheet("Global Spaces")
# Pull time of program execution and update
geneva = timezone('Etc/GMT-2')
current_time = datetime.now(geneva)
formatted_time = current_time.strftime("%d %m %Y %H:%M:%S")
updated = "Sheet Last Updated: " + formatted_time + ' (GMT+2)'
worksheet.update_acell('A1', updated)
# label
worksheet.update_acell('A2','Space')
worksheet.update_acell('B2','Last Modified')
# Select a range
space_list = worksheet.range('A3:A'+str(len(spaces)+2))
time_list = worksheet.range('B3:B'+str(len(times)+2))
index = 0
for cell in space_list: #update id
cell.value = spaces[index]
index+=1
index = 0
for cell in time_list: #update names
cell.value = times[index]
index+=1
# Update in batch - avoids API timeout problem
worksheet.update_cells(space_list)
worksheet.update_cells(time_list)
def contribs_by_year():
years = {}
# current year
utc = timezone('UTC')
tstamp = int(datetime(2019, 1, 1, 0, 0, 0, tzinfo=utc).timestamp())
tstamp = str(tstamp)
# events
events_url = 'https://www.humanitarianresponse.info/en/api/v1.0/events?fields=organizations.label,created&filter[created][value]=' + tstamp + '&filter[created][operator]=>='
content = base.open_url(events_url)
years = work(content, years)
years = next_page(content, years)
# docs
docs_url = 'https://www.humanitarianresponse.info/api/v1.0/documents?fields=organizations.label,created&filter[created][value]=' + tstamp + '&filter[created][operator]=>='
content = base.open_url(docs_url)
years = work(content, years)
years = next_page(content, years)
# maps/infographics
maps_url = 'https://www.humanitarianresponse.info/api/v1.0/infographics?fields=organizations.label,created&filter[created][value]=' + tstamp + '&filter[created][operator]=>='
content = base.open_url(maps_url)
years = work(content, years)
years = next_page(content, years)
spreadsheet = base.get_spreadsheet()
try:
worksheet = spreadsheet.add_worksheet(
title="Contributing Orgs By Year", rows=100, cols=10)
# label
worksheet.update_acell('A2', '2012')
worksheet.update_acell('B2', '2013')
worksheet.update_acell('C2', '2014')
worksheet.update_acell('D2', '2015')
worksheet.update_acell('E2', '2016')
worksheet.update_acell('F2', '2017')
worksheet.update_acell('G2', '2018')
worksheet.update_acell('H2', '2019')
except APIError:
worksheet = spreadsheet.worksheet("Contributing Orgs By Year")
# Pull time of program execution and update
geneva = timezone('Etc/GMT-2')
current_time = datetime.now(geneva)
formatted_time = current_time.strftime("%d %m %Y %H:%M:%S")
updated = "Sheet Last Updated: " + formatted_time + ' (GMT+2)'
worksheet.update_acell('A1', updated)
# 2012
#update_worksheet_year(years, '2012', 'A3:A', worksheet)
# 2013
#update_worksheet_year(years, '2013', 'B3:B', worksheet)
# 2014
#update_worksheet_year(years, '2014', 'C3:C', worksheet)
# 2015
#update_worksheet_year(years, '2015', 'D3:D', worksheet)
# 2016
#update_worksheet_year(years, '2016', 'E3:E', worksheet)
# 2017
#update_worksheet_year(years, '2017', 'F3:F', worksheet)
# 2018
#update_worksheet_year(years, '2018', 'G3:G', worksheet)
# 2019
update_worksheet_year(years, '2019', 'H3:H', worksheet)
def contrib_orgs(TEXT_FILE, NAME_OF_SHEET):
lines = [line.rstrip('\n')
for line in open(TEXT_FILE)] # read line by line
spreadsheet = base.get_spreadsheet()
try:
worksheet = spreadsheet.add_worksheet(title=NAME_OF_SHEET,
rows=len(lines),
cols=10)
except APIError:
worksheet = spreadsheet.worksheet(NAME_OF_SHEET)
test = lines[70:]
index = 0
orgs = []
for line in test:
if "<caption>Name: " in line:
# ignore admins
name = line.split('>', 2)[2]
name = name.split('<')[0]
if name == "Marina Colozzi" or name == "Guillaume Viguier" or name == "Adrian Ciancio":
index += 1
continue
raw_org = test[index + 18]
raw_org = raw_org.strip("</td>")
raw_org = raw_org.strip(" ")
if "'" in raw_org:
raw_org = raw_org.replace("'", "'")
if "," in raw_org:
raw_orgs = raw_org.split(",")
for org in raw_orgs:
if org[0] == " ":
orgs.append(org[1:])
else:
orgs.append(org)
else:
orgs.append(raw_org)
index += 1
org_counts = Counter(orgs)
orgo = org_counts.most_common()
cells = str(len(orgo) + 1)
# Pull time of program execution and update
geneva = timezone('Etc/GMT-2')
current_time = datetime.now(geneva)
formatted_time = current_time.strftime("%d %m %Y %H:%M:%S")
updated = "Sheet Last Updated: " + formatted_time + ' (GMT+2)'
worksheet.update_acell('A1', updated)
# Label
worksheet.update_acell('A2', 'Organization')
worksheet.update_acell('B2', 'Count')
# Select a range
org_list = worksheet.range('A3:A' + cells)
num_list = worksheet.range('B3:B' + cells)
index = 0
for cell in org_list: #update orgs
cell.value = orgo[index][0]
index += 1
index = 0
for cell in num_list: #update nums
cell.value = orgo[index][1]
index += 1
# Update in batch - to avoid hitting API request limit
worksheet.update_cells(org_list)
worksheet.update_cells(num_list)
def ops_by_status(url):
content = base.open_url(url)
op_status = {}
op_status = op_stat(content, op_status)
op_status = next_page(content, op_status)
# Dict to list
mylist = []
for key, value in op_status.items():
temp = [key, value]
mylist.append(temp)
rows = len(mylist) + 5
spreadsheet = base.get_spreadsheet()
# Upload to Sheets
try:
worksheet = spreadsheet.add_worksheet(title="Ops By Status",
rows=str(rows),
cols="10")
except APIError:
worksheet = spreadsheet.worksheet("Ops By Status")
# Pull time of program execution and update
geneva = timezone('Etc/GMT-2')
current_time = datetime.now(geneva)
formatted_time = current_time.strftime("%d %m %Y %H:%M:%S")
updated = "Sheet Last Updated: " + formatted_time + ' (GMT+2)'
worksheet.update_acell('A1', updated)
# Label
worksheet.update_acell('A2', 'Operation')
worksheet.update_acell('B2', 'Status')
worksheet.update_acell('C2', 'Last Modified')
# Select a range
org_list = worksheet.range('A3:A' + str(len(op_status) + 2))
status_list = worksheet.range('B3:B' + str(len(op_status) + 2))
changed_list = worksheet.range('C3:C' + str(len(op_status) + 2))
# Update organization
index = 0
for cell in org_list:
cell.value = mylist[index][0]
index += 1
# Update status
index = 0
for cell in status_list:
cell.value = mylist[index][1][0]
index += 1
# Update time changed
index = 0
for cell in changed_list:
cell.value = mylist[index][1][1]
index += 1
# Update in batch (to avoid API timeout)
worksheet.update_cells(org_list)
worksheet.update_cells(status_list)
worksheet.update_cells(changed_list)
def compiling(dict):
spreadsheet = base.get_spreadsheet()
# Upload to Sheets
try:
worksheet = spreadsheet.add_worksheet(title="Groups By Active Operation", rows=500, cols=10)
except APIError:
worksheet = spreadsheet.worksheet("Groups By Active Operation")
# Pull time of program execution and update
geneva = timezone('Etc/GMT-2')
current_time = datetime.now(geneva)
formatted_time = current_time.strftime("%d %m %Y %H:%M:%S")
updated = "Sheet Last Updated: " + formatted_time + ' (GMT+2)'
worksheet.update_acell('A1', updated)
# label
worksheet.update_acell('A2','Operation')
worksheet.update_acell('B2','Name')
worksheet.update_acell('C2','Type')
worksheet.update_acell('D2','Last Modified')
worksheet.update_acell('E2','Created')
worksheet.update_acell('F2','Published?')
# Dict to list
mylist = []
for key, value in dict.items():
temp = [key,value]
mylist.append(temp)
# total number of rows depends on total number of groups
rows = 0
for operation in mylist:
rows += len(operation[1])
rows = str(rows+2)
op_list = worksheet.range('A3:A'+rows)
name_list = worksheet.range('B3:B'+rows)
type_list = worksheet.range('C3:C'+rows)
time_list = worksheet.range('D3:D'+rows)
create_list = worksheet.range('E3:E'+rows)
pub_list = worksheet.range('F3:F'+rows)
op_list_index = 0
index = 0
for operation in mylist:
within_op_index = 0
op_name = operation[0]
num_clusters = len(operation[1])
same_op = True
cell = op_list[op_list_index]
name_cell = name_list[index]
type_cell = type_list[index]
time_cell = time_list[index]
create_cell = create_list[index]
pub_cell = pub_list[index]
clus_name = operation[1][within_op_index][0]
type_name = operation[1][within_op_index][1]
time_name = operation[1][within_op_index][2]
create_name = operation[1][within_op_index][3]
pub_name = operation[1][within_op_index][4]
name_cell.value = clus_name
type_cell.value = type_name
time_cell.value = time_name
create_cell.value = create_name
pub_cell.value = pub_name
cluster_index = 0
while same_op:
cell.value = op_name
op_list_index+=1
cluster_index+=1
index+=1
within_op_index+=1
if cluster_index == num_clusters:
same_op = False
continue
cell = op_list[op_list_index]
name_cell = name_list[index]
type_cell = type_list[index]
time_cell = time_list[index]
create_cell = create_list[index]
pub_cell = pub_list[index]
clus_name = operation[1][within_op_index][0]
type_name = operation[1][within_op_index][1]
time_name = operation[1][within_op_index][2]
create_name = operation[1][within_op_index][3]
pub_name = operation[1][within_op_index][4]
name_cell.value = clus_name
type_cell.value = type_name
time_cell.value = time_name
create_cell.value = create_name
pub_cell.value = pub_name
# Update in batch - avoids API timeout problem
worksheet.update_cells(op_list)
worksheet.update_cells(name_list)
worksheet.update_cells(type_list)
worksheet.update_cells(time_list)
worksheet.update_cells(create_list)
worksheet.update_cells(pub_list)
exit()