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
=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
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.
Save API Key in PropertiesService
Reload your sheet, click PlacesData API → Set API Key (PropertiesService), and paste your pd_live_... key.
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.