/
validate.py
169 lines (147 loc) · 5 KB
/
validate.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
# Build addresses from house numbers and postcodes stored in Google Sheets
# Written by Jake Charman
# Import libraries included by default.
import pip
import requests
import json
import os
import sys
def setup():
# Print program information.
print('##############################')
print('# Address Validator 0.1 #')
print('# By Jake Charman #')
print('# www.jakecharman.co.uk #')
print('# software@jakecharman.co.uk #')
print('##############################')
# Import extra libraries. Install if necessary.
print('Importing libraries')
try:
import gspread
except ImportError:
print("Failed to import gspread. Installing")
pip.main(['install', 'gspread'])
import gspread
setup.gspread = gspread
try:
from oauth2client.service_account import ServiceAccountCredentials
except ImportError:
print("Failed to import oauth2client. Installing")
pip.main(['install', 'oauth2client'])
from oauth2client.service_account import ServiceAccountCredentials
try:
globals()["tqdm"] = __import__("tqdm.tqdm")
except ImportError:
print("Failed to import tqdm. Installing")
pip.main(['install', 'tqdm'])
globals()["tqdm"] = __import__("tqdm")
# Define the getaddress.io API key.
print("Getting API key from 'api_key.txt'")
try:
apikeyfile = open("api_key.txt", "r")
setup.apikey = str(apikeyfile.read())
except IOError:
print("File error, exiting")
exit()
# Create a Google Drive API client.
print('Creating Google API client')
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
setup.client = gspread.authorize(creds)
#Validate the addresses.
def validateaddresses(sheetname):
# Create an empty list.
output = []
print('Opeining sheet "%(sheet)s"' % {"sheet": (sheetname)})
# Find the sheet by name.
sheet = setup.client.open(sheetname).sheet1
# Get column/row numbers
print("Column number containing first line of address?")
print("A = 1")
print("B = 2")
print("...")
addresscol = input(" ")
print("Column number containing postcode?")
print("A = 1")
print("B = 2")
print("...")
postcodecol = input(" ")
startrow = input("Row number to start at: ")
# Extract the required values from the sheet.
print('Pulling data from sheet')
postcodes = sheet.col_values(int(postcodecol))
postcodes = [p for p in postcodes if p]
addresses = sheet.col_values(int(addresscol))
print('Validating Addresses')
# For each postcode, call the API and retrieve data.
for i in tqdm.tqdm(range((int(startrow) - 1), len(postcodes))):
# Omit blank cells.
if postcodes[i] != "" or addresses[i] != "":
# Take only the first word of the address string.
number = addresses[i].split(' ', 1)[0]
# Remove spaces from the postcode.
postcode = postcodes[i].replace(" ", "")
# Print the postcode.
# print(postcode)
# Query the API.
response = requests.get(
"https://api.getaddress.io/v2/uk/%(postcode)s/%(number)s?api-key=%(apikey)s" % {
"postcode": (postcode),
"number": (number),
"apikey": (setup.apikey)
}
)
# Retrieve and format the data.
try:
data = (json.loads(response.text)['Addresses'][0].replace(", ", "\n"))
data = os.linesep.join([s for s in data.splitlines() if s])
except:
data = ("BAD INPUT")
# Print the address.
# print(data)
# Append the data to the list.
output.append(data + "\n" + postcode)
# Return the list of data.
return (output)
#Upload output to sheet.
def uploadoutput(sheetname, output):
print('Opeining sheet "%(sheet)s"' % {"sheet": (sheetname)})
# Find the sheet by name.
sheet = setup.client.open(sheetname)
print('Creating new worksheet "Validated Addresses"')
# Create a new worksheet.
worksheet = sheet.add_worksheet(title="Validated Addresses", rows=str(len(output)), cols="1")
print("Opening worksheet")
# Open the newly created worksheet.
worksheet = sheet.worksheet("Validated Addresses")
print("Adding values to worksheet")
# Add the data to the newly created cells.
for i in tqdm.tqdm(range(0, len(output))):
worksheet.update_cell(i + 1, 1, output[i])
# UI.
try:
# Run the program.
if sys.argv[1] == "-r":
try:
# Pass the second argument to the program provided it is a string.
if isinstance(sys.argv[2], basestring):
# Run setup
setup()
# Validate the addresses and upload them.
uploadoutput(sys.argv[2], validateaddresses(sys.argv[2]))
else:
print("INVALID INPUT! Use -h for help")
except IndexError:
print("INVALID INPUT! Use -h for help")
# Display help.
elif sys.argv[1] == "-h":
print("Usage: -r <sheet>")
print(" ")
print("INSTRUCTIONS FOR USE:")
print("1) Create a Google service account")
print("2) Share the Google Sheet with your Google service account")
print("3) Place your API key in 'api_key.txt' in the same directory")
print("4) Place your client secret in client_secret.json in the same directory")
print("5) Run this program with the usage above")
except IndexError:
print("INVALID INPUT! Use -h for help")