Your Email Inbox is Your Most Important Employee
In every e-commerce and B2B company, this process exists. Someone (often a manager or the owner) opens their email inbox first thing in the morning. They're waiting for a message from supplier X with the subject 'New Price List' or 'Stock Update'. Then, they manually download the attachment (Excel, CSV, XML), open it, compare it with old prices, calculate the new markup, and finally, manually log in to Base (BaseLinker) (or another system) to update the prices. This is a bottleneck. It's a waste of time, a source of costly errors, and a job nobody likes.
Most companies don't know this process can be 100% automated. Your inbox isn't just a messenger. For many suppliers who don't offer an API, email *is* the API. Let's treat it as a data transfer system.
How to Design Price List Automation?
The process consists of four key stages. The first is the most important: identification.
Stage 1: Identification (Simple vs. AI Version)
The Simple Version (Recommended): The Gentleman's Agreement.
Instead of building complex AI, call your supplier and set simple rules. This is the best and most reliable method. Agree that the price list will *always* be sent:
- From the same email address (e.g., `pricelist@supplier.com`)
- With the same, fixed subject (e.g., `[AUTO] New CSV Price List`)
- Always with the same attachment name (e.g., `pricelist.csv`)
- Always in the same file format and structure (e.g., columns: `SKU`, `NetPrice`, `Stock`).
With these constants, writing an automaton is trivial. The script logs into the inbox and looks for emails matching these four conditions.
The Complex Version (AI): If the supplier is uncooperative and sends emails with subjects like 'february prices', 'new prices', 'price list 03.2025', a rule-based automaton will fail. This is when you can bring in an AI model (e.g., Gemini) to first *read* the email content and assess if it's a price list, and then *intelligently analyze* the file structure, guessing which column is SKU and which is price. However, this is more expensive and more prone to errors if the AI misinterprets the data.
Stage 2: Download and Parse the File
Once the script finds the right email, it downloads the attachment. Then it needs to 'understand' it. This is where the Node.js libraries you mentioned come in:
- CSV: Use `csv-parse/sync` to turn a CSV file into an array of JSON objects.
- XML: Use `xml2js` to convert an XML tree into a JavaScript object.
- Excel (XLSX): Use `xlsx` or `exceljs` to read data from a spreadsheet.
The result of this stage is a clean data array, e.g., `[{ sku: 'A001', price: 100.00 }, { sku: 'A002', price: 150.00 }]`.
Stage 3: Apply Pricing Strategy (Your Advantage)
This is the most important business step. You don't want to sell at your supplier's prices. The automaton must now loop through the downloaded data and apply your pricing strategy. This is the power of a custom solution—you can implement any logic here:
const MARGIN_PERCENT = 1.30; // 30% markup
const FLAT_FEE = 1.00; // $1.00 flat fee
const processedProducts = parsedData.map(product => {
const supplierPrice = parseFloat(product.price);
let finalPrice;
// Example logic: if the product is expensive, use a smaller margin
if (supplierPrice > 1000) {
finalPrice = supplierPrice * 1.15; // 15% margin
} else {
finalPrice = (supplierPrice * MARGIN_PERCENT) + FLAT_FEE;
}
// Round to .99
finalPrice = Math.floor(finalPrice) + 0.99;
return {
sku: product.sku,
price: finalPrice
};
});
Stage 4: Update Prices via Base (BaseLinker) API
We have a ready list of products with new prices. Time to send them to Base (BaseLinker). As per the API documentation, we'll use the `updateInventoryProductsPrices` method. We need to prepare the data in the correct format (an array where the key is the product/variant ID, and the value is an array of prices for price groups).
Assuming we have `inventory_id = 1234` and `price_group_id = 1` (main price group) and we are mapping SKUs to Base (BaseLinker) product IDs, the API call (e.g., via `axios`) would look something like this:
// This is a simplification. In practice, you must convert SKU to product ID
// (e.g., by keeping a map in a DB or fetching products from Base (BaseLinker) by SKU)
const productsPayload = {
"100001": { "1": 135.99 }, // Product ID 100001 (from SKU A001)
"100002": { "1": 200.99 } // Product ID 100002 (from SKU A002)
};
const response = await axios.post(
'https://api.baselinker.com/connector.php',
new URLSearchParams({
method: 'updateInventoryProductsPrices',
parameters: JSON.stringify({
inventory_id: 1234,
products: productsPayload
})
}),
{
headers: {
'X-BLToken': 'YOUR-BASELINKER-API-TOKEN',
'Content-Type': 'application/x-www-form-urlencoded'
}
}
);
Example Node.js Script with Cron
The code below is a skeleton you can build upon. It uses `node-cron` to run every hour, `imap-simple` to connect to an IMAP inbox (works with Gmail, O365, etc.), and `csv-parse`.
Disclaimer: Remember security. Keep passwords, tokens, and credentials in a `.env` file, not in the code!
'use strict';
// Dependencies: npm install node-cron imap-simple mailparser csv-parse axios dotenv
// Run: node email-bot.js
import cron from 'node-cron';
import imaps from 'imap-simple';
import { simpleParser } from 'mailparser';
import { parse } from 'csv-parse/sync';
import axios from 'axios';
import 'dotenv/config'; // For environment variables
// --- SCRIPT CONFIGURATION (from .env file) ---
const imapConfig = {
imap: {
user: process.env.EMAIL_USER,
password: process.env.EMAIL_PASSWORD, // For Gmail/Google Workspace, use an "App Password"
host: process.env.EMAIL_HOST, // e.g., 'imap.gmail.com'
port: 993,
tls: true,
authTimeout: 3000
}
};
const BASELINKER_TOKEN = process.env.BASELINKER_TOKEN;
const INVENTORY_ID = parseInt(process.env.BASELINKER_INVENTORY_ID, 10);
const PRICE_GROUP_ID = parseInt(process.env.BASELINKER_PRICE_GROUP_ID, 10);
// --- Email Search Criteria ---
const SEARCH_CRITERIA = [
'UNSEEN', // Only unread
['FROM', 'pricelist@supplier.com'], // From whom
['SUBJECT', '[AUTO] New CSV Price List'] // What subject
];
/**
* Main automation function
*/
async function runAutomation() {
console.log(`[${new Date().toISOString()}] Running price list automation...`);
let connection;
try {
connection = await imaps.connect(imapConfig);
await connection.openBox('INBOX');
const messages = await connection.search(SEARCH_CRITERIA, { bodies: ['HEADER.FIELDS (FROM SUBJECT)'] });
if (messages.length === 0) {
console.log('No new price lists found.');
await connection.end();
return;
}
console.log(`Found ${messages.length} new price lists. Processing the first one...`);
const message = messages[0];
const allParts = await connection.getParts(message.attributes.uid, [message.attributes.uid]);
const attachmentPart = allParts.find(part =>
part.disposition && part.disposition.type.toLowerCase() === 'attachment' && part.disposition.params.filename.endsWith('.csv')
);
if (!attachmentPart) {
console.error('ERROR: Email found, but no .csv attachment.');
// Mark as read to avoid reprocessing
await connection.addFlags(message.attributes.uid, '\Seen');
await connection.end();
return;
}
const attachmentBuffer = await connection.getPartData(message, attachmentPart);
const attachmentContent = attachmentBuffer.toString('utf-8');
// 1. PARSE CSV
const records = parse(attachmentContent, {
columns: true,
skip_empty_lines: true,
delimiter: ';' // Adjust to your file's delimiter
});
console.log(`Downloaded and parsed file. Found ${records.length} products.`);
// 2. PRICING LOGIC & PAYLOAD PREP
// At this point, you MUST map the supplier SKU to your Base (BaseLinker) Product ID
// The below is a MOCK that assumes you have a map { 'SUPPLIER-SKU': 123456 (Base (BaseLinker) ID) }
const skuToBlIdMap = await getSkuToBlIdMap(); // You must implement this function
const productsPayload = {};
for (const record of records) {
const sku = record.SKU; // Column name from CSV
const price = parseFloat(record.NetPrice.replace(',', '.'));
const blId = skuToBlIdMap[sku];
if (blId && !isNaN(price)) {
// Apply your pricing strategy
const finalPrice = (price * 1.30) + 1.00; // 30% margin + $1 flat
productsPayload[blId] = {
[PRICE_GROUP_ID]: Math.floor(finalPrice) + 0.99 // .99 price
};
}
}
console.log(`Prepared payload for ${Object.keys(productsPayload).length} products.`);
// 3. SEND TO BASELINKER
const response = await axios.post(
'https://api.baselinker.com/connector.php',
new URLSearchParams({
method: 'updateInventoryProductsPrices',
parameters: JSON.stringify({
inventory_id: INVENTORY_ID,
products: productsPayload
})
}),
{ headers: { 'X-BLToken': BASELINKER_TOKEN, 'Content-Type': 'application/x-www-form-urlencoded' }}
);
if (response.data.status === 'SUCCESS') {
console.log('SUCCESS! Prices updated in Base (BaseLinker).');
console.log(`Updated products: ${response.data.counter}`);
if(response.data.warnings && Object.keys(response.data.warnings).length > 0) {
console.warn('Warnings from Base (BaseLinker):', response.data.warnings);
}
// Mark email as read on success
await connection.addFlags(message.attributes.uid, '\Seen');
} else {
console.error('Base (BaseLinker) ERROR:', response.data);
}
await connection.end();
} catch (err) {
console.error('A critical automation error occurred:', err);
if (connection) await connection.end();
}
}
// Helper function - you must implement this
async function getSkuToBlIdMap() {
// Here, connect to your DB or fetch products from Base (BaseLinker)
// to create the mapping: {'SUPPLIER_SKU': 123456, ...}
console.log('Fetching SKU to Base (BaseLinker) ID map... (MOCK)');
return {
'A001': 100001,
'A002': 100002,
'B001': 100003
};
}
// --- CRON JOB ---
// Run the task every hour on weekdays
console.log('Registering CRON job (will run every hour, 8am-6pm, Mon-Fri).');
cron.schedule('0 8-18 * * 1-5', runAutomation, {
scheduled: true,
timezone: "Europe/Warsaw"
});
// Run once on start for testing
// runAutomation();