Иногда наша компания участвует в тендерах и чтобы это было выгодно компания должна оценить свои затраты на строительство и оснащение объекта ещё до подачи ценовой заявки на участие в конкурсе. Для оценки затрат обычно берётся спецификация оборудования к рабочей документации и составляются таблицы с ценами на товары и услуги.
При этой оценке очень часто присутствует большой объем механического поиска цен товаров и услуг в интернете на сайтах поставщиков.
В какой-то момент мне захотелось упростить процесс и на 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. Однако мне известно две стратегии управления этими ограничениями:
-
Использование несколько ключей API. Можно создать несколько проектов Google Cloud, каждый со своим собственным ключом API, и распределить между ними свои запросы.
-
Ротация ключей 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:
-
Перейдите в Google Cloud Console.
-
Если у вас нет учетной записи Google, вам необходимо ее создать. Войдите, используя свои учетные данные.
Создать новый проект:
-
В Cloud Console щелкните раскрывающееся меню рядом с названием вашего проекта в верхнем левом углу.
-
Нажмите «Новый проект», чтобы создать новый проект. Дайте ему имя и запишите идентификатор проекта (он понадобится вам позже).
Включите API пользовательского поиска:
-
Выбрав проект, перейдите в Библиотеку API и сервисов.
-
Найдите «API пользовательского поиска» (Custom Search API).
-
Нажмите API пользовательского поиска, а затем нажмите «Включить», чтобы добавить его в свой проект.
Создание учетных данных (ключ API):
-
Перейдите в раздел «Учетные данные» в Cloud Console.
-
Нажмите «Создать учетные данные» и выберите «Ключ API».
-
Будет создан новый ключ API. Скопируйте этот ключ и сохраните его в надежном месте, так как он будет использоваться в вашем приложении для аутентификации запросов к API пользовательского поиска.
Получение идентификатора системы пользовательского поиска (CSE)
Доступ к системе пользовательского поиска Google:
-
Посетите страницу Система пользовательского поиска Google.
-
Войдите в свою учетную запись Google.
Создайте новую систему пользовательского поиска:
-
Нажмите «Добавить», чтобы создать новую систему пользовательского поиска.
-
Укажите имя своей поисковой системы и укажите сайты, которые вы хотите включить в область поиска (вы можете добавить определенные URL-адреса или установить область для всего Интернета).
-
После создания Google присвоит вашей системе пользовательского поиска уникальный идентификатор поисковой системы (также известный как «cx» или идентификатор CSE).
Получите свой идентификатор CSE:
-
На панели управления системой пользовательского поиска щелкните название своей поисковой системы.
-
Перейдите в раздел «Настройка», где вы найдете свой идентификатор системы пользовательского поиска (
cx
). Этот идентификатор имеет решающее значение при вызовах API, поскольку он сообщает Google, какую систему пользовательского поиска использовать для ваших запросов.
Как использовать в своей работе?
По этой ссылке откроется ваша копия таблицы. Если в браузере выполнен вход в разные гугл-аккаунты, то нужно оставить только один, иначе при переходе по ссылке может появиться ошибка. По ссылке Вам предложат создать свою копию таблицы — создайте копию в свой аккаунт.
Дальше откройте редактор сценариев Google Apps:
– В Google Таблице выберите «Расширения» > «Apps Script».
– Редактор скриптов Google Apps откроется на новой вкладке.
Теперь, когда у вас открыт редактор сценариев приложений запустите функцию под названием processSearchTab()
. Чтобы её запустить нажмите кнопку воспроизведения (значок треугольника).
При первом запуске скрипту необходимо авторизоваться для работы. Для этого потребуется авторизация в вашем аккаунте. Не бойтесь, что скрипт как-то повредит вам — в любой момент вы можете посмотреть список выданных вами разрешений на специальной странице и в один клик их отозвать. Гугл предупредит, что приложение не проверено. Нужно выбрать «Дополнительные настройки», а потом перейти по нижней ссылке. После этого останется нажать на кнопку «Разрешить», и на этом первоначальная настройка будет готова.
Дополнительно для работы этого скрипта необходимо получить ключ API Google Custom Search API и идентификатор системы пользовательского поиска (CSE). Как их получить расписано выше.
Выполнив эти шаги, вы сможете успешно запустить скрипт поиска цен по всей России.
Вместо выводов
Скрипт автоматизирует то, что в противном случае было бы утомительным ручным процессом поиска цен на товары в Интернете и ввода результатов в таблицу.
Используя API пользовательского поиска Google, скрипт получает доступ к широкому спектру онлайн-источников для получения актуальной информации о ценах.
Поисковый запрос настраивается с использованием региона countryRU
, что позволяет адаптировать результаты к российскому рынку.
Скрипт записывает результаты обратно в электронную таблицу с гиперссылками, отображающими название продукта, что упрощает навигацию по данным.
Автор: Михаил Шардин
2 сентября 2024 г.
ссылка на оригинал статьи https://habr.com/ru/articles/837616/
Добавить комментарий