Stop wasting days on manual data entry. With ocart.net, you can build a professional online store and manage your entire inventory directly from Google Sheets using our automated synchronization technology.

Changing product IDs or updating thousands of items shouldn't be a headache. This guide shows you how to use our "drag-and-fill" method to keep your entire database perfectly synchronized.

Step-by-Step Instructions

1. Install the Automation Script

  • Open your inventory Google Sheet.
  • Navigate to Extensions > Apps Script.
  • Delete any existing code and paste the Bulk ID Updater script provided below.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ID Management')
    .addItem('Bulk Update IDs', 'bulkUpdateIds')
    .addToUi();
}

function bulkUpdateIds() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  
  const lr = sheet.getLastRow();
  if (lr < 2) return;

  const data = sheet.getRange(2, 1, lr - 1, 2).getValues();
  const idMap = {};
  let hasChanges = false;

  for (let i = 0; i < data.length; i++) {
    const oldId = String(data[i][0]).trim();
    const newId = String(data[i][1]).trim();
    if (oldId && newId && oldId !== newId) {
      idMap[oldId] = newId;
      hasChanges = true;
    }
  }

  if (!hasChanges) {
    SpreadsheetApp.getUi().alert("No new IDs detected. Please fill Column B with your new IDs.");
    return;
  }

  const allSheets = ss.getSheets();

  allSheets.forEach(targetSheet => {
    if (targetSheet.getName() === sheet.getName()) return;
    
    const targetLr = targetSheet.getLastRow();
    if (targetLr > 1) {
      const targetRange = targetSheet.getRange(2, 1, targetLr - 1, 1);
      const values = targetRange.getValues();
      let changed = false;

      for (let i = 0; i < values.length; i++) {
        const currentId = String(values[i][0]).trim();
        if (idMap[currentId]) {
          values[i][0] = idMap[currentId];
          changed = true;
        }
      }
      if (changed) targetRange.setValues(values);
    }
  });

  const finalColumnA = data.map(row => [row[1] !== "" ? row[1] : row[0]]);
  sheet.getRange(2, 1, finalColumnA.length, 1).setValues(finalColumnA);
  sheet.deleteColumn(2);
  
  SpreadsheetApp.getUi().alert("Success! Your online store database is synchronized.");
}
  • Click Save and name it "Store ID Manager".

2. Refresh & Initialize

  • Refresh your browser tab.
  • A new menu called ID Management will appear at the top navigation bar.

3. Mass Update Your Products

  • On your main product sheet, right-click Column A (product_id) and select Insert 1 column right.
  • In the new Column B, enter your new ID sequence.
  • Note: Keep the old IDs in Column A so the script can find and update them.

4. Run the Sync

  • Click ID Management > Bulk Update IDs.
  • The script will instantly update every page of your store's database.