Apps Script прокси: запрос через посредника и сбор данных в Google Таблицы

Раздел 4.4 · Серверная сторона

У меня в работе лежит таблица на 40 листов, куда каждое утро приезжают позиции по 3000 ключей и заголовки страниц десятка сайтов из выдачи. Первую версию я собрал целиком на Apps Script за вечер, и неделю она отработала ровно. Потом половина запросов начала возвращать пустые тела и страницы проверки: обращения шли с одного диапазона адресов Google, целевые сайты это заметили и прикрутили лимит. Переделка заняла ещё два вечера. Ниже разобраны оба порядка, к которым я пришёл.

1. Что понадобится

Аккаунт Google и доступ к редактору кода: в таблице меню «Расширения», далее «Apps Script». Строка подключения к посреднику в одном из двух форматов, IP:PORT для привязанного адреса либо IP:PORT:LOGIN:PASS для доступа по логину. Кабинет отдаёт список ссылкой или файлом, обе формы строки лежат рядом, переключиться между ними получается за минуту.

Для первого пути нужен свой сервер с внешним адресом, доменным именем и сертификатом, на нём Node.js версии 20 и выше либо PHP 8 с расширением cURL. Для второго пути хватит рабочей машины с Python 3.10 и сервисного аккаунта Google, ключ которого скачивается файлом JSON. Заранее решите, каким способом оформлена авторизация у посредника: привязка внешнего адреса сервера и доступ по логину настраиваются в разных местах, и отказы 403 почти всегда растут отсюда.

Включение пакета занимает около 5 минут, раньше этого срока настраивать обработчик смысла нет. Бесплатный тест до 2 часов покрывает весь цикл: развернуть сервер, прогнать десяток целей, сверить адрес на выходе и убедиться, что ячейки заполняются.

2. Почему у Apps Script нет собственной настройки посредника

Код Apps Script выполняется на серверах Google, и внешний адрес исходящего запроса принадлежит Google. Метод UrlFetchApp.fetch принимает URL и объект параметров: method, headers, payload, contentType, muteHttpExceptions, followRedirects, validateHttpsCertificates, escaping, useIntranet. Поля для адреса посредника среди них нет. Подсунуть его заголовком тоже не выйдет: заголовок описывает содержание запроса, маршрут выбирает клиент, и клиентом здесь работает инфраструктура Google.

Переменные окружения вроде HTTP_PROXY среда не читает. Доступа к сокету, к системному сетевому стеку и к установке пакетов через npm тут нет: доступны встроенные сервисы и подключаемые библиотеки на том же Apps Script. Инструкции с параметром proxy внутри UrlFetchApp встречаются в сети до сих пор, верить им не стоит: такого ключа в API нет, и код с ним падает на разборе объекта параметров. Проверяется это за полминуты в справочнике методов класса.

Отсюда два рабочих порядка. Первый оставляет запуск в таблице и выносит наружу только сетевую часть: свой обработчик на своём сервере принимает адрес цели, ходит через посредника и возвращает тело. Второй убирает сбор с серверов Google целиком: скрипт крутится на вашей машине, а таблица получает готовые строки через API Google.

3. Путь первый: промежуточный обработчик на своём сервере

Обработчик это маленький HTTP-сервис, у которого одна ручка. Он принимает POST с адресом цели, проверяет токен, сверяет хост со списком разрешённых, идёт наружу через посредника и отдаёт назад код ответа с телом. Таблица про посредника ничего не знает и разговаривает только с обработчиком.

Вариант на Node.js с библиотекой undici, у неё есть готовый ProxyAgent:

// relay.js   npm i express undici   запуск: node relay.js
const express = require('express');
const { request, ProxyAgent } = require('undici');

const app = express();
app.use(express.json({ limit: '64kb' }));

const TOKEN = process.env.RELAY_TOKEN;
const ALLOW = (process.env.ALLOW_HOSTS || '').split(',').filter(Boolean);
const MAX_BODY = 4 * 1024 * 1024;
const dispatcher = new ProxyAgent(process.env.PROXY_URL);

