🟢 Validation via formulas

Before you start working with our formulas in Google Sheets, the E-mail and Phone Validation by Foxentry add-on must first be connected to your account. This step-by-step guide will walk you through the entire installation process.

Let's clarify the terminology right at the start: You always write a formula into a cell (it starts with the = sign). The formula then utilizes a specific Foxentry function (e.g., EMAIL_VALIDATE) to perform the actual action.

E-mail address validation

1. To start working with formulas, ensure your Google Sheets is connected to your account with a valid API key.
   

   
   

2. To validate e-mails, construct a formula that uses the EMAIL_VALIDATE function (1). When used without specific settings, extended validation is applied by default. To fully utilize the function, type your formula like this (2):
   
   =EMAIL_VALIDATE(e-mail, "extended", true, true)
   
   Besides extended validation, there is also basic validation ("basic"). This won't provide details about the mailbox or check if it actually receives e-mails — it only fixes typos and checks the format.
   
   The first "true" parameter in the formula sets whether to allow temporary mailboxes during extended validation; the second applies to freemails.
   

   
   

3. Start validation by pressing Enter. Then you can drag the formula down across all cells with e-mails you need to verify.
   

   
   

4. After validation, select the cells with the API response, copy them using Ctrl + C, and paste them back as values using Ctrl + Shift + V. This prevents repeated requests on every update or file opening.
   
   You’ll know it's done correctly if you see a static text response in the formula bar instead of the =EMAIL_VALIDATE(...) command.
   

   
   

5. You can continue working with validated data by entering more formulas with secondary functions, which only process the downloaded API response. We recommend starting with the formula =GET_PROPOSAL(API_response), which gives you the ultimate verdict on the data.
   

   
   

   Proposal	Meaning
   valid	The request is valid.
   validWithSuggestion	The request is valid, but a better result might exist.
   invalid	The request is invalid.
   invalidWithPartialCorrection	The request is invalid but may be partially corrected. However, the corrected value is still invalid.*
   invalidWithCorrection	The request is invalid but can be automatically corrected to a valid state.
   invalidWithSuggestion	The request is invalid, but the validator isn’t 100% sure if it should be automatically corrected.
   invalidWithCorrectionWithSuggestion	The request is invalid and can be auto-corrected, but multiple valid options exist.
   unknown	The request couldn't be processed or took too long.

   * For example, the e-mail “[email protected]” is corrected to “[email protected]”, but this mailbox does not exist, so the result remains invalid.

6. To verify the current validity of an e-mail, use a formula with the IS_VALID function. You decide which part of the response to check within the formula parameters:
   
   • original e-mail ("result")
   • corrected version ("resultCorrected")
   • suggested correction ("suggestion")
   
   Example formula: =IS_VALID(API_response, "resultCorrected"). If the formula returns N/A, it may mean the API response data is missing.
   

   
   

7. To extract the corrected e-mail into a clean cell, write the formula =GET_CORRECTED(API_response) and reference the cell containing the raw response.
   

   
   

8. To display only the suggested alternative (if it exists), use the formula =GET_SUGGESTION(API_response).
   

   
   

9. With other functions nested inside formulas, you can easily find out whether an address is:
   
   • =EMAIL_IS_FREEMAIL(...) - Freemail is a free e-mail service provided by public providers such as Gmail or Yahoo.
   • =EMAIL_IS_DISPOSABLE(...) - Disposable e-mail is an address intended for short-term use. It poses a risk due to low-quality contacts and frequent misuse.
   • =EMAIL_IS_CATCH_ALL_SERVER(...) - A server that accepts e-mails sent to any address within a domain, even if the specific mailbox does not exist.
   

   
   

10. For the three functions mentioned above, you can specify as a formula parameter whether you want the status for the original ("result"), corrected ("resultCorrected"), or suggested ("suggestion") address.
    
    We recommend always checking the result of the GET_PROPOSAL formula (2.) first, which will tell you which part of the API response is the most relevant.
    

    
    

Phone number validation

1. To start working with phone validation formulas, ensure your Google Sheets is connected to your account with a valid API key.
   

   
   

2. To validate phone numbers, construct a formula using one of two main functions:
   
   • =PHONE_VALIDATE(...) – if you have the prefix and number in one single cell,
   • =PHONE_VALIDATE_WITH_PREFIX(...) – if you have the prefix and the number separated into two cells.
   
   Extended validation is used by default. To use basic validation, add the "basic" parameter to the formula. Start the validation by pressing Enter.
3. After validation, select the cells with the API response, copy them (Ctrl + C) and immediately paste them back as values (Ctrl + Shift + V). This protects your credits from unwanted consumption. The formula is erased from the cell and replaced by the static response text.
   

   
   

4. To process API response data, we recommend starting with the =GET_PROPOSAL(API_response) formula, which summarizes the final result of the check.
   

   
   

5. To verify the current validity of a phone number, use the IS_VALID function. As a parameter in the formula, determine which part to check:
   
   • validity of the original number ("result")
   • validity of the corrected version in international format ("resultCorrected")
   • suggested correction ("suggestion")
   

   
   

6. If you want to get the specific text of the corrected number or suggested variant, reference the response cell in the GET_CORRECTED or GET_SUGGESTION formulas.
   

   
   

7. Foxentry can format phones according to standards. Construct a formula with the PHONE_GET_FORMAT function, which accepts these four parameters:
   
   • "RAW" – raw numeric format without spaces
   • "National" – local format customary in a given country
   • "E.123" – international standardized format
   • "E.164" – default international format
   

   
   

8. With extended validation, the formula =PHONE_GET_TYPE(API_response) will immediately tell you whether it's a mobile or landline number.
   

   
   

9. Similarly, during extended validation, the formula =PHONE_GET_CARRIER(API_response) will detect the carrier for the phone number.