The script validates and updates 'Participant's Shipping Address' in a TSV file using Google Cloud Address Validation API, for rows with 'USA' as the country.

address_validator.py

import csv
import requests

api_key = 'API KEY'
def validate_address(address, api_key):
    """
    Validates an address using the Google Cloud Address Validation API and returns the postal address fields.

    Args:
        address (str): The address to be validated.
        api_key (str): Your Google Cloud Address Validation API key.

    Returns:
        dict: A dictionary containing the postal address fields, or None if the address is invalid.
    """
    # Construct the API URL with the address and API key
    url = f'https://addressvalidation.googleapis.com/v1:validateAddress?key={api_key}'
    headers = {'Content-Type': 'application/json'}

    # Construct the request payload
    payload = {
        'address': {
            'addressLines': [address]
        }
    }

    try:
        # Send a POST request to the Address Validation API
        response = requests.post(url, headers=headers, json=payload)
        # Raise an exception for HTTP errors
        response.raise_for_status()
        data = response.json()

        # Check the status of the API response
        if response.status_code == 200:
            # Extract the postal address fields from the API response
            postal_address = data['result']['address']['postalAddress']
            return postal_address
        else:
            # If the status code is anything other than 200, it means the address is invalid
            return None
    except requests.exceptions.HTTPError as e:
        # Handle HTTP errors and continue execution
        print(f'An error occurred: {e}')
        return None


# Load the TSV spreadsheet and create a new output TSV file
input_file = 'sota.tsv'
output_file = 'output-sota.tsv'
with open(input_file, 'r') as f_input, open(output_file, 'w') as f_output:
    reader = csv.DictReader(f_input, delimiter='\t')
    fieldnames = reader.fieldnames
    writer = csv.DictWriter(f_output, fieldnames=fieldnames, delimiter='\t')
    writer.writeheader()

    # Loop through each row in the input TSV file
    for row in reader:
        # Get the country from the input row
        country = row['Country']

        # Only call the validate_address() function and update the row
        # if the country is 'USA'
        if country == 'USA':
            # Get the shipping address from the input row
            shipping_address = row["Participant's Shipping address"]

            # Call the validate_address() function with the shipping address
            # and get the postal address fields
            postal_address = validate_address(shipping_address, api_key)

            # Update the row with the postal address fields
            if postal_address is not None:
                row['Address'] = postal_address.get('addressLines', [''])[0]
                row['APT'] = postal_address.get('administrativeArea', '')
                row['City'] = postal_address.get('locality', '')
                row['State'] = postal_address.get('administrativeArea', '')
                row['Zip'] = postal_address.get('postalCode', '')
                row['Country'] = postal_address.get('countryCode', '')

        # Write the row to the output TSV file, whether it was updated or not
        writer.writerow(row)

print("Output file has been created successfully.")