We're excited to announce a pricing update coming November 1st! Read more

How to use Icypeas on Google Sheets

This is a tutorial on how to plug Icypeas into your google sheets.

Summary : 

  • I. Install the macros on your google sheets
  • II. Example with searchEmail macro
  • III. Code

I) Install the macros on your google sheets



1) Sign in or Sign up into your Icypeas account.

2) Navigate to the API tab on the navbar.

3) Activate your API access and retrieve your API_Key, API_Secret, User_id.

4) Open your google sheets and click on the Extensions > Apps Script.

5) Now paste the code available at the end of the page in the Code.gs file and save it (Ctrl+S or Cmd+S).

6) Modify the API_KEY, API_SECRET, USER_ID fields to match the values retrieved in step 3.
Paste each value between the single quotes.

7) Then make a new deployment (Deploy > New deployment)

8) Select the add-on type to be “Library” then press “Deploy”

9) Go back to your Google Sheet and import macros (Extensions > Macros > Import macro)

10) Import the following functions “searchDomain”, “searchEmail” and “verifyEmail

11) You can now use the macros as you like.

II ) Exemple with searchEmail

Fill up the cells of the sheet with your data and the desired macro :

Then appreciate the result :

III ) Code

// Replace "API-KEY" with your API Key
const API_KEY = 'YOUR_API_KEY';
// Replace "API-SECRET" with your API Secret
const API_SECRET = 'YOUR_SECRET_KEY';
// Replace "USER-ID" with your user ID
const USER_ID = 'YOUR_USER_ID';
const API_BASE_URL = 'https://app.icypeas.com/api/';

function sha1(str) {
  const buffer = Utilities.newBlob(str).getBytes();
  const hash = Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_1, buffer));
  return hash;
}

function generateSignature(endpoint, method, secret, timestamp) {
  if (!endpoint) {
    throw new Error('Endpoint cannot be empty');
  }

  const payload = `${method}${endpoint}${timestamp}`.toLowerCase();
  const signature = sha1(payload);

  return { signature, timestamp };
}

function makeApiRequest(endpoint, method, payload = {}) {
  const timestamp = new Date().toISOString();

  const { signature, timestamp: authTimestamp } = 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 searchEmail(firstName, lastName, companyName) {
  try {
    const endpoint = `${API_BASE_URL}email-search`;
    const method = 'POST';
    const payload = { firstname: firstName, lastname: lastName, domainOrCompany: companyName };

    const apiResponse = makeApiRequest(endpoint, method, payload);

    const searchId = apiResponse.item._id;

    Utilities.sleep(10000);

    const results = getSearchResults(searchId);

    for (const emailResult of results.items[0].results.emails) {
      const certainty = emailResult.certainty;

      if (certainty === 'probable' || certainty === 'sure' || certainty === 'ultra_sure') {
        return [[emailResult.email, certainty]];
      }
    }

    return [['Unfound', '']];
  } catch (error) {
    return [['Resource not found', '']];
  }
}

function verifyEmail(email) {
  try {
    const endpoint = `${API_BASE_URL}email-verification/`;
    const method = 'POST';
    const payload = { email: email };

    const apiResponse = makeApiRequest(endpoint, method, payload);

    const searchId = apiResponse.item._id;

    Utilities.sleep(10000);

    const results = getSearchResults(searchId);

    return results.items[0].results.emails[0].certainty;
  } catch (error) {
    return [['Resource not found', '']];
  }
}

function searchDomain(domainOrCompany) {
  try {
    const endpoint = `${API_BASE_URL}domain-search/`;
    const method = 'POST';
    const payload = { domainOrCompany: domainOrCompany };

    const apiResponse = makeApiRequest(endpoint, method, payload);

    const searchId = apiResponse.item._id;

    Utilities.sleep(20000);

    const results = getSearchResults(searchId);

    const emailResults = [];

    for (const emailResult of results.items[0].results.emails) {
      const certainty = emailResult.certainty;

      if (certainty === 'probable' || certainty === 'sure' || certainty === 'ultra_sure') {
        emailResults.push([emailResult.email, certainty]);
      }
    }

    return emailResults;
  } catch (error) {
    return [['Resource not found', '']];
  }
}

function getSearchResults(searchId) {
  const endpoint = `${API_BASE_URL}bulk-single-searchs/read`;
  const method = 'POST';
  const payload = {
    user: USER_ID, 
    mode: "single",
    id: searchId
  };

  const response = makeApiRequest(endpoint, method, payload);

  if (!response.success) {
    throw new Error('Search results not found');
  }

  return response;
}