How to Track the Cheapest Hotel Prices Across Top Vendors Using Hotel Price API and Google Sheets

Are you a travel agent, travel agency, or part of a travel business looking to score the best luxury hotel deals for your clients? You’re in the right place.

And here’s the good news—you don’t need to know how to code. This solution is simple. Follow the steps below to build it from scratch. Plus, you’ll get a ready-to-use blueprint at the end. Download it, edit it, and start pulling the cheapest hotel prices across top vendors using Makcorps API.

In this tutorial, we’ll be using 10 Big Brands in Sydney to find out which vendor offers the lowest price for each one.

Let’s get into it.

What You’ll Need:

• A free account on MakCorps.com
• Google Sheet

Step 1: Get Your Makcorps API Key

First, sign up for a free account on Makcorps.com. 

Verify your email address. 

Once you’re in, check your email for your API key. 

A free account gives you 15 API calls to get started. If you need more, you can upgrade anytime to access more credits. Check out the pricing from here. 

With your API key ready, make sure you also save the two API endpoints we’ll be using throughout this process:

1. Hotel Mapping API – This helps you pull the hotel ID. (Full details are in the documentation.)
2. Hotel Price API – This gets you price comparison data. You’ll need the hotel ID from the first API to use this one. (More info in the documentation.)

Keep both handy. We’ll be switching between them as we move forward.

Step 2: Prepare the Google Sheet

Now, let’s set up your Google Sheet. We’ll create four tabs, each handling a different part of the process.

Here’s what you’ll do:

1) input tab

• In this tab, you’ll list out all the hotel names you want to track.
• Example: Enter the hotel names under the Hotel Name column.
• Keep it simple—one hotel name per row and also add the city name with the hotel name. 

2) hotel id output tab

• This is where you’ll store the hotel IDs that you’ll get from the Hotel Mapping API.
• Column A: Hotel Name
• Column B: Hotel ID
• Leave this empty for now—we’ll automate this in the next step.

3) price tracking input tab

• Here’s where you’ll set up the details for pulling price data:
  • Column A: Hotel Name
  • Column B: Hotel ID
  • Column C: Check-In Date
  • Column D: Check-Out Date
  • Column E: Adults
  • Column F: Rooms
• Fill out the dates, adults, and rooms based on your data needs.

4) price tracking output tab

• This is where the API will send the vendor pricing data. Just add the headers.
  • Column A: Hotel Name
  • Column B: Vendors
  • Column C: Price
  • Column D: Tax

5) cheapest vendors list tab

• Finally, we’ll filter the cheapest vendor per hotel here.
  • Column A: Hotel Name
  • Column B: Cheapest Vendors
  • Column C: Price
  • Column D: Tax

This setup will make it easy to pull, organize, and spot the best prices across multiple vendors for each hotel.

The tabs are ready, and next, we’ll use the API to populate the data automatically with code.

Step 3: Add App Script Code

In this step, you’ll connect your Google Sheet to the Makcorps APIs and automate everything—from pulling hotel IDs to getting the hotel price data.

Below is the code you’ll need. It does the full job:

• Extracts hotel IDs using the Hotel Mapping API
• Pulls price comparison data using the Hotel Price API
• Populates the sheet with hotel prices from multiple vendors

What to do:

1. Copy the code below.

