How to Scrape Websites into Google Sheets & Airtable Automatically

const API_KEY  = 'bs_YOUR_API_KEY';
const BASE_URL = 'https://boltscraper.com/api/v1/website';

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🔍 Bolt Scraper')
    .addItem('Enrich Selected URLs', 'enrichUrls')
    .addToUi();
}

function enrichUrls() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const urls = sheet.getRange(2, 1, lastRow - 1).getValues()
    .flat().filter(u => u);

  if (!urls.length) { return Browser.msgBox('No URLs found in column A'); }

  // 1. Submit job
  const jobRes = UrlFetchApp.fetch(`${BASE_URL}/jobs`, {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: `Bearer ${API_KEY}` },
    payload: JSON.stringify({ job_name: 'Sheets Enrichment', urls })
  });
  const { job_id } = JSON.parse(jobRes.getContentText());

  // 2. Poll until complete (max 10 min)
  let status = 'scraping';
  let tries = 0;
  while (status !== 'COMPLETED' && tries < 40) {
    Utilities.sleep(15000);
    const poll = JSON.parse(UrlFetchApp.fetch(`${BASE_URL}/jobs/${job_id}`, {
      headers: { Authorization: `Bearer ${API_KEY}` }
    }).getContentText());
    status = poll.job.status;
    tries++;
  }

  // 3. Fetch results and write back to sheet
  const results = JSON.parse(UrlFetchApp.fetch(
    `${BASE_URL}/jobs/${job_id}/results?pageSize=10000`,
    { headers: { Authorization: `Bearer ${API_KEY}` } }
  ).getContentText()).results;

  const resultMap = {};
  results.forEach(r => { resultMap[r.url.replace(/\/$/, '')] = r; });

  for (let i = 2; i <= lastRow; i++) {
    const url = sheet.getRange(i, 1).getValue().replace(/\/$/, '');
    const r = resultMap[url] || resultMap['https://' + url];
    if (r) {
      sheet.getRange(i, 2, 1, 6).setValues([[
        r.email_1, r.phone_1, r.linkedin_url,
        r.instagram_url, r.facebook_url, r.page_title
      ]]);
    }
  }
  Browser.msgBox(`✅ Enrichment complete — ${results.length} URLs processed`);
}

// Node 1: Webhook Trigger
{
  "type": "n8n-nodes-base.webhook",
  "parameters": {
    "httpMethod": "POST",
    "path": "bolt-website"
  }
}

// Node 2: Filter — only process completed jobs
{
  "type": "n8n-nodes-base.if",
  "parameters": {
    "conditions": {
      "string": [{ "value1": "={{ $json.event }}", "value2": "website_job.completed" }]
    }
  }
}

// Node 3: HTTP Request — fetch full results CSV
{
  "type": "n8n-nodes-base.httpRequest",
  "parameters": {
    "method": "GET",
    "url": "={{ $json.download_csv_url }}",
    "responseFormat": "text"
  }
}

// Node 4: Airtable — Create/Upsert Records
{
  "type": "n8n-nodes-base.airtable",
  "parameters": {
    "operation": "upsert",
    "table": "Leads",
    "matchField": "Website",
    "fields": {
      "Website":   "={{ $item.url }}",
      "Email":     "={{ $item.email_1 }}",
      "Phone":     "={{ $item.phone_1 }}",
      "LinkedIn":  "={{ $item.linkedin_url }}",
      "Instagram": "={{ $item.instagram_url }}",
      "Facebook":  "={{ $item.facebook_url }}",
      "Company":   "={{ $item.page_title }}"
    }
  }
}