def main(): wks = wb.worksheet('add_parse') reports = get_reports(secrets.raid_id, secrets.c_date) print('Reports retrieved') players = get_players(reports) parses = get_parses(players, []) print('Table created') update_sheet(wks, parses) print('Worksheet updated')
def main_script(): # Duplicate the Report worksheet today = dt.datetime.today().strftime('%m-%d-%y') DATA = { 'requests': [{ 'duplicateSheet': { 'sourceSheetId': int(wks.id), 'insertSheetIndex': 0, 'newSheetName': 'Report ' + today } }] } service.spreadsheets().batchUpdate(spreadsheetId=secrets.google_sheet_id, body=DATA).execute() # Collect Trello Card info table = [] print('--------------------') for b in trello.trello_boards: for l in b['lists']: list_json = requests.request( 'GET', trello.url_lists + l['id'] + '/cards' + trello.tokens).json() print(b['name']) print('--------------------') x = 0 for c in list_json: x += 1 print(str(x) + ' - ' + c['name']) card_members = [] if len(c['idMembers']) > 0: for id in c['idMembers']: card_member = get_trello_member(id) card_members.append(card_member['name']) new_row = [ b['name'], l['name'], c['name'], c['shortUrl'], str(dt.datetime.fromtimestamp(int(c['id'][0:8], 16))), str( dt.datetime.strptime(c['dateLastActivity'], '%Y-%m-%dT%H:%M:%S.%fZ')), ', '.join(card_members) ] # Append card / ticket to the table table.append(new_row) print('--------------------') # Add card info to the newly created worksheet new_wks = wb.worksheet('Report ' + today) update_sheet(new_wks, table) print('Complete!')
def main(): ''' run it ''' now = datetime.now() log = open("log.txt", "a") log.write("=================================\n") log.write("0. " + now.strftime("%Y-%m-%d-%H-%M") + "\n") #Finding the data from a map in this page https://www.oakgov.com/covid/casesByZip.html. #Url3 is the address where the map pulls its data from url3 = 'https://services1.arcgis.com/GE4Idg9FL97XBa3P/arcgis/rest/services/COVID19_Cases_by_Zip_Code_Total_Population/FeatureServer/0/query?f=json&where=1%3D1&returnGeometry=false&spatialRel=esriSpatialRelIntersects&outFields=*&orderByFields=Join_Zip_Code%20asc&resultOffset=0&resultRecordCount=200&cacheHint=true' response = get(url3) data = response.json() log.write("1. Pulled JSON\n") #The data is in the list of dictionaries called 'features' features = data['features'] #We want to pull all the variables inside the dict called attributes, so we create a new list of dictionaries that only includes the attributes new_data = [] for feature in features: new_data.append(feature['attributes']) Oakland = DataFrame(new_data) log.write("2. Created data frame\n") #### Output ################## utils.save_source_file("Oakland.json", response.text) log.write("3. Saved Source file\n") csv = Oakland.to_csv(index=False) utils.save_data_file("Oakland.csv", csv) log.write("4. Saved Data file\n") fp = Oakland[[ "Join_Zip_Code", "Total__COVID19_Cases", "Death_Count", "Total_Population_ACS_17_5YR" ]].copy(deep=True) fp.columns = ["ZIP code", "Cases", "Deaths", "Population"] fp.loc[:, "Cases"] = fp["Cases"].astype(float) fp.loc[:, "Deaths"] = fp["Deaths"].astype(float) fp.loc[:, "Population"] = fp["Population"].astype(float) fp.loc[fp["Cases"].isnull(), "Cases"] = 0.0 fp.loc[fp["Deaths"].isnull(), "Deaths"] = 0.0 fp.loc[:, "Cases per 100,000"] = 100000.0 * fp["Cases"] / fp["Population"] log.write("5. Processed Freepress\n") fpcsv = fp.to_csv(index=False) utils.save_freepress_file("Oakland.csv", fpcsv) log.write("6. Saved Freepress\n") ############################## #### save csv to sheets ###### GOOGLE_SHEET_ID = '1wtkFL6BIZQoLnUVfcr8y_2XyjSzlrPimH6A0lMjtlys' GOOGLE_SHEET_NAME = 'Oakland County' try: # we need to convert the dataframe to a list of lists # and then update via google sheets api data_to_save = [fp.columns.values.tolist()] + fp.values.tolist() sheets.update_sheet(GOOGLE_SHEET_ID, GOOGLE_SHEET_NAME, data_to_save) log.write("7. Updated google sheet\n") except Exception as exc: print(exc) log.write(f'exception writing to sheet: {exc}')
def main(): ''' run it ''' now = datetime.now() log = open("log.txt", "a") log.write("=================================\n") log.write("0. "+now.strftime("%Y-%m-%d-%H-%M")+"\n") # read the local "config" csv file with each city/municipality's display name, # name (as mapped to gis data) and 2018 population # format of the csv is # display name, name in gis data, 2018 population city_population_df = pd.read_csv('city_population.csv') url = 'https://www.waynecounty.com/gisserver/rest/services/COVID/COVID_Location/MapServer/0/query?f=json&where=1%3D1&returnGeometry=false&spatialRel=esriSpatialRelIntersects&outFields=*&orderByFields=Confirms%20desc&resultOffset=0&resultRecordCount=43' data = {} try: response = get(url) data = response.json() except Exception as exc: log.write(f'Failed to fetch data via waynecounty.com/gisserver: {exc}') return log.write("1. Pulled JSON\n") # The data is in the list of dictionaries called 'features' features = data.get('features') if not features: log.write('Failed to get "features" from json for wayne county') return new_data = [] for feature in features: new_data.append(feature.get('attributes')) wayne_df = pd.DataFrame(new_data) log.write("2. Created data frame\n") #### Output ################## utils.save_source_file("wayne.json", response.text) log.write("3. Saved Source file\n") wayce_csv = wayne_df.to_csv(index=False) utils.save_data_file("wayne.csv", wayce_csv) log.write("4. Saved Data file\n") # create a dataframe with the data from arcgis merged with the population csv file wayne_df = pd.DataFrame(new_data) wayne_full_df = pd.merge(wayne_df, city_population_df, on='Municipality') # some cleanup...converting to floats wayne_full_df.loc[:, 'Confirms'] = wayne_full_df['Confirms'].astype(float) wayne_full_df.loc[:, 'Deaths'] = wayne_full_df['Deaths'].astype(float) wayne_full_df.loc[:, '2018 Population'] = wayne_full_df['2018 Population'].str.replace(',', '').astype(float) wayne_full_df.loc[wayne_full_df['Confirms'].isnull(), 'Confirms'] = 0.0 wayne_full_df.loc[wayne_full_df['Deaths'].isnull(), 'Deaths'] = 0.0 # cases per 1,000 wayne_full_df['Cases per 1,000 population'] = 1000.0*wayne_full_df['Confirms']/wayne_full_df['2018 Population'] # save our a few of the columns to a new dataframe and # rename the columns to match what the free press would like final_df = wayne_full_df[['Display Name', 'Confirms', 'Deaths', 'Cases per 1,000 population', '2018 Population']].copy(deep=True) final_df.columns = ['City/Twp.', 'Cases', 'Deaths', 'Cases per 1,000 population', 'Population'] log.write("5. Processed Freepress\n") # save the dataframe to csv df_csv = final_df.to_csv(index=False) utils.save_freepress_file("wayne.csv", df_csv) log.write("6. Saved Freepress\n") ############################## #### save csv to sheets ###### try: # we need to convert the dataframe to a list of lists # and then update via google sheets api data_to_save = [final_df.columns.values.tolist()] + final_df.values.tolist() sheets.update_sheet(GOOGLE_SHEET_ID, GOOGLE_SHEET_NAME, data_to_save) log.write("7. Updated google sheet\n") except Exception as exc: print(exc) log.write(f'exception writing to sheet: {exc}') raise exc
def main(): wks = wb.worksheet('players') reports = get_reports(secrets.raid_id, secrets.c_date) players = get_player_rows(reports) update_sheet(wks, players)
def main(): wks = wb.worksheet('add_parse') parses = get_parses(secrets.team_players, []) print('Table created') update_sheet(wks, parses) print('Worksheet updated')
def main_script(): # Duplicate the Report worksheet today = dt.datetime.today().strftime('%m-%d-%y') DATA = {'requests': [ { 'duplicateSheet': { 'sourceSheetId': int(wks.id), 'insertSheetIndex': 0, 'newSheetName': 'Report ' + today } } ]} service.spreadsheets().batchUpdate( spreadsheetId=secrets.google_sheet_id, body=DATA).execute() # Collect Trello Card / Zendesk ticket info table = [] tickets_to_open = [] print('--------------------') for b in trello.trello_boards: for l in b['lists']: list_json = requests.request('GET', trello.url_lists + l['id'] + '/cards' + trello.tokens).json() print(b['name']) print('--------------------') x = 0 for c in list_json: x += 1 print(str(x) + ' - ' + c['name']) zendesk_url = find_zendesk_url(c) zendesk_id = zendesk_url.split('/').pop() new_row = [ b['name'], l['name'], c['name'], c['shortUrl'], str(dt.datetime.strptime(c['dateLastActivity'], '%Y-%m-%dT%H:%M:%S.%fZ')), zendesk_url ] if len(zendesk_id) <= 6: zendesk_ticket = get_zendesk_ticket(zendesk_id) try: agent = zendesk_ticket['zAssigneeName'] status = zendesk_ticket['zStatus'] hold_pending = status == 'hold' or status == 'pending' if c['dateLastActivity'] > zendesk_ticket['zLastUpdated'] and hold_pending: status = u're-opened' tickets_to_open.append(zendesk_id) if(isinstance(agent, str)): agent = u'unknown' new_row.extend((agent, status, str(dt.datetime.strptime(zendesk_ticket['zLastUpdated'], '%Y-%m-%dT%H:%M:%SZ')))) add_to_datadog_api(b['tag'], l['tag'], 'zendesk_agent:' + remove_accents(agent.replace(' ', '_').lower()), 'zendesk_status:' + status) except KeyError: new_row.extend(('unknown', 'error', 'x')) add_to_datadog_api(b['tag'], l['tag'], 'zendesk_agent:unknown', 'zendesk_status:error') else: new_row.extend(('unknown', 'missing', 'x')) add_to_datadog_api(b['tag'], l['tag'], 'zendesk_agent:unknown', 'zendesk_status:missing') # Append card / ticket to the table table.append(new_row) print('--------------------') # Add card / ticket info to the newly created worksheet new_wks = wb.worksheet('Report ' + today) update_sheet(new_wks, table) # Add metrics through datadog api metrics = [] for attr, val in datadog_api.items(): metrics.append(val) api.Metric.send(metrics) # Open Zendesk tickets where last date Trello Card updated > last Zendesk ticket updated + status = pending or hold print('Tickets to open: ', len(tickets_to_open)) if(len(tickets_to_open) > 0): confirm = open_zendesk_tickets(tickets_to_open) print(confirm) print('Complete!')