How to quickly get an approximate cost of equipment based on the project specification

Sometimes our company participates in tenders and in order for it to be profitable, the company must estimate its costs for the construction and equipment of the facility before submitting a price application for participation in the competition. To estimate costs, the equipment specification for the working documentation is usually taken and tables with prices for goods and services are compiled.

This assessment very often involves a large amount of mechanical searching for prices of goods and services on the Internet on suppliers' websites.

At some point I wanted to simplify the process and wrote a small script for Google Custom Search Engine (CSE) on Google Apps Script for Google Sheets, which automatically goes through all the searched positions on the list and makes a list of current prices and links to their sources. Although such a mechanism cannot be used without subsequent manual verification of adequacy, the script itself greatly simplifies, facilitates and speeds up the work.

How does the script work and what does it do?

⚠️ A working example is here ⚠️

This script is a combination of Google Apps Script functions that automate product price lookups using Google Sheets and Google Custom Search Engine (CSE).

The code is designed to read a Google Sheets tab called “Поиск” and extracting the product or service names from it, then using the Google Custom Search API to get pricing information for those products from the web. The results are then written back to a Google spreadsheet, displaying them as a clickable link with the product name and current price:

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. Main function: processSearchTab

This function processes each row in the Search tab, looks up product prices, and pastes the results back into the spreadsheet.

How it works:

• The function starts by selecting the Search sheet and getting all its data.

• It goes through each line (starting with the second, since the first is considered a heading).

• For each row, it checks to see if the product name is in column B (productName) and is the flag value in column F (flagValue) number –Number.isFinite().

• If both conditions are met, the function calls searchProductPrice with the product name.

• The returned result includes a hyperlink (a link to the product page with a descriptive title) and a price, which are stored in an array.

• Finally, the result array is written back into columns G and H of the sheet.

2. Helper function: searchProductPrice

The function performs the actual search for product prices using the Google Custom Search API:

• The function creates a search query using the product name and a predefined search phrase (secrets.initial_phrase“), For example Пермь купить цена. That's because I'm in Perm 🙂

• The function then sends an API request to Google's Custom Search service, which searches the web for relevant results.

• If results are found, she looks through them to see if they contain price information.

• The first valid price found is formatted and returned along with the product link and title.

• If the price is not found, the function returns a fallback response indicating that there are no results.

Features of the script for searching prices on the Internet

• API Quotas: The script uses the Google Custom Search API, which has usage limits of 100 queries per day. For larger volumes, additional strategies may be required – such as using multiple API keys.

• Data reliability. Price accuracy depends on data available in Google search results, which may not always display the most current prices.

In general, it is not easy to bypass the 100 queries per day limit in Google Custom Search Engine (CSE), as it is used by Google to control API usage. However, I know of two strategies to manage these limits:

1. Use multiple API keys You can create multiple Google Cloud projects, each with its own API key, and distribute your requests among them.

2. API Key Rotation. You can implement rotation logic between these keys in your code.

How to use it in your work?

Follow this link your copy of the table will open. If you are logged into different Google accounts in your browser, you should leave only one, otherwise an error may appear when you click on the link. The link will prompt you to create your own copy of the table – create a copy in your account.

Next, open the Google Apps Script Editor:

– In Google Sheets, select Extensions > Apps Script.

– The Google Apps Script Editor will open in a new tab.

Now that you have the Application Script Editor open, run the function called processSearchTab(). To start it, click the play button (triangle icon).

When you first run the script, you need to log in to work. This will require authorization in your account. Do not be afraid that the script will somehow harm you – at any time you can view the list of permissions you have granted on a special page and revoke them in one click. Google will warn you that the application is not verified. You need to select “Additional settings”, and then follow the link below. After that, you will need to click on the “Allow” button, and the initial setup will be ready.

Additionally, for this script to work, you need to get the Google Custom Search API key and the Custom Search Engine (CSE) identifier. How to get them is described above.

By following these steps, you will be able to successfully run the price search script for all of Russia.

Instead of conclusions

The script automates what would otherwise be a tedious manual process of searching for product prices online and entering the results into a spreadsheet.

Using Google's Custom Search API, the script accesses a wide range of online sources to obtain up-to-date pricing information.

The search query is configured using the region countryRUwhich allows us to adapt the results to the Russian market.

The script writes the results back to a spreadsheet with hyperlinks displaying the product name, making it easy to navigate the data.

Author: Mikhail Shardin

September 2, 2024