app.post('/fetch', async (req, res) => {
  if (req.get('X-Relay-Token') !== TOKEN) {
    return res.status(401).json({ error: 'token' });
  }
  let target;
  try { target = new URL(String(req.body.url)); }
  catch (e) { return res.status(400).json({ error: 'url' }); }
  if (target.protocol !== 'https:' || !ALLOW.includes(target.hostname)) {
    return res.status(403).json({ error: 'host' });
  }

  const ctl = new AbortController();
  const timer = setTimeout(() => ctl.abort(), 25000);
  try {
    const r = await request(target.href, {
      dispatcher,
      method: 'GET',
      headers: {
        'user-agent': req.body.ua || 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)',
        'accept-language': 'ru-RU,ru;q=0.9'
      },
      maxRedirections: 3,
      signal: ctl.signal
    });
    const buf = Buffer.from(await r.body.arrayBuffer());
    res.json({
      status: r.statusCode,
      len: buf.length,
      body: buf.subarray(0, MAX_BODY).toString('utf8')
    });
  } catch (e) {
    res.status(502).json({ error: String(e.message || e) });
  } finally {
    clearTimeout(timer);
  }
});

app.listen(8443, '127.0.0.1');

Разберу по узлам. Строка PROXY_URL собирается из выданной строки подключения: IP:PORT превращается в http://203.0.113.24:8000, форма с логином в http://u4821:s7Kd2Rm9@203.0.113.24:8000. Символы @ и : внутри пароля кодируются процентами, иначе разбор URL сломается на первом же из них.

Проверка токена стоит первой строкой обработчика намеренно. Без неё вы поднимаете открытый посредник, который найдут сканеры за сутки и начнут гонять через него чужой трафик. Список ALLOW_HOSTS закрывает вторую половину той же дыры: обработчик ходит только на те домены, которые вы перечислили, и попытка подставить внутренний адрес вашей же сети упирается в отказ 403.

Ограничение MAX_BODY бережёт таблицу. Ответ UrlFetchApp свыше 50 МБ среда отбрасывает целиком, и одна тяжёлая страница обнуляет весь прогон. Я режу тело на четырёх мегабайтах, для разбора заголовков и блоков выдачи этого хватает с большим запасом. Таймаут в 25 секунд подобран под общее время запуска скрипта, к нему вернусь в разделе про лимиты.

Слушать я советую именно 127.0.0.1, а TLS вешать на nginx перед процессом. Тогда сертификат живёт в одном месте, и validateHttpsCertificates в таблице можно оставить включённым. Для самого обработчика я беру адреса с ротацией внутри пула: список отдаётся живым, смена узла на выходе идёт автоматически, и выпавший узел не роняет утренний прогон.

Тот же обработчик на PHP, если на сервере уже стоит веб-сервер с PHP:

<?php // fetch.php
if (($_SERVER['HTTP_X_RELAY_TOKEN'] ?? '') !== getenv('RELAY_TOKEN')) {
    http_response_code(401); exit;
}
$in    = json_decode(file_get_contents('php://input'), true);
$url   = $in['url'] ?? '';
$host  = parse_url($url, PHP_URL_HOST);
$allow = explode(',', (string) getenv('ALLOW_HOSTS'));
if (!$host || !in_array($host, $allow, true)) { http_response_code(403); exit; }

$ch = curl_init($url);
curl_setopt_array($ch, [
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_PROXY          => getenv('PROXY_URL'),
    CURLOPT_FOLLOWLOCATION => true,
    CURLOPT_MAXREDIRS      => 3,
    CURLOPT_TIMEOUT        => 25,
    CURLOPT_USERAGENT      => 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)',
]);
$body = (string) curl_exec($ch);
$code = (int) curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
header('Content-Type: application/json');
echo json_encode(['status' => $code, 'len' => strlen($body),
                  'body' => substr($body, 0, 4 * 1024 * 1024)]);