function fetchHotelIDs() {

  var ss = SpreadsheetApp.openById("Sheet_Id"); // Your actual Spreadsheet ID

  var inputSheet = ss.getSheetByName("input"); // Sheet name for input

  var outputSheet = ss.getSheetByName("hotel id output"); // Sheet name for output

  var inputRange = inputSheet.getRange("A2:A" + inputSheet.getLastRow()); // Dynamic range for hotel names

  var hotelNames = inputRange.getValues();

  var apiResults = [];

  for (var i = 0; i < hotelNames.length; i++) {

    var hotelName = encodeURIComponent(hotelNames[i][0]);

    if (hotelName !== "") {

      var url = "https://api.makcorps.com/mapping?api_key=My API_KEY&name=" + hotelName;

      var options = {

        "method": "get",

        "muteHttpExceptions": true

      };

      var response = UrlFetchApp.fetch(url, options);

      var json = JSON.parse(response.getContentText());

      Logger.log(json); // Log API response for debugging

      if (json && json.length > 0 && json[0].hasOwnProperty("document_id")) {

        apiResults.push([hotelNames[i][0], json[0].document_id]);

      } else {

        apiResults.push([hotelNames[i][0], "No ID Found"]);

      }

    } else {

      apiResults.push([hotelNames[i][0], "Invalid Hotel Name"]);

    }

  }

  // Output results to the 'hotel id output' sheet

  if (apiResults.length > 0) {

    var outputRange = outputSheet.getRange(2, 1, apiResults.length, 2);

    outputRange.setValues(apiResults);

  }

}

function fetchHotelPrices() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var inputSheet = ss.getSheetByName("price tracking input");

  var outputSheet = ss.getSheetByName("price tracking output");

  var lastRow = inputSheet.getLastRow();

  // Start writing from the second row to preserve headers

  // Clear only the data rows, keep the headers intact

  var dataRange = outputSheet.getRange("A2:D" + outputSheet.getLastRow());

  if (outputSheet.getLastRow() > 1) {  // Check if there's data below the header

    dataRange.clearContent();

  }

  var outputRow = 2;  // Initialize outputRow to start writing from row 2

  for (var i = 2; i <= lastRow; i++) {

    var hotelName = inputSheet.getRange(i, 1).getValue();

    var hotelId = inputSheet.getRange(i, 2).getValue();

    var checkIn = formatDate(inputSheet.getRange(i, 3).getValue());

    var checkOut = formatDate(inputSheet.getRange(i, 4).getValue());

    var adults = inputSheet.getRange(i, 5).getValue();

    var rooms = inputSheet.getRange(i, 6).getValue();

    var apiUrl = 'https://api.makcorps.com/hotel?hotelid=' + hotelId + '&rooms=' + rooms + '&adults=' + adults + '&checkin=' + checkIn + '&checkout=' + checkOut + '&api_key=My API_Key';

    var options = {

      'method': 'get',

      'muteHttpExceptions': true

    };

    var response = UrlFetchApp.fetch(apiUrl, options);

    if (response.getResponseCode() == 200) {

      var json = JSON.parse(response.getContentText());

      if (json && json.comparison && json.comparison[0].length > 0) {

        var comparisonData = json.comparison[0];  // Assuming the data is in the first index

        comparisonData.forEach(function (vendorData, index) {

          var vendor = vendorData['vendor' + (index + 1)];

          var price = vendorData['price' + (index + 1)];

          var tax = vendorData['tax' + (index + 1)];

          if (vendor && price && tax) {

            outputSheet.getRange('A' + outputRow + ':D' + outputRow).setValues([[hotelName, vendor, price, tax]]);

            outputRow++;  // Increment output row for next data entry

          }

        });

      } else {

        outputSheet.getRange('A' + outputRow + ':D' + outputRow).setValues([[hotelName, 'No data', 'N/A', 'N/A']]);

        outputRow++;

      }

    } else {

      outputSheet.getRange('A' + outputRow + ':D' + outputRow).setValues([[hotelName, 'Failed to fetch', 'N/A', 'N/A']]);

      outputRow++;

      Logger.log('Failed to fetch data for Hotel ID: ' + hotelId + ' with response: ' + response.getContentText());

    }

  }

}

function formatDate(date) {

  var d = new Date(date);

  var day = ('0' + d.getDate()).slice(-2);

  var month = ('0' + (d.getMonth() + 1)).slice(-2);

  var year = d.getFullYear();

  return year + '-' + month + '-' + day;

}

