How to Integrate the Email Verifier feature into Google Sheets

1. 1. If you don't have an existing Google Sheet, create a new one.

   

2. 2. Click on Extensions.

   

3. 3. Select Apps Script. It will open an App Script tab.

   

4. 4. Now paste the code available at the end of the page and save it (Ctrl+S or Cmd+S).

   

5. 5. Go to the Icypeas website and Sign In of Sign up.

6. 6. Select the API tab

   

7. 7. There, you will find your API key, your API secret key, and your User id. Save this information.

   

8. 8. Go back to the App Script tab.

9. 9. At the beginning of the code, insert your API key, API secret key, and User ID between the single quotes (''). Make sure you place each value correctly, without any extra spaces or characters as shown below.

   

10. 10. Then click on Deploy.

    

11. 11. Select New deployement.

    

12. 12. Click on the gear icon.

    

13. 13. Select Library.

    

14. 14. Click on Deploy.

    

15. 15. Click on Done.

    

16. 16. Go back to your Google Sheet.

17. 17. Select Extensions -> Macros -> Import macro.

    

18. 18. Click on Add Function.

    

19. 19. Click on Close.

    

20. 20. Now, you can use the verifyEmail() macro to verify email addresses.

    

21. 21. Then, you can extend the verifyEmail() formula, and the result will be displayed.

    

22. 22. Code to paste at step 4

    // Replace "API-KEY" with your API Key
    const API_KEY = '';
    // Replace "API-SECRET" with your API Secret
    const API_SECRET = '';
    // Replace "USER-ID" with your user ID
    const USER_ID = '';
    const API_BASE_URL = 'https://app.icypeas.com/api/';
    
    
    const utils = {
      sha1:function (str){
        const buffer = Utilities.newBlob(str).getBytes();
        const hash = Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_1, buffer));
        return hash;
      },
    
      generateSignature: function(endpoint, method, secret, timestamp) {
        if (!endpoint) {
          throw new Error('Endpoint cannot be empty');
        }
    
        const payload = `${method}${endpoint}${timestamp}`.toLowerCase();
        const signature = utils.sha1(payload);
    
        return { signature, timestamp };
      },
    
      makeApiRequest:function(endpoint, method, payload = {}) {
        const timestamp = new Date().toISOString();
    
        const { signature, timestamp: authTimestamp } = utils.generateSignature(endpoint, method, API_SECRET, timestamp);
    
        const params = {
          method,
          headers: {
            'Content-Type': 'application/json',
            'Authorization': `${API_KEY}:${signature}`,
            'X-ROCK-TIMESTAMP': authTimestamp,
          },
          payload: JSON.stringify(payload),
        };
    
        try {
          const response = UrlFetchApp.fetch(endpoint, params);
          const jsonResponse = response.getContentText();
    
          return JSON.parse(jsonResponse);
        } catch (error) {
          console.log('API Request Failed. Error:', error);
          console.log('Full Server Response:', error.getContentText());
          throw error;
        }
      },
    }
    
    function verifyEmail(email) {
      try {
        const endpoint = `${API_BASE_URL}sync/email-verification`;
        const method = 'POST';
        const payload = { email: email };
    
        const apiResponse = utils.makeApiRequest(endpoint, method, payload);
    
        if (apiResponse.status == "NOT_FOUND"){
          return "not_found";
        } else {
          return apiResponse.emails[0].certainty;
        }
      } catch (error) {
        return [['Resource not found', '']];
      }
    }