Для варианта с SOCKS5 добавьте CURLOPT_PROXYTYPE => CURLPROXY_SOCKS5_HOSTNAME, тогда имя хоста резолвится на стороне посредника. В Node ту же задачу закрывает socks-proxy-agent, подключается он на месте ProxyAgent.

4. Вызов обработчика из таблицы

Со стороны Apps Script остаётся обычный POST. Токен я держу в свойствах проекта, в коде его нет:

const RELAY_URL = 'https://relay.example.net/fetch';

function relayFetch_(url) {
  const token = PropertiesService.getScriptProperties().getProperty('RELAY_TOKEN');
  const res = UrlFetchApp.fetch(RELAY_URL, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'X-Relay-Token': token },
    payload: JSON.stringify({ url: url }),
    muteHttpExceptions: true,
    followRedirects: false,
    validateHttpsCertificates: true
  });
  const code = res.getResponseCode();
  if (code !== 200) {
    throw new Error('relay ' + code + ': ' + res.getContentText().slice(0, 200));
  }
  return JSON.parse(res.getContentText());
}

Ключ muteHttpExceptions обязателен. Без него любой ответ с кодом от 400 превращается в исключение, и вы теряете тело ошибки, по которому видно причину. followRedirects: false тут стоит осознанно: переходы отрабатывает обработчик на сервере, таблице достаточно итогового тела.

Пачками работать быстрее. Метод UrlFetchApp.fetchAll отправляет массив запросов параллельно и возвращает массив ответов в том же порядке:

function relayFetchAll_(urls) {
  const token = PropertiesService.getScriptProperties().getProperty('RELAY_TOKEN');
  const reqs = urls.map(function (u) {
    return {
      url: RELAY_URL,
      method: 'post',
      contentType: 'application/json',
      headers: { 'X-Relay-Token': token },
      payload: JSON.stringify({ url: u }),
      muteHttpExceptions: true
    };
  });
  return UrlFetchApp.fetchAll(reqs).map(function (r) {
    return r.getResponseCode() === 200
      ? JSON.parse(r.getContentText())
      : { status: r.getResponseCode(), len: 0, body: '' };
  });
}

function collectToSheet() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Сбор');
  const last = sh.getLastRow();
  const urls = sh.getRange(2, 1, last - 1, 1).getValues()
    .map(function (r) { return String(r[0]).trim(); })
    .filter(String);

  const out = [];
  for (let i = 0; i < urls.length; i += 20) {
    relayFetchAll_(urls.slice(i, i + 20)).forEach(function (r) {
      const m = /<title[^>]*>([\s\S]*?)<\/title>/i.exec(r.body || '');
      out.push([r.status, r.len || 0, m ? m[1].trim() : '']);
    });
    Utilities.sleep(400);
  }
  sh.getRange(2, 2, out.length, 3).setValues(out);
}

Обратите внимание на последнюю строку: значения пишутся одним вызовом setValues. Построчная запись через setValue внутри цикла работает в десятки раз медленнее и съедает время запуска на пустом месте. Я переписал этот кусок после того, как прогон на 300 целей упёрся в предел по времени при живом сетевом канале.

Размер пачки в 20 подобран под потоки. На обычном пакете доступно 1000 потоков, на корпоративном до 3000, при двух привязанных адресах лимит делится пополам. Двадцать параллельных обращений от таблицы разворачиваются на сервере в двадцать соединений с посредником, запас тут огромный, и упереться получится скорее в свой сервер. Для утренних прогонов по сотням страниц я держу пул, рассчитанный на парсинг сайтов: главное здесь ровный темп на всей серии. Скорость одиночного запроса на итог влияет слабо, счёт идёт по числу целей, закрытых за один запуск.

5. Путь второй: сбор на своей машине и запись в таблицу

Здесь Apps Script вообще не участвует в сетевой части. Скрипт на вашей машине ходит через посредника обычным клиентом и кладёт готовые строки в таблицу через Google Sheets API. Порядок подготовки короткий:

