/** * GTMMAXING for Google Sheets. * * Paste into Extensions > Apps Script, save, reload the sheet. * Then: GTMMAXING menu > Set API key. * * Two ways to use it: * =GTM_EMAIL(A2) work email for the LinkedIn URL in A2 (1 credit if found, misses free) * =GTM_EMAIL(A2, TRUE) also mailbox-verify it (up to 3 credits) * GTMMAXING > Find emails for selected cells * bulk: quotes first, asks you, then writes email + verdict * into the two columns to the right of your selection. * * Repeating a successful lookup is free, so recalculation does not double-charge. */ var GTM_API = "https://api.gtmmaxing.com/v1/agent/tools/"; var GTM_BATCH = 100; // the API accepts up to 100 people per call var GTM_POLL_MS = 4000; function onOpen() { SpreadsheetApp.getUi() .createMenu("GTMMAXING") .addItem("Find emails for selected cells", "gtmFindSelected") .addItem("Show balance", "gtmShowBalance") .addSeparator() .addItem("Set API key", "gtmSetKey") .addToUi(); } function gtmSetKey() { var ui = SpreadsheetApp.getUi(); var res = ui.prompt("GTMMAXING API key", "Create one at gtmmaxing.com > API keys, then paste it here.", ui.ButtonSet.OK_CANCEL); if (res.getSelectedButton() !== ui.Button.OK) return; var key = res.getResponseText().trim(); if (!key) return; // Script properties are readable from custom functions; user properties are not. PropertiesService.getScriptProperties().setProperty("GTM_API_KEY", key); var answer = gtmCall_("gtm_account", {}); ui.alert("Connected. " + answer.summary); } function gtmShowBalance() { SpreadsheetApp.getUi().alert(gtmCall_("gtm_account", {}).summary); } /** * Work email for a LinkedIn profile, via GTMMAXING. * * @param {string} profile LinkedIn profile URL or handle. * @param {boolean} verify Also mailbox-check the email (optional). * @return The email, or a short reason when there is none. * @customfunction */ function GTM_EMAIL(profile, verify) { if (Array.isArray(profile)) { return profile.map(function (row) { return [GTM_EMAIL(row[0], verify)]; }); } profile = String(profile || "").trim(); if (!profile) return ""; var cache = CacheService.getScriptCache(); var cacheKey = "gtm-email-" + (verify ? "v-" : "") + Utilities.base64EncodeWebSafe(profile).slice(0, 200); var cached = cache.get(cacheKey); if (cached !== null) return cached; var found = gtmFindContacts_([profile], Boolean(verify), verify ? 3 : 1, 20000)[0]; var value = found ? found.email || "no email found" : "still running, recalc later"; if (found) cache.put(cacheKey, value, 21600); return value; } function gtmFindSelected() { var ui = SpreadsheetApp.getUi(); var range = SpreadsheetApp.getActiveRange(); if (!range || range.getNumColumns() !== 1) { ui.alert("Select one column of LinkedIn profile URLs first."); return; } var cells = range.getValues().map(function (r) { return String(r[0] || "").trim(); }); var profiles = cells.filter(Boolean); if (!profiles.length) { ui.alert("The selection has no LinkedIn URLs."); return; } var verify = ui.alert("Verify mailboxes too?", "Verification costs up to 2 extra credits per email found.", ui.ButtonSet.YES_NO) === ui.Button.YES; // Free quote for the whole selection before spending anything. var quote = 0; var balance = null; for (var i = 0; i < profiles.length; i += GTM_BATCH) { var q = gtmCall_("gtm_find_contacts", { people: profiles.slice(i, i + GTM_BATCH), verify: verify, dry_run: true, max_credits: 0 }); quote += q.quote || 0; balance = q.balance; } var ok = ui.alert( "Run GTMMAXING?", profiles.length + " profiles. At most " + quote + " credits (misses and repeats are free). Balance: " + balance + ".", ui.ButtonSet.OK_CANCEL ); if (ok !== ui.Button.OK) return; var byProfile = {}; for (var j = 0; j < profiles.length; j += GTM_BATCH) { var batch = profiles.slice(j, j + GTM_BATCH); var budget = verify ? batch.length * 3 : batch.length; var rows = gtmFindContacts_(batch, verify, budget, 300000); batch.forEach(function (p, k) { byProfile[p] = rows[k]; }); } var out = cells.map(function (p) { if (!p) return ["", ""]; var r = byProfile[p]; if (!r) return ["still running, run again", ""]; return [r.email || "", r.verdict || r.status || ""]; }); range.offset(0, 1, out.length, 2).setValues(out); ui.alert(gtmCall_("gtm_account", {}).summary); } /** * Runs gtm_find_contacts and returns one record per input profile, in order, * as {email, verdict, status, ...} objects keyed by column name, or null for * a profile whose job was still running when waitMs ran out. */ function gtmFindContacts_(people, verify, maxCredits, waitMs) { var key = "sheets-" + (verify ? "v-" : "") + Utilities.base64EncodeWebSafe( Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, people.join("\n")) ); var answer = gtmCall_("gtm_find_contacts", { people: people, verify: verify, max_credits: maxCredits, idempotency_key: key }); var deadline = Date.now() + waitMs; while (answer.status === "running" && answer.run_id && Date.now() + GTM_POLL_MS < deadline) { Utilities.sleep(GTM_POLL_MS); answer = gtmCall_("gtm_results", { run_id: answer.run_id }); } if (answer.status === "running") return people.map(function () { return null; }); if (answer.status === "refused" || answer.status === "error") throw new Error("GTMMAXING: " + answer.summary); return gtmMatch_(people, gtmAllRows_(answer)); } /** * Lines records up with the input profiles. Matches on the LinkedIn handle * when the rows carry a profile column, so skipped misses or reordered rows * never shift an email onto the wrong person; falls back to row order. */ function gtmMatch_(people, records) { var col = null; if (records.length) { Object.keys(records[0]).forEach(function (c) { if (!col && /linkedin|profile|url/i.test(c)) col = c; }); } if (!col) { var ordered = records.slice(0, people.length); while (ordered.length < people.length) ordered.push({}); return ordered; } var byHandle = {}; records.forEach(function (r) { byHandle[gtmHandle_(r[col])] = r; }); return people.map(function (p) { return byHandle[gtmHandle_(p)] || {}; }); } function gtmHandle_(value) { return (function spreadsheetProfileKey(value, canonicalize) { const text = String(value || "").trim().replace(/[?#].*$/, "").replace(/\/+$/, ""); const match = text.match(/linkedin\.com\/in\/([^/]+)/i); return canonicalize(decodeURIComponent(match ? match[1] : text.replace(/^@/, ""))); })(value, function canonicalProfileSlug(slug) { return /^AC[ow]AA/.test(slug) ? slug : slug.toLowerCase(); }); } /** * Every row of a run. The answer only previews 5 rows without the profile * URL, so a run's full export CSV (name,title,email,verdict,url,status) is * used whenever there is one; it keeps one row per person, misses included. */ function gtmAllRows_(answer) { if (!answer.run_id || !answer.total) return gtmRecords_(answer); var link = gtmCall_("gtm_results", { run_id: answer.run_id, format: "csv" }).link; if (!link) return gtmRecords_(answer); var res = UrlFetchApp.fetch(link, { muteHttpExceptions: true }); if (res.getResponseCode() !== 200) return gtmRecords_(answer); var table = Utilities.parseCsv(res.getContentText()); var header = table.shift() || []; return table.map(function (row) { var rec = {}; header.forEach(function (c, i) { rec[c] = row[i] === "" ? null : row[i]; }); return rec; }); } function gtmRecords_(answer) { var cols = answer.columns || []; return (answer.rows || []).map(function (row) { var rec = {}; cols.forEach(function (c, i) { rec[c] = row[i]; }); return rec; }); } function gtmCall_(tool, args) { var key = PropertiesService.getScriptProperties().getProperty("GTM_API_KEY"); if (!key) throw new Error("GTMMAXING: set your API key from the GTMMAXING menu first."); for (var attempt = 0; attempt < 4; attempt++) { var res = UrlFetchApp.fetch(GTM_API + tool, { method: "post", contentType: "application/json", headers: { Authorization: "Bearer " + key, Accept: "application/json", "X-GTM-Source": "google-sheets" }, payload: JSON.stringify(args), muteHttpExceptions: true, }); var code = res.getResponseCode(); if (code === 429 || code >= 500) { Utilities.sleep(Math.min(20, Math.pow(2, attempt + 1)) * 1000); continue; } if (code === 401) throw new Error("GTMMAXING: API key not accepted. Set a new one from the GTMMAXING menu."); if (code === 403) throw new Error("GTMMAXING: this key cannot spend credits. Create a key with run access."); if (code !== 200) throw new Error("GTMMAXING answered HTTP " + code); return JSON.parse(res.getContentText()).answer; } throw new Error("GTMMAXING is busy; try again in a minute."); }