Integrating Go-UPC with Airtable

Manual and Bulk Barcode Data Retrieval

Overview

This guide provides a comprehensive walkthrough for integrating Airtable with Go-UPC’s API. The integration enables you to populate product details either by manually entering individual barcodes or by pasting multiple barcodes in bulk. It allows automatic filling of fields such as product name, description, brand, image URL, and more, based on barcode inputs.

The integration supports two methods of data retrieval:

  1. Manual Entry: Enter a single barcode to pull product information from Go-UPC.
  2. Bulk Entry: Paste multiple barcodes separated by commas, and the script processes each one, creating a separate record for each product.

Requirements

  1. Airtable Account: Ensure you have access to a plan with scripting extensions.
  2. Go-UPC API Key: Sign up at Go-UPC to generate an API key. This key authenticates requests to the Go-UPC database.

Step-by-Step Guide

Step 1: Set Up Airtable Base and Table

  1. Create a New Base: Log in to Airtable, create a new base, and name it "Go-UPC Product Integration."
  2. Rename the Default Table: Change the table name to "Product Data."
  3. Add Columns to Match Go-UPC API Response:
  • Barcode (Single Line Text): Input for each barcode.
  • Product Name (Single Line Text)
  • Description (Long Text)
  • Product Image (Attachment)
  • Brand (Single Line Text)
  • Category (Single Line Text)
  • Specs (Long Text): JSON format for additional product specifications.
  • Dimensions: Height, Width, and Length (Single Line Text)
  • Organic and Non-GMO (Checkbox)

Note: Ensure that column names exactly match those returned by Go-UPC’s API to avoid any discrepancies in data mapping.

Step 2: Enable the Scripting Extension

  1. Add the Scripting Extension: Within the base, open "Extensions" and select "+ Add Extension."
  2. Choose Scripting: Select "Scripting" from the available extensions, then name the script "Go-UPC Lookup Script."

Step 3: Insert the Updated Script

In the script editor, replace any existing code with the script below. This code provides options for single-barcode entry or multiple-barcode processing in bulk.

Airtable Screenshot 1 
let table = base.getTable('Table 1');

// Prompt the user for input method in English
let inputType = await input.buttonsAsync("Choose entry method:", [
  {label: "Enter Barcode Manually", value: "manual"},
  {label: "Paste CSV Content (Barcodes Separated by Comma)", value: "pasteCsv"}
]);

// Function to process each barcode individually
async function processBarcode(barcode) {
let apiKey = 'YOUR_API_KEY'; // Substitute with your Go-UPC API key
let apiUrl = `https://go-upc.com/api/v1/code/${barcode}`;

try {
  // Request product data from Go-UPC API
  let response = await remoteFetchAsync(apiUrl, {
    method: "GET",
    headers: {
      Authorization: `Bearer ${apiKey}`
    }
  });

  if (response.ok) {
    let productData = await response.json(); // Parse JSON data from API response

    // Initialize an empty object for the new record
    let newRecord = {};

    // Ensure fields exist before setting values
    if (table.getField("code")) newRecord["code"] = { text: barcode }; // Keep barcode as a text object
    if (productData.hasOwnProperty('codeType') && table.getField("codeType")) newRecord["codeType"] = productData.codeType || "";
    if (productData.product && productData.product.hasOwnProperty('name') && table.getField("name")) newRecord["name"] = productData.product.name || "";
    if (productData.product && productData.product.hasOwnProperty('description') && table.getField("description")) newRecord["description"] = productData.product.description || "";
    if (productData.product && productData.product.hasOwnProperty('imageUrl') && table.getField("imageUrl")) newRecord["imageUrl"] = productData.product.imageUrl || "";
    if (productData.product && productData.product.hasOwnProperty('brand') && table.getField("brand")) newRecord["brand"] = productData.product.brand || "";

    // Convert specs to JSON format if available
    if (productData.product && productData.product.hasOwnProperty('specs') && table.getField("specs")) {
      newRecord["specs"] = JSON.stringify(productData.product.specs) || "";
    }

    // Check for dimensions and store if fields are available
    if (productData.product && productData.product.hasOwnProperty('specs')) {
      if (productData.product.specs?.[0]?.[1] && table.getField("height")) newRecord["height"] = productData.product.specs[0][1] || "";
      if (productData.product.specs?.[1]?.[1] && table.getField("width")) newRecord["width"] = productData.product.specs[1][1] || "";
      if (productData.product.specs?.[2]?.[1] && table.getField("length")) newRecord["length"] = productData.product.specs[2][1] || "";
    }

    // Add new record to Airtable table
    await table.createRecordAsync(newRecord);
    output.text(`Record for barcode ${barcode} created successfully!`);

  } else {
    // Handle common HTTP errors
    switch (response.status) {
      case 400:
      output.text("400 Bad Request: The provided code is not in a recognized format.");
      break;
      case 401:
        output.text("401 Unauthorized: Access denied due to authentication failure.");
      break;
      case 404:
        output.text("404 Not Found: No product information found for the provided barcode.");
      break;
      case 429:
        output.text("429 Too Many Requests: You have exceeded your quota or made too many requests in a short period.");
      break;
      default:
        output.text(`Unknown error: ${response.status}`);
    }
  }

} catch (error) {
  // Handle network or connection errors
  output.text(`Network error: ${error}`);
}
}

// Process based on user's choice
if (inputType === "manual") {
// Manual barcode entry
let barcode = await input.textAsync("Enter the barcode:");
if (!barcode) {
  output.text("No barcode provided.");
  return;
}
await processBarcode(barcode);

} else if (inputType === "pasteCsv") {
// Ask the user to paste barcodes in a single line, separated by commas
let csvContent = await input.textAsync("Paste barcodes here, separated by commas:");

if (!csvContent) {
  output.text("No content pasted.");
  return;
}

// Split barcodes by comma and trim excess whitespace
let barcodes = csvContent.split(",").map(barcode => barcode.trim());

for (let barcode of barcodes) {
  if (barcode) await processBarcode(barcode);
}
output.text("Batch processing completed successfully!");
}

Step 4: Run and Test the Script

  1. Select Input Method: Click "Run" within the script extension. Choose to enter a single barcode manually or paste multiple barcodes as a CSV.
  2. Process Multiple Barcodes: Paste barcodes separated by commas, and the script will create individual records for each entry.
  3. Verify Data: After each run, verify that product details populate the correct fields. A successful batch run will confirm with "Batch processing completed successfully!"

Additional Information

This integration supports both single and bulk processing, offering significant advantages over conventional spreadsheet software. For expanded functionality, you may consider using automation tools like Zapier or Make (formerly "Integromat") to trigger the script automatically for new entries.

Support and Documentation

For more details, refer to the Go-UPC API documentation or contact our support team.