Как быстро получить примерную стоимость оборудования на основе спецификации проекта

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

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

В какой-то момент мне захотелось упростить процесс и на 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().

Условие поиска if (productName && Number.isFinite(flagValue))

Условие поиска if (productName && Number.isFinite (flagValue))

  • Если оба условия соблюдены, функция вызывает 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 Custom Search API и Google Custom Search Engine (CSE) — что это и как создать ключи?

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

Google Custom Search API: этот API позволяет разработчикам взаимодействовать с возможностями поиска Google непосредственно из своих приложений. Отправляя запросы к API, вы можете получать результаты поиска в структурированном формате (например, JSON), что упрощает обработку и отображение данных по мере необходимости.

Google Custom Search Engine (CSE): это инструмент Google, который позволяет вам создать поисковую систему, адаптированную к вашим конкретным потребностям. Вы можете ограничить область поиска определенными веб-сайтами или страницами, точно настроить релевантность результатов. По сути, это Google Search, но с возможностью сосредоточиться на той части Интернета, которая наиболее актуальна для вас.

Пошаговое руководство по получению ключа API в Google Cloud Console:

  1. Перейдите в Google Cloud Console.

  2. Если у вас нет учетной записи Google, вам необходимо ее создать. Войдите, используя свои учетные данные.

Создать новый проект:

  1. В Cloud Console щелкните раскрывающееся меню рядом с названием вашего проекта в верхнем левом углу.

  2. Нажмите «Новый проект», чтобы создать новый проект. Дайте ему имя и запишите идентификатор проекта (он понадобится вам позже).

Включите API пользовательского поиска:

  1. Выбрав проект, перейдите в Библиотеку API и сервисов.

  2. Найдите «API пользовательского поиска» (Custom Search API).

  3. Нажмите API пользовательского поиска, а затем нажмите «Включить», чтобы добавить его в свой проект.

Создание учетных данных (ключ API):

  1. Перейдите в раздел «Учетные данные» в Cloud Console.

  2. Нажмите «Создать учетные данные» и выберите «Ключ API».

  3. Будет создан новый ключ API. Скопируйте этот ключ и сохраните его в надежном месте, так как он будет использоваться в вашем приложении для аутентификации запросов к API пользовательского поиска.

Получение идентификатора системы пользовательского поиска (CSE)

Доступ к системе пользовательского поиска Google:

  1. Посетите страницу Система пользовательского поиска Google.

  2. Войдите в свою учетную запись Google.

Создайте новую систему пользовательского поиска:

  1. Нажмите «Добавить», чтобы создать новую систему пользовательского поиска.

  2. Укажите имя своей поисковой системы и укажите сайты, которые вы хотите включить в область поиска (вы можете добавить определенные URL-адреса или установить область для всего Интернета).

  3. После создания Google присвоит вашей системе пользовательского поиска уникальный идентификатор поисковой системы (также известный как «cx» или идентификатор CSE).

Получите свой идентификатор CSE:

  1. На панели управления системой пользовательского поиска щелкните название своей поисковой системы.

  2. Перейдите в раздел «Настройка», где вы найдете свой идентификатор системы пользовательского поиска (cx). Этот идентификатор имеет решающее значение при вызовах API, поскольку он сообщает Google, какую систему пользовательского поиска использовать для ваших запросов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

© Habrahabr.ru