Skip to content
Integrations/Google Sheets Batch Apps Script

Enrich Google Maps Business Leads & Emails in Google Sheets

Paste one zero-dependency script into Extensions → Apps Script to add a PlacesData API → Enrich Selected Businesses (Skip Filled) menu—complete with 6-hour CacheService deduplication, PropertiesService key storage, and 0 Credits charged on empty lookups.

Menu-Driven Batch Enricher · 6h CacheService · PropertiesService Key Vault

Google Apps Script (Code.gs) — Zero Auto-Refresh Credit Drain

Why We Never Use Custom Cell Formulas (=PLACES_SEARCH()): Google Sheets automatically recalculates custom formulas whenever a tab reloads or rows sort—silently re-billing your API credits. This script runs exclusively when you click PlacesData API → Enrich Selected Businesses (Skip Filled), skips any row where Place ID is already populated, and caches responses in CacheService for 6 hours.
/**
 * PlacesData API (https://placesdataapi.com) — Google Sheets Batch Lead & Email Enricher
 *
 * SAFETY BY DESIGN (Zero Auto-Refresh Formula Drain):
 *   1. Runs ONLY when explicitly triggered from the custom top menu:
 *      "PlacesData API -> Enrich Selected Businesses (Skip Filled)"
 *   2. Stores your pd_live_... key securely in PropertiesService (Script Properties).
 *   3. Caches responses for 6 hours (21,600s) in CacheService to prevent duplicate spend.
 *   4. Automatically skips rows where Column B (Place ID) is already filled.
 *
 * Expected Sheet Columns (Row 1 Headers):
 *   Col A: Business Search Query (e.g. "Blue Bottle Coffee San Francisco" or "Roofers in Dallas TX")
 *   Col B: Place ID (auto-filled; skipped when non-empty)
 *   Col C: Verified Name
 *   Col D: Phone (E.164)
 *   Col E: Website URL
 *   Col F: Rating
 *   Col G: Review Count
 *   Col H: Primary Email (/v1/leads/enrich — 0 Credits when empty)
 *   Col I: LinkedIn / Instagram URL
 */

const PLACESDATA_BASE_URL = "https://api.placesdataapi.com";
const CACHE_TTL_SECONDS = 21600; // 6 hours CacheService TTL

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("PlacesData API")
    .addItem("Enrich Selected Businesses (Skip Filled)", "enrichSelectedBusinessesSkipFilled")
    .addSeparator()
    .addItem("Set API Key (PropertiesService)", "configurePlacesDataApiKey")
    .addToUi();
}

function configurePlacesDataApiKey() {
  const ui = SpreadsheetApp.getUi();
  const prompt = ui.prompt(
    "Set PlacesData API Key",
    "Paste your pd_live_... API key from https://placesdataapi.com/dashboard (3,000 Free Credits / $0.30 on sign-up — No Card Required):",
    ui.ButtonSet.OK_CANCEL
  );
  if (prompt.getSelectedButton() !== ui.Button.OK) return;
  const key = prompt.getResponseText().trim();
  if (!key) {
    ui.alert("API key was empty. No changes saved.");
    return;
  }
  PropertiesService.getScriptProperties().setProperty("PLACESDATA_API_KEY", key);
  ui.alert("Saved PLACESDATA_API_KEY in Script Properties.");
}

function enrichSelectedBusinessesSkipFilled() {
  const ui = SpreadsheetApp.getUi();
  const apiKey = PropertiesService.getScriptProperties().getProperty("PLACESDATA_API_KEY");
  if (!apiKey) {
    ui.alert("Missing API key. Click 'PlacesData API -> Set API Key (PropertiesService)' first.");
    return;
  }

  const sheet = SpreadsheetApp.getActiveSheet();
  const selection = sheet.getActiveRange();
  if (!selection) {
    ui.alert("Select one or more rows in Column A first.");
    return;
  }

  const startRow = Math.max(2, selection.getRow()); // Never overwrite Row 1 header
  const numRows = selection.getNumRows();
  const rowRange = sheet.getRange(startRow, 1, numRows, 9);
  const values = rowRange.getValues();

  let enrichedCount = 0;
  let skippedCount = 0;

  for (let i = 0; i < values.length; i++) {
    const query = String(values[i][0] || "").trim();
    const existingPlaceId = String(values[i][1] || "").trim();
    if (!query || existingPlaceId) {
      skippedCount++;
      continue;
    }

    // Step 1: Search Google Maps Business (5 Credits/place, 0 Credits if empty)
    const searchUrl =
      PLACESDATA_BASE_URL + "/v1/places/search?q=" + encodeURIComponent(query) + "&limit=1";
    const searchJson = fetchWithSixHourCache_(searchUrl, apiKey);
    const place = searchJson && searchJson.data && searchJson.data[0] ? searchJson.data[0] : null;
    if (!place) {
      values[i][1] = "NOT_FOUND";
      continue;
    }

    const domain = String(place.domain || place.website || "").trim();
    let email = "";
    let socialUrl = "";

    // Step 2: Crawl Business Website for B2B Email & Socials (20 Credits if found, 0 Credits if empty)
    if (domain) {
      const enrichUrl =
        PLACESDATA_BASE_URL +
        "/v1/leads/enrich?domain=" +
        encodeURIComponent(domain) +
        "&place_id=" +
        encodeURIComponent(place.place_id || "");
      const enrichJson = fetchWithSixHourCache_(enrichUrl, apiKey);
      const enrichData = enrichJson && enrichJson.data ? enrichJson.data : null;
      if (enrichData) {
        if (Array.isArray(enrichData.emails) && enrichData.emails.length > 0) {
          email = enrichData.emails[0];
        }
        const socials = enrichData.social_links || {};
        socialUrl = socials.linkedin || socials.instagram || socials.facebook || "";
      }
    }

    values[i][1] = place.place_id || "";
    values[i][2] = place.name || "";
    values[i][3] = place.phone_e164 || place.phone || "";
    values[i][4] = place.website || "";
    values[i][5] = place.rating !== undefined && place.rating !== null ? place.rating : "";
    values[i][6] =
      place.review_count !== undefined && place.review_count !== null
        ? place.review_count
        : place.reviews_count || "";
    values[i][7] = email;
    values[i][8] = socialUrl;
    enrichedCount++;
  }

  rowRange.setValues(values);
  SpreadsheetApp.getActiveSpreadsheet().toast(
    "Enriched " + enrichedCount + " row(s), skipped " + skippedCount + " filled/blank row(s).",
    "PlacesData API",
    6
  );
}