Создайте проект в Google Cloud Console и включите в нём Google Sheets API. Заведите сервисный аккаунт, выпустите для него ключ формата JSON и скачайте файл. Откройте адрес сервисного аккаунта из поля client_email внутри файла и выдайте ему права редактора на нужную таблицу через обычную кнопку «Настройки доступа». Никаких браузерных подтверждений при запуске после этого не потребуется.

# collect.py   pip install requests google-api-python-client google-auth
import os, re, requests
from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

PROXY = os.environ["PROXY_URL"]        # http://u4821:s7Kd2Rm9@203.0.113.24:8000
PROXIES = {"http": PROXY, "https": PROXY}
SHEET = os.environ["SHEET_ID"]

creds = Credentials.from_service_account_file(
    os.environ["SA_KEY"],
    scopes=["https://www.googleapis.com/auth/spreadsheets"],
)
api = build("sheets", "v4", credentials=creds).spreadsheets().values()

src = api.get(spreadsheetId=SHEET, range="Сбор!A2:A").execute().get("values", [])
urls = [row[0].strip() for row in src if row and row[0].strip()]

rows = []
for url in urls:
    try:
        r = requests.get(url, proxies=PROXIES, timeout=25,
                         headers={"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"})
        t = re.search(r"<title[^>]*>(.*?)</title>", r.text, re.S | re.I)
        rows.append([r.status_code, len(r.content), t.group(1).strip() if t else ""])
    except requests.RequestException as e:
        rows.append([0, 0, type(e).__name__])

api.update(spreadsheetId=SHEET,
           range="Сбор!B2:D" + str(len(rows) + 1),
           valueInputOption="RAW",
           body={"values": rows}).execute()

Словарь PROXIES покрывает оба протокола. Для схемы socks5h:// поставьте pip install requests[socks], буква h в схеме означает резолв имени на стороне посредника. Обращения к самому API Google я через посредника не пускаю: они идут напрямую и на пул нагрузки не дают.

Второй путь снимает потолок по времени полностью, и прогон на несколько тысяч страниц спокойно крутится час. Здесь удобно, что обмен на пакете не считают, и полные HTML вместе с картинками выкачиваются без арифметики по объёму. Прогоны по готовым шаблонам идут тем же порядком, адреса под них разобраны на странице про пакет под шаблонные прогоны ZennoPoster. Тяжёлый разбор через lxml или selectolax тоже переезжает на вашу машину, и таблица получает уже разобранные значения.

6. Ограничения по времени выполнения и числу обращений

Квоты Apps Script задевают только первый путь. Цифры отличаются у обычного аккаунта и у аккаунта Workspace:

Про последнюю строку скажу отдельно. Соблазн написать пользовательскую функцию вида =GETTITLE(A2) и растянуть её на столбец возникает у всех. Таблица пересчитывает такие формулы при каждом открытии файла, каждая ячейка тратит своё обращение, и суточная квота выгорает за пару часов работы с документом. Сбор запускается функцией из меню или триггером, результат ложится в ячейки статикой.

Обход потолка по времени строится через курсор. Скрипт запоминает, где остановился, и сам ставит себе следующий запуск:

function collectChunk() {
  const props = PropertiesService.getScriptProperties();
  const sh = SpreadsheetApp.getActive().getSheetByName('Сбор');
  const urls = sh.getRange(2, 1, sh.getLastRow() - 1, 1).getValues()
    .map(function (r) { return String(r[0]).trim(); });

  const t0 = Date.now();
  let i = Number(props.getProperty('CURSOR') || 0);

  while (i < urls.length && Date.now() - t0 < 4 * 60 * 1000) {
    const chunk = urls.slice(i, i + 20);
    const res = relayFetchAll_(chunk).map(function (r) {
      const m = /<title[^>]*>([\s\S]*?)<\/title>/i.exec(r.body || '');
      return [r.status, r.len || 0, m ? m[1].trim() : ''];
    });
    sh.getRange(i + 2, 2, res.length, 3).setValues(res);
    i += chunk.length;
  }

  dropOwnTriggers_('collectChunk');
  if (i < urls.length) {
    props.setProperty('CURSOR', String(i));
    ScriptApp.newTrigger('collectChunk').timeBased().after(60 * 1000).create();
  } else {
    props.deleteProperty('CURSOR');
  }
}

