Google Sheets CSV Builder
What Is This Tool?
The Travelgate Inventory CSV Builder is a Google Sheets template pre-configured by Travelgate. It helps you create, validate, and export all inventory CSV files without building them manually.
The tool covers all 8 inventory entities:
| Sheet | CSV Entity | Purpose |
|---|---|---|
| 🏨 HotelsMaster | hotelsMaster | Register hotels in the master catalog |
| 🛏️ RoomsMaster | roomsMaster | Define room type master records |
| 🏩 Hotels | hotels | Activate hotels and configure booking notifications |
| 💰 RatePlans | rateplans | Create and configure rate plans per hotel |
| 🚪 Rooms | rooms | Assign room types to rate plans |
| 📋 Policies | policies | Define cancellation policies per rate |
| 🧾 Taxes | taxes | Configure taxes and surcharges per rate |
| 🎁 Offers | offers | Set up promotional offers per rate |
Workflow Overview
You create your personal sheet copy using the button below
↓
You fill in ⚙️ Config (Client Code + Supplier Code)
↓
Load dynamic data from the API → dropdowns auto-populated
↓
Fill data sheet by sheet (follow the recommended order)
↓
Validate all sheets → fix errors highlighted in red
↓
Export CSVs → rename if needed → upload to SFTP ✅
Step 1 — Get Your Own Copy of the Builder
Click the button below to create your own private copy of the Travelgate Inventory CSV Builder. Google will open a dialog asking you to confirm the destination Google Drive — the copy then belongs entirely to you.
📄 Make my own copy
Each copy is a fully independent Google Sheet with its own Apps Script instance. Your data, your dropdowns and your exported CSV files belong only to your Google Drive — multiple clients can create and use their copies in parallel without ever interfering with each other.
The Travelgate API key required to load dynamic data is not stored inside the sheet. The Apps Script bundled with your copy calls a Travelgate-hosted endpoint that holds the credentials server-side, so the key is never visible in any cell, formula, or script property — not even to you.
After the copy is created, Google will open it automatically. Continue with Step 2 to configure your codes.
Step 2 — Configure Client and Supplier Codes
Open the ⚙️ Config tab and fill in:
| Setting | Description | Example |
|---|---|---|
| Client Code | Your Travelgate inventory client code | AXI |
| Supplier Code | Your channel manager provider code | HOTP |
These codes are used automatically to name your exported CSV files.
Go to My Connections, click Get form data on any access:
- The Password field → your Client Code
- The User field → your Supplier Code
Step 3 — Initialize Sheets
Click 📦 Inventory CSV → ⚙️ Initialize Sheets in the top menu.
This will:
- Create (or reset) all 8 data sheets with correct column headers
- Apply data validation dropdowns for enum fields (Active, PaymentType, Currency, etc.)
- Add column tooltips — hover over any header cell to read the expected format
- Clear any previous data and reset cell highlight colors
Re-running Initialize Sheets will erase all data in all sheets. Only do this when starting a completely new batch.
Step 4 — Load Dynamic Data from the API
Click 📦 Inventory CSV → 🔄 Load Dynamic Data (API)
This fetches live data from Travelgate and populates the following dropdowns automatically:
| Field | Sheet | Description |
|---|---|---|
| Context codes | HotelsMaster, Hotels | Inventory contexts available for your supplier |
| Hotel categories | HotelsMaster | Full hotel category catalog |
| Meal plans | RatePlans | All available meal plan names |
| Room Master codes | Rooms | Your existing rooms master codes |
If a dropdown is empty after loading, the API returned no results for your Client/Supplier combination. Double-check the codes in ⚙️ Config.
Step 5 — Fill In Your Data
Fill each sheet following the order below. Order matters: each entity references data from the previous ones (e.g. Rooms reference Rate Codes from RatePlans).
1. 🏨 HotelsMaster ← Only if hotels are not yet in the master catalog
2. 🛏️ RoomsMaster ← Define room types before assigning them
3. 🏩 Hotels ← Activate hotels and set notification emails
4. 💰 RatePlans ← Create rate plans per hotel
5. 🚪 Rooms ← Assign room types to rate plans
6. 📋 Policies ← Cancellation conditions per rate
7. 🧾 Taxes ← Taxes and surcharges per rate
8. 🎁 Offers ← Promotional offers per rate
Formatting Rules
| Field type | Expected format | Notes |
|---|---|---|
| Dates | dd/MM/yyyy | e.g. 14/04/2026. Date-type cells convert automatically. |
| Boolean | True or False | Dropdowns enforce valid values. The export converts them to 1/0 automatically (the format expected by setup_loading). |
| Multi-value | Pipe-separated | | e.g. MerchantPay|LaterPay or ES|FR|DE |
| Markets Included | ISO-2 codes or empty | Empty = all markets. ALL is accepted and exported as empty. |
| Markets Excluded | ISO-2 codes or empty | Empty = none excluded. N/A is accepted and exported as empty. |
| Decimal numbers | . or , | 18.5 and 18,5 are both valid |
Hover over any header cell to read a tooltip describing the expected format. This is the quickest way to check requirements without leaving the sheet.
For complete field-by-field specifications, see the individual entity pages:
- Hotels Master · Rooms Master
- Hotels · RatePlans · Rooms
- Policies · Taxes · Offers
Step 6 — Validate Your Data
Click 📦 Inventory CSV → ✅ Validate All Sheets
The validator checks every row across all 8 sheets and:
- Highlights invalid cells in red (hard errors)
- Highlights suspicious code values in orange (e.g.
##########,{HotelCode},----— values that are technically non-empty but almost certainly corrupted) - Shows a copyable modal dialog listing all errors and suspicious findings, so you can paste the full list into Gemini or another assistant for help
- Displays "✅ All sheets valid!" when everything passes
Fix all red and orange cells before exporting. Both errors and suspicious values block the export. Common errors and their solutions:
| Error message | Cause | Fix |
|---|---|---|
Id is required / Id must be numeric | Hotels sheet: the Id column must contain the numeric Travelgate hotel ID | Copy the numeric Id from your HotelsMaster |
Invalid email format | A malformed email address | Use a valid email; multiple addresses must be pipe-separated |
Markets Included must be ISO-2 | A value like SPAIN was entered | Use 2-letter codes (ES, FR) or leave the cell empty |
Uses format: 2-0-0|2-0-1 | Room occupancy format is wrong | Use adults-children-infants e.g. 2-0-0|2-1-0 |
Booking From: use dd/MM/yyyy | Date format is incorrect | Type as dd/MM/yyyy or use a Google Sheets date cell |
Currency is required | Currency column is empty | Select a value from the dropdown |
⚠️ SOSPECHOSO (Suspicious) — Code looks invalid | A code cell contains a corrupted or placeholder value (e.g. ##########, {HotelCode}, ----) | Replace it with the actual code. These cells are highlighted in orange. |
Validation runs automatically again at export time. If errors or suspicious values exist, the export is blocked until all red and orange cells are fixed.
Step 7 — Export CSV Files
Once validation passes, export via the menu:
- 📦 Inventory CSV → 📥 Export CSV → [Sheet name] — exports one entity
- 📦 Inventory CSV → 📥 Export CSV → 📦 Export All — exports all 8 entities at once (silent/batch mode — no per-file pop-ups, one final summary dialog)
Each export:
- Re-validates the sheet (export blocked if errors or suspicious values remain)
- Generates files using semicolon (
;) delimiter and UTF-8 encoding - Converts boolean values from
True/Falseto1/0automatically - Auto-splits per-hotel entities (rateplans, rooms, policies, taxes, offers) into one file per hotel, using the context from the Hotels / HotelsMaster sheet
- Names files automatically following the correct naming convention:
- Masters:
{ClientCode}-{SupplierCode}-hotelsMaster.csv - Hotels setup:
{ClientCode}-{SupplierCode}-hotels.csv - Per-hotel entities:
{ClientCode}-{SupplierCode}-{HotelCode}#{ContextCode}-{entity}.csv
- Masters:
- Saves files to your Google Drive root and downloads them to your local machine via a browser dialog
Since version 3.4 of the script, the tool automatically splits setup entities by hotel code and includes the #ContextCode suffix in the filename. The context is read from the Context column of the Hotels or HotelsMaster sheet. You no longer need to rename files manually before uploading to the SFTP.
If a hotel referenced in a setup entity (e.g. RatePlans) does not have a Context defined in the Hotels or HotelsMaster sheet, the export will fail and list the affected hotel codes. Fill in the Context column for those hotels before exporting.
Step 8 — Upload to SFTP
Upload the exported CSV files to:
sftp://sftp.travelgate.com:22
Place the files inside the Inventory folder. The system checks for new files approximately every hour. After a file is picked up, it is renamed with a status suffix:
| Suffix | Meaning |
|---|---|
-Queued | File has been detected and is waiting to be processed |
-Processing | File is currently being processed |
-Processed | File was loaded successfully ✅ |
-Error | Processing failed — check file name and field values |
Monitor these suffixes on the SFTP to track your uploads.
For full SFTP setup instructions, see Uploading Files to SFTP.
Working with Multiple Hotels or Clients
Multiple hotels, same client and supplier
All sheets support multiple rows. Fill RatePlans, Rooms, Policies, Taxes, and Offers for all hotels in one session. The Hotel Code column in each setup sheet groups the rows — on export, the tool automatically splits the data into one file per hotel, each named with the correct HotelCode#ContextCode suffix. No manual renaming is needed.
Different clients or suppliers
Create a separate sheet copy per client/supplier combination using the same Make my own copy button. Update ⚙️ Config in each copy. Exported file names will reflect the correct codes automatically.
Team collaboration on the same sheet
- Coordinate who edits each sheet (e.g. person A fills RatePlans, person B fills Policies)
- Use File → Version history → See version history in Google Sheets to review and revert changes
- Avoid simultaneous editing of the same sheet to prevent data conflicts
Troubleshooting
| Symptom | Solution |
|---|---|
| Menu 📦 Inventory CSV not visible | Reload the page (F5). The menu loads via an onOpen script trigger. |
| Dropdowns empty after Initialize | Run 🔄 Load Dynamic Data (API) |
| Load Dynamic Data fails | Verify Client Code and Supplier Code in ⚙️ Config. Contact Travelgate support if the issue persists. |
| Export is blocked (red cells) | Fix all red-highlighted cells (validation errors), then retry |
| Export is blocked (orange cells) | Orange cells indicate suspicious/corrupted code values (e.g. ##########, {HotelCode}). Replace them with the actual codes. |
| Export fails with "Missing context" | The Context column is empty for one or more hotels referenced in setup entities. Fill in the Context in the Hotels or HotelsMaster sheet. |
# in filename saved as %23 | No action needed — the system decodes %23 → # automatically during processing. If your SFTP client or OS still causes issues, upload the file from Google Drive instead (or rename %23 back to #). |
File stuck as -Processing after 2+ hours | Contact Travelgate support with the file name and upload timestamp |
ALL / N/A in Markets fields | Valid inputs — converted to empty on export (= all markets / no exclusions) |