function fetchWithSixHourCache_(url, apiKey) {
  const cache = CacheService.getScriptCache();
  const digestBytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, url);
  const cacheKey =
    "pd6h_" +
    digestBytes
      .slice(0, 16)
      .map(function (b) {
        return ("0" + (b & 0xff).toString(16)).slice(-2);
      })
      .join("");

  const cached = cache.get(cacheKey);
  if (cached) {
    try {
      return JSON.parse(cached);
    } catch (e) {
      // fall through on cache parse error
    }
  }

  for (let attempt = 0; attempt < 2; attempt++) {
    const resp = UrlFetchApp.fetch(url, {
      method: "get",
      muteHttpExceptions: true,
      headers: {
        Authorization: "Bearer " + apiKey,
        Accept: "application/json",
      },
    });
    const status = resp.getResponseCode();
    if (status === 429 && attempt === 0) {
      const hdrs = resp.getAllHeaders();
      const waitSec = Number(hdrs["Retry-After"] || hdrs["retry-after"] || 2) || 2;
      Utilities.sleep(waitSec * 1000);
      continue;
    }
    if (status !== 200) return null;
    const text = resp.getContentText();
    if (text && text.length < 95000) {
      cache.put(cacheKey, text, CACHE_TTL_SECONDS);
    }
    return JSON.parse(text);
  }
  return null;
}

60-Second Google Sheets Installation

Step 01

Paste Row 1 Headers & Script

Click Copy Row 1 Headers (A1:I1) into Row 1, then open Extensions → Apps Script, paste Code.gs, and save.

Step 02

Save API Key in PropertiesService

Reload your sheet, click PlacesData API → Set API Key (PropertiesService), and paste your pd_live_... key.

Step 03

Highlight Column A & Enrich

Select your target business queries in Column A and click PlacesData API → Enrich Selected Businesses (Skip Filled).

Google Sheets Enrichment FAQ

Why shouldn't I use =IMPORTDATA() or custom cell formulas for Google Maps enrichment?

Google Sheets recalculates custom cell formulas periodically on tab open, filter changes, and row insertions—which re-executes external HTTP calls and wastes paid API credits. Our menu-driven Apps Script ('PlacesData API -> Enrich Selected Businesses (Skip Filled)') writes static cell values once, skips rows that already have a Place ID, and caches lookups in CacheService for 6 hours.

Where is my PlacesData API key stored inside Google Sheets?

Your pd_live_... API key is stored inside Google's server-side PropertiesService.getScriptProperties() via the menu item 'PlacesData API -> Set API Key (PropertiesService)'—never exposed inside spreadsheet cells or shared formulas.

How much does enriching 1,000 rows in Google Sheets cost?

Place Search (/v1/places/search?limit=1) costs 5 Credits per matched business ($0.28–$0.50 per 1,000 rows on Pro–PAYG, or $0.20 per 1,000 rows on Business; 0 Credits if not found). Optional website email enrichment (/v1/leads/enrich) costs 20 Credits ONLY when emails or social links are found, and 0 Credits when empty.

What happens if I select 500 rows at once?

Apps Script executes sequentially within Google's 6-minute execution limit (~150–250 live rows or 1,000+ warm-cached rows per click). Because the script automatically skips any row where Column B (Place ID) is already filled, you can safely highlight all rows and click 'Enrich Selected Businesses (Skip Filled)' again to resume from the exact row where the previous batch stopped.