function getCheapestVendors() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sourceSheet = ss.getSheetByName("price tracking output");

  const targetSheet = ss.getSheetByName("cheapest vendors list");

  const N = 1; // Change this to 1, 2, or 3 as needed

  const numRows = sourceSheet.getLastRow() - 1; // exclude header

  if (numRows <= 0) {

    Logger.log("No data found in 'price tracking output'");

    return;

  }

  const data = sourceSheet.getRange(2, 1, numRows, 4).getValues();

  const hotelGroups = {};

  data.forEach(row => {

    const [hotel, vendor, price, tax] = row;

    const numericPrice = parseFloat(price.replace(/[^0-9.]/g, '')) || 0;

    if (!hotelGroups[hotel]) {

      hotelGroups[hotel] = [];

    }

    hotelGroups[hotel].push({

      hotel,

      vendor,

      price: numericPrice,

      displayPrice: price,

      tax

    });

  });

  const output = [];

  for (let hotel in hotelGroups) {

    const sorted = hotelGroups[hotel].sort((a, b) => a.price - b.price);

    const topVendors = sorted.slice(0, N);

    topVendors.forEach(entry => {

      output.push([entry.hotel, entry.vendor, entry.displayPrice, entry.tax]);

    });

  }

  // Write below the existing header in your "cheapest vendors list" sheet

  if (output.length > 0) {

    targetSheet.getRange(2, 1, output.length, 4).setValues(output);

  }

}

1. Go to extension and then the app script. 

3. In Code.cs paste the code provided. 

4. Replace “Sheet_Id” with your actual Google Sheet ID.

This is where you will get your spreadsheet ID:

5. Replace “My API_Key” with your actual API key from Makcorps in the code. API key has to be added twice, to search for “My API_Key in the code, and replace it with your API key code. 

Step 4: Fetch Hotel ID 

After updating the Code.cs file, select fetchHotelIDs from the dropdown and click Run.

The script will now pull all the data and populate it directly into the hotel id output tab.

Step 5: Fetch Hotel Price Data

Once you have the hotel IDs, copy the names and IDs from the “hotel id output” tab and paste them into the “price tracking input” tab. 

Then, go back to Apps Script, select fetchHotelPrices from the dropdown, and run the script.

After it runs, open the “price tracking output” tab—you’ll see all the vendor pricing data populated there.

Step 6: Fetch the Cheapest Vendors List

The Hotel Price API has already populated the “price tracking output” tab. Now, let’s pull the cheapest vendors list automatically.

Head back to Apps Script, select getCheapestVendors from the dropdown, and run the script.

Once it’s done, open the “cheapest vendors list” tab. You’ll see the lowest-priced vendor for each hotel.

Want to get 2 or 3 cheapest vendors instead of just one?
No problem. You can adjust the number directly in the code. Enter 2 or 3, and you’ll get a list of the top cheapest vendors based on your selection.

To do this search in the code “const” and edit the number as per the cheapest vendors you want for each hotel from 1-3.

Check out the screenshot—I’ve set it to 3, and you can see how the list now shows the top 3 vendors per hotel.

Get Your Blueprint & Automate Everything Fast

No time for the full build?
Download the ready-made blueprint below and get started right away.

➡️ Download the Blueprint

Here’s all you need to do:

• Add your hotel names and cities.
• Plug in your API key and sheet ID.
• Choose how many cheapest vendors you want to fetch.

Then, run the scripts from Step 6 and your hotel price comparison will be ready—quick and easy.

Wrapping Up:

Tracking the cheapest hotel prices across top vendors is a powerful move for marketing for travel agencies aiming to stand out. Automating this process with Google Sheets and a trusted solution like Makcorps, one of the top hotel API providers, helps you deliver unbeatable deals, drive more bookings, and strengthen your client offers with real-time pricing.

Ready to give your agency the edge? Start building your price tracker now and level up your marketing.