Порог в 4 минуты оставляет запас до предела в 6 минут: последняя пачка успевает дописаться, и триггер создаётся до принудительной остановки. Вызов dropOwnTriggers_ перед созданием нового спасает от переполнения списка триггеров, иначе за неделю их накопится больше двадцати и создание упрётся в отказ.

7. Хранение ключей и учётных данных

Токен обработчика и строка подключения к посреднику в тексте скрипта не живут никогда. В Apps Script для этого есть свойства проекта, они правятся кодом либо через панель настроек проекта:

function setSecrets() {
  PropertiesService.getScriptProperties().setProperties({
    RELAY_TOKEN: 'вставить значение, выполнить функцию, стереть строку'
  });
}

Помните, кому видны эти значения. Любой человек с правами редактора на таблицу открывает редактор кода и читает свойства проекта одной строкой. Если таблицу смотрят десять человек, выдавайте им доступ на чтение, а сбор держите в отдельном автономном проекте Apps Script, который пишет в файл от своего имени. Тогда редакторы видят цифры без доступа к токену.

На сервере переменные обработчика я держу в файле окружения systemd с правами 600:

# /etc/relay.env
RELAY_TOKEN=9f3c1a7be0d64b2f8c5a
PROXY_URL=http://u4821:s7Kd2Rm9@203.0.113.24:8000
ALLOW_HOSTS=example.net,shop.example.org,api.ipify.org

Файл подключается строкой EnvironmentFile=/etc/relay.env в юните и в репозиторий не попадает. Ключ JSON сервисного аккаунта для второго пути лежит там же по соседству с теми же правами, путь к нему передаётся переменной SA_KEY. Ротация токена делается за минуту: новое значение в файл, перезапуск сервиса, новое значение в свойства проекта.

Отдельная строка про привязку адреса. Наружу от обработчика уходит внешний адрес вашего сервера, именно его вносят в кабинете. Внутренний адрес контейнера или локальный интерфейс в поле привязки уйдёт впустую. В пакет входят две привязки, менять их можно свободно, так что боевой сервер и запасной живут рядом. Вариант с логином освобождает от привязки вовсе и удобен, когда адрес сервера меняется. Обработчик у меня ходит по HTTPS до целей и по HTTP до посредника, для этой пары я беру пакет с протоколом HTTPS: туннель поднимается методом CONNECT, содержимое до конечного сайта остаётся зашифрованным.

8. Запуск по расписанию через триггеры

Триггер создаётся в интерфейсе, значок часов на левой панели редактора, либо кодом. Кодом удобнее: настройка переезжает вместе с проектом.

function installTriggers() {
  dropOwnTriggers_('collectChunk');
  ScriptApp.newTrigger('collectChunk')
    .timeBased()
    .atHour(6)
    .everyDays(1)
    .inTimezone('Europe/Moscow')
    .create();
}

function dropOwnTriggers_(name) {
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return t.getHandlerFunction() === name; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
}

Метод atHour(6) задаёт окно, внутри которого Google сам выберет минуту запуска. Точное время получить нельзя, разброс доходит до часа, и планировать сбор впритык к отчёту не стоит. Часовой пояс берётся из настроек проекта, поэтому inTimezone я указываю явно: файлы часто создают в одном поясе, а смотрят в другом.

Триггер выполняется от имени того, кто его создал. Если этот человек потеряет доступ к таблице, запуски встанут молча, без письма и без записи в журнале. Проверяйте владельца триггеров при передаче проекта.

Для второго пути расписание живёт на вашей стороне. На Windows это планировщик заданий с действием python C:\work\collect.py, на Linux строка 0 6 * * * /usr/bin/python3 /opt/collect/collect.py в crontab. Переменные окружения планировщик Windows не наследует, задавайте их в самом скрипте через чтение файла либо оберните вызов в .cmd.

9. Проверка после настройки

Сначала проверяем обработчик с самого сервера, без участия таблицы:

curl -sS -X POST http://127.0.0.1:8443/fetch \
  -H 'Content-Type: application/json' \
  -H "X-Relay-Token: $RELAY_TOKEN" \
  -d '{"url":"https://api.ipify.org/?format=json"}'
curl -s https://api.ipify.org; echo

Первая команда печатает адрес, который увидел внешний сайт через посредника. Вторая печатает адрес самого сервера напрямую. Значения обязаны различаться. Совпадение означает, что переменная PROXY_URL пустая либо разобрана неверно. Повторите первую команду 5 раз подряд: адрес будет меняться, потому что ротация внутри пула работает без вашего участия, а самих адресов там около 12 000.

Дальше проверяем связку из таблицы. Добавьте функцию и выполните её кнопкой запуска в редакторе:

function checkRelay() {
  const r = relayFetch_('https://api.ipify.org/?format=json');
  Logger.log('status=' + r.status + ' len=' + r.len + ' body=' + r.body);
}

Вывод открывается через «Журнал выполнений» на левой панели. Строка со статусом 200 и телом, где стоит внешний адрес, означает готовую цепочку. Сверьте адрес из журнала с тем, что печатал curl со своей машины: они должны отличаться, иначе обработчик ходит напрямую.

Третья проверка касается защиты обработчика. Повторите запрос без заголовка с токеном и с посторонним доменом в поле url:

curl -s -o /dev/null -w '%{http_code}\n' -X POST http://127.0.0.1:8443/fetch \
  -H 'Content-Type: application/json' -d '{"url":"https://example.com/"}'
curl -s -o /dev/null -w '%{http_code}\n' -X POST http://127.0.0.1:8443/fetch \
  -H 'Content-Type: application/json' -H "X-Relay-Token: $RELAY_TOKEN" \
  -d '{"url":"https://169.254.169.254/latest/meta-data/"}'

Ожидаемые коды 401 и 403. Любой другой ответ означает открытый обработчик, его нужно закрыть до первого боевого прогона.

Последним смотрю саму таблицу. Запускаю collectChunk руками, слежу за заполнением столбцов B, C и D, потом открываю «Журнал выполнений» и сверяю длительность. Если один запуск укладывается в 3 минуты, дневная квота триггеров выдержит десяток прогонов. Для второго пути та же проверка делается одной командой на своей машине:

python -c "import os,requests as q;p=os.environ['PROXY_URL'];print(q.get('https://api.ipify.org',proxies={'http':p,'https':p},timeout=15).text)"

10. Разбор ошибок

Отдельный сюжет: обработчик отвечает, ячейки заполняются, а через сутки часть строк приходит с нулевой длиной тела. Смотрите на параллелизм и на привязки. Пакеты по потокам не складываются, при двух привязанных адресах лимит делится пополам, и сборщик, поднявший 700 параллельных соединений при второй активной привязке, упрётся в границу и получит отказы на установке соединения. Считайте параллелизм по фактической половине лимита. Я для утреннего прогона держу адреса под регулярный сбор данных с запасом по потокам и никогда не выхожу выше двух третей от доступного числа. Сбор позиций по ключам я держу отдельной программой, и адреса туда идут тем же файлом: адреса для съёма позиций в Key Collector берутся из того же пакета, поэтому поведение целевых сайтов остаётся предсказуемым от прогона к прогону.

Смежные сценарии на серверной стороне разобраны на соседних страницах справочника: запуск заданий с прокси в PowerShell для машин под Windows, настройка посредника в Docker и docker-compose, если обработчик поедет в контейнер, и обмен данными в 1С через посредника. Когда выгрузка нужна в настольной таблице без облака, порядок описан в статье про запросы через посредника в Excel и Power Query.