{"app":{"id":23,"summary":"Email verification tool to validate and generate verified email lists.","versions":[72,73,8854],"created_by":"donald nwokoro31","created_at":"2024-04-30T00:15:11.238Z","votes":0,"approved":false,"apps":["gsheets"],"app_type":"openapp","external_embed_url":null,"value":{"grid":[{"3":{"h":1,"w":1,"x":1,"y":1,"fixed":false},"12":{"h":10,"w":8,"x":2,"y":0,"fixed":false},"id":"d","data":{"id":"d","type":"textcomponent","actions":[],"customCss":{"text":{"class":"","style":""},"container":{"class":"","style":""}},"configuration":{"style":{"type":"static","value":"Body"},"tooltip":{"type":"static","value":""},"copyButton":{"type":"static","value":false}},"componentInput":{"eval":"Email Verification Instructions\r\n\r\n1. Create a new Google Spreadsheet.\r\n2. In the first column (Column A), enter the email addresses you want to verify. Each email address should be in a separate row.\r\n   Example: https://docs.google.com/spreadsheets/d/1TXtZo4k0Jhygqbbsr75dTjTpRMP3qH5ewMpt1pqqbNI\r\n3. Make the spreadsheet publicly accessible:\r\n   - Click on the \"Share\" button in the top right corner of the spreadsheet.\r\n   - In the \"Share with others\" dialog, click on \"Change to anyone with the link\".\r\n   - Select \"Viewer\" as the access level.\r\n   - Click \"Done\" to save the sharing settings.\r\n4. Copy the URL of the publicly shared spreadsheet.\r\n5. Paste the spreadsheet URL into the input field below and click \"Verify Emails\".","type":"templatev2","fieldType":"template","connections":[]},"verticalAlignment":"top","horizontalAlignment":"left"}},{"3":{"h":1,"w":2,"x":0,"y":0,"fixed":false},"12":{"h":1,"w":4,"x":4,"y":10,"fixed":false},"id":"a","data":{"id":"a","type":"textinputcomponent","actions":[],"customCss":{"input":{"class":"","style":""}},"configuration":{"disabled":{"type":"static","value":false},"afterIcon":{"type":"static","value":""},"beforeIcon":{"type":"static","value":""},"placeholder":{"type":"static","value":"Type..."},"defaultValue":{"type":"static"}},"verticalAlignment":"center"}},{"3":{"h":1,"w":1,"x":2,"y":0,"fixed":false},"12":{"h":1,"w":2,"x":5,"y":11,"fixed":false},"id":"b","data":{"id":"b","type":"buttoncomponent","actions":[],"customCss":{"button":{"class":"","style":""},"container":{"class":"","style":""}},"recomputeIds":[],"configuration":{"size":{"type":"static","value":"xs"},"color":{"type":"static","value":"blue"},"label":{"type":"static","value":"Press me"},"onError":{"type":"oneOf","selected":"errorOverlay","configuration":{"open":{"id":{"type":"static","value":""}},"close":{"id":{"type":"static","value":""}},"setTab":{"setTab":{"type":"static","value":[]}},"gotoUrl":{"url":{"type":"static","value":""},"newTab":{"type":"static","value":true}},"errorOverlay":{},"sendErrorToast":{"message":{"type":"static","value":""},"appendError":{"type":"static","value":true}}}},"disabled":{"type":"static","value":false},"afterIcon":{"type":"static","value":""},"onSuccess":{"type":"oneOf","selected":"none","configuration":{"none":{},"open":{"id":{"type":"static","value":""}},"close":{"id":{"type":"static","value":""}},"setTab":{"setTab":{"type":"static","value":[]}},"gotoUrl":{"url":{"type":"static","value":""},"newTab":{"type":"static","value":true}},"openModal":{"modalId":{"type":"static","value":""}},"sendToast":{"message":{"type":"static","value":""}},"clearFiles":{"id":{"type":"static","value":""}},"closeModal":{"modalId":{"type":"static","value":""}}}},"beforeIcon":{"type":"static","value":""},"fillContainer":{"type":"static","value":false},"triggerOnAppLoad":{"type":"static","value":false},"confirmationModal":{"type":"oneOf","selected":"none","configuration":{"none":{},"confirmationModal":{"title":{"type":"static","value":"Title"},"description":{"type":"static","value":"Are you sure?"},"confirmationText":{"type":"static","value":"Confirm"}}}}},"componentInput":{"type":"runnable","value":{"verified_emails":["infect3dlab@gmail.com"],"google_sheet_url":"https://docs.google.com/spreadsheets/d/1mWRSVGwmoU862j0v9xqio2gRQc3blrvtdnLdBJSoNeY"},"fields":{"x":{"expr":"a.result","type":"evalv2","value":null,"fieldType":"string","connections":[{"id":"result","componentId":"a"}]}},"runnable":{"name":"Inline Script 0","type":"runnableByName","inlineScript":{"lock":"anyio==4.3.0\ncertifi==2024.2.2\ncharset-normalizer==3.3.2\nh11==0.14.0\nhttpcore==0.17.3\nhttpx==0.24.1\nidna==3.7\nrequests==2.31.0\nsniffio==1.3.1\nurllib3==2.2.1\nwmill==1.320.3","path":"u/donald/a_poc_to_validate_bulk_emails/Inline_Script_0","schema":{"type":"object","$schema":"https://json-schema.org/draft/2020-12/schema","required":["x"],"properties":{"x":{"type":"string","default":null,"description":""}}},"content":"import wmill\nimport requests\nimport re\n\ndef extract_emails_from_spreadsheet(spreadsheet_url, token):\n    spreadsheet_id = spreadsheet_url.split(\"/d/\")[1].split(\"/\")[0]\n    range_name = 'Sheet1!A:A'\n    GET_VALUES_URL = f\"https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet_id}/values/{range_name}\"\n    \n    response = requests.get(\n        GET_VALUES_URL,\n        headers={\n            \"Authorization\": f\"Bearer {token}\",\n            \"Content-Type\": \"application/json\"\n        }\n    )\n    \n    if response.status_code != 200:\n        raise Exception(f\"Failed to retrieve data: {response.text}\")\n    \n    values = response.json().get('values', [])\n    \n    emails = [item for sublist in values for item in sublist]\n    \n    email_regex = r\"[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+\"\n    \n    email_list = [email for email in emails if re.match(email_regex, email)]\n    \n    return email_list\n\n\ndef verify_emails(email_list, api_key):\n    verified_emails = []\n    for email in email_list:\n        url = f\"https://api.millionverifier.com/api/v3/?api={api_key}&email={email}\"\n        response = requests.get(url)\n        if response.status_code == 200:\n            data = response.json()\n            print(data)\n            if data['result'] == 'ok':\n                verified_emails.append(email)\n    return verified_emails\n\n\ndef create_google_sheet(verified_emails, token):\n    CREATE_SHEET_URL = \"https://sheets.googleapis.com/v4/spreadsheets\"\n    \n    payload = {\n        \"properties\": {\n            \"title\": \"Verified Emails\"\n        }\n    }\n    \n    response = requests.post(\n        CREATE_SHEET_URL,\n        headers={\n            \"Authorization\": f\"Bearer {token}\",\n            \"Content-Type\": \"application/json\"\n        },\n        json=payload\n    )\n    \n    if response.status_code != 200:\n        raise Exception(f\"Failed to create Google Sheet: {response.text}\")\n    \n    spreadsheet_id = response.json()['spreadsheetId']\n    \n    APPEND_VALUES_URL = f\"https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet_id}/values/A1:append?valueInputOption=USER_ENTERED\"\n    \n    payload = {\n        \"values\": [[email] for email in verified_emails]\n    }\n    \n    response = requests.post(\n        APPEND_VALUES_URL,\n        headers={\n            \"Authorization\": f\"Bearer {token}\",\n            \"Content-Type\": \"application/json\"\n        },\n        json=payload\n    )\n    \n    if response.status_code != 200:\n        raise Exception(f\"Failed to append values to Google Sheet: {response.text}\")\n    \n    return f\"https://docs.google.com/spreadsheets/d/{spreadsheet_id}\"\n\ndef main(x: str):\n    service_account_info = wmill.get_resource(\"u/donald/energy_efficient_gsheets\")\n\n    if service_account_info is None:\n        raise ValueError(\"Failed to retrieve service account information. Check the resource path and permissions.\")\n\n    token = service_account_info['token']\n\n    email_list = extract_emails_from_spreadsheet(x, token)\n    api_key = wmill.get_variable(\"u/donald/MillionVerifierAPIKey\")\n    print(api_key)\n\n    verified_emails = verify_emails(email_list, api_key)\n    \n    google_sheet_url = create_google_sheet(verified_emails, token)\n    \n    return {'verified_emails': verified_emails, 'google_sheet_url': google_sheet_url}","language":"python3"}},"fieldType":"any","autoRefresh":false,"recomputeOnInputChanged":false},"verticalAlignment":"center","horizontalAlignment":"center"}},{"3":{"h":1,"w":1,"x":0,"y":1,"fixed":false},"12":{"h":9,"w":9,"x":1,"y":12,"fixed":false},"id":"c","data":{"id":"c","type":"textcomponent","actions":[],"customCss":{"text":{"class":"","style":""},"container":{"class":"","style":""}},"configuration":{"style":{"type":"static","value":"Body"},"tooltip":{"type":"static","value":""},"copyButton":{"type":"static","value":false}},"componentInput":{"eval":"Hello ${ctx.username}","expr":"return `\r\nEmail Verification Results\r\n\r\nVerified Emails:\r\n${b.result[\"verified_emails\"].map(email => `- ${email}`).join('\\n')}\r\n\r\nTotal Verified Emails: ${b.result[\"verified_emails\"].length}\r\n\r\nGoogle Sheet URL: ${b.result[\"google_sheet_url\"]}\r\n`;","type":"evalv2","fieldType":"template","connections":[{"id":"result","componentId":"b"}]},"verticalAlignment":"top","horizontalAlignment":"left"}}],"theme":{"path":"f/app_themes/theme_0","type":"path"},"subgrids":{},"fullscreen":true,"norefreshbar":false,"hiddenInlineScripts":[],"unusedInlineScripts":[]},"description":"This app allows users to upload a Google Spreadsheet with email addresses, verifies them using the MillionVerifier API, and creates a new spreadsheet containing the verified emails.","vcreated_at":"2024-05-10T20:00:45.226Z","vcreated_by":"donald nwokoro31","comments":[{"id":49,"content":"you hou @bluewind","created_by":"wayne776","created_at":"2024-04-30T00:23:23.151Z","votes":0}]}}