Иногда наша компания участвует в тендерах и чтобы это было выгодно компания должна оценить свои затраты на строительство и оснащение объекта ещё до подачи ценовой заявки на участие в конкурсе. Для оценки затрат обычно берётся спецификация оборудования к рабочей документации и составляются таблицы с ценами на товары и услуги.

При этой оценке очень часто присутствует большой объем механического поиска цен товаров и услуг в интернете на сайтах поставщиков.

В какой-то момент мне захотелось упростить процесс и на Google Apps Script для Гугл Таблиц написал небольшой скрипт для Google Custom Search Engine (CSE) который автоматически обходит все искомые позиции по списку и составляет список актуальных цен и ссылок на их источники. Хотя без последующей ручной проверки на адекватность использовать такой механизм нельзя, но сам скрипт очень сильно упрощает, облегчает и ускоряет работу.

Как работает и что делает скрипт

⚠️ Работающий пример здесь ⚠️

Этот скрипт представляет собой комбинацию функций Google Apps Script, которая автоматизируют поиск цен на товары с помощью Google Таблиц и системы пользовательского поиска Google (CSE).

Код предназначен для считывания вкладки Google Sheets под названием «Поиск» и извлечения из неё названий товаров или услуг, а затем использования API пользовательского поиска Google для получения информации о ценах на эти продукты из Интернета. Затем результаты записываются обратно в гугл таблицу, отображая их кликабельной ссылкой с названием продукта и текущей ценой:

const secrets = {
  apiKey_token: 'хххххх',  // Замените своим ключом API
  cx_token: 'хххххх', // Замените идентификатором Google's Custom Search Engine (CSE) ID 
  // https://cse.google.com/cse?cx=хххххх&cr=countryRU
  initial_phrase: '' // Начинать все запросы со слов // Пермь купить цена 
};

function processSearchTab() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Поиск');
  var data = sheet.getDataRange().getValues();
  var results = [];

  for (var i = 1; i < data.length; i++) { // Начнём с 1й, чтобы пропустить строку заголовка
    var productName = data[i][1]; // Колонка B
    var flagValue = data[i][5]; // Колонка F

    if (productName && Number.isFinite(flagValue)) {
      var result = searchProductPrice(productName);
      var hyperlink = '=HYPERLINK("' + result.link + '"; "' + result.title + '")';
      results.push([hyperlink, result.price]);
    } else {
      results.push(['', '']); // Если условия не выполняются, оставим ячейки пустыми
    }
  }

  // Запишем результаты обратно в столбцы G и H.
  var range = sheet.getRange(2, 7, results.length, 2); // Начиная со 2-го столбца, столбец G(7) и H(8)
  range.setValues(results);
}

function test() {
  searchProductPrice("Труба НПВХ SDR41 - 160x4,0мм");
}

function searchProductPrice(productName) {
  var apiKey = secrets.apiKey_token;
  var cx = secrets.cx_token; 
  productName = productName //.replace('-', ' ');
  var query = encodeURIComponent(secrets.initial_phrase + productName);
  var url = 'https://www.googleapis.com/customsearch/v1?q=' + query + '&cr=countryRU&cx=' + cx + '&key=' + apiKey; 
  // https://developers.google.com/custom-search/v1/reference/rest/v1/cse/list?hl=ru
  console.log(`url: ${url}`);

  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());

  if (json.items && json.items.length > 0) {
    for (var i = 0; i < json.items.length; i++) {
      if (json.items[i].pagemap && json.items[i].pagemap.offer && json.items[i].pagemap.offer[0].price) {
        var price = json.items[i].pagemap.offer[0].price;
        price = price.replace('.', ',');
        var link = json.items[i].link;
        var title = json.items[i].title;
        console.log(`${productName}:\n${title}: ${price} в ${link}`);

        return {
          price: price,
          link: link,
          title: title
        };
      }
    }
  } 

  console.log(`Ничего не найдено для ${productName}`);
  return {
    price: '0',
    link: 'https://ya.ru/search/?text=' + query,
    title: 'Ничего не найдено'
  };
}

1. Основная функция: processSearchTab

Эта функция обрабатывает каждую строку на вкладке «Поиск», ищет цены на товары и вставляет результаты обратно в электронную таблицу.

Как она работает:

• Функция начинается с выбора листа «Поиск» и получения всех его данных.

• Он проходит через каждую строку (начиная со второй, поскольку первая считается заголовком).

• Для каждой строки она проверяет, есть ли название продукта в столбце B (productName) и является ли значение флага в столбце F (flagValue) числом -Number.isFinite().

• Если оба условия соблюдены, функция вызывает searchProductPrice с именем продукта.

• Возвращаемый результат включает гиперссылку (ссылку на страницу продукта с описательным заголовком) и цену, которые хранятся в массиве.

• Наконец, массив результатов записывается обратно в столбцы G и H листа.

2. Вспомогательная функция: searchProductPrice

Функция выполняет фактический поиск цен на продукты с помощью API пользовательского поиска Google:

• Функция создает поисковый запрос, используя название продукта и предопределенную поисковую фразу («secrets.initial_phrase»), например Пермь купить цена. Это потому что я в Перми :)

• Затем функция отправляет запрос API в службу пользовательского поиска Google, которая ищет в Интернете релевантные результаты.

• Если результаты найдены, она просматривает их, проверяя, содержат ли они информацию о цене.

• Первая найденная действительная цена форматируется и возвращается вместе со ссылкой на продукт и названием.

• Если цена не найдена, функция возвращает резервный ответ, указывающий на отсутствие результатов.

Особенности работы скрипта по поиску цен в интернете

• Квоты API: Скрипт использует API пользовательского поиска Google, который имеет ограничения на использование: 100 запросов в день. Для использования в больших объемах могут потребоваться дополнительные стратегии - например, использование нескольких ключей API.

• Надежность данных. Точность цен зависит от данных, доступных в результатах поиска Google, которые не всегда могут отображать самые свежие цены.

Вообще обойти ограничение в 100 запросов в день в системе пользовательского поиска Google (CSE) непросто, поскольку оно применяется Google для контроля использования API. Однако мне известно две стратегии управления этими ограничениями:

1. Использование несколько ключей API. Можно создать несколько проектов Google Cloud, каждый со своим собственным ключом API, и распределить между ними свои запросы.

2. Ротация ключей API. Можно реализовать в своем коде логику ротации между этими ключами.

Как использовать в своей работе?

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

Дальше откройте редактор сценариев Google Apps:

– В Google Таблице выберите «Расширения» > «Apps Script».

– Редактор скриптов Google Apps откроется на новой вкладке.

Теперь, когда у вас открыт редактор сценариев приложений запустите функцию под названием processSearchTab(). Чтобы её запустить нажмите кнопку воспроизведения (значок треугольника).

При первом запуске скрипту необходимо авторизоваться для работы. Для этого потребуется авторизация в вашем аккаунте. Не бойтесь, что скрипт как-то повредит вам - в любой момент вы можете посмотреть список выданных вами разрешений на специальной странице и в один клик их отозвать. Гугл предупредит, что приложение не проверено. Нужно выбрать «Дополнительные настройки», а потом перейти по нижней ссылке. После этого останется нажать на кнопку «Разрешить», и на этом первоначальная настройка будет готова.

Дополнительно для работы этого скрипта необходимо получить ключ API Google Custom Search API и идентификатор системы пользовательского поиска (CSE). Как их получить расписано выше.

Выполнив эти шаги, вы сможете успешно запустить скрипт поиска цен по всей России.

Вместо выводов

Скрипт автоматизирует то, что в противном случае было бы утомительным ручным процессом поиска цен на товары в Интернете и ввода результатов в таблицу.

Используя API пользовательского поиска Google, скрипт получает доступ к широкому спектру онлайн-источников для получения актуальной информации о ценах.

Поисковый запрос настраивается с использованием региона countryRU, что позволяет адаптировать результаты к российскому рынку.

Скрипт записывает результаты обратно в электронную таблицу с гиперссылками, отображающими название продукта, что упрощает навигацию по данным.

Автор: Михаил Шардин

2 сентября 2024 г.