Quick & Easy Data Cleansing with OpenAI's GPT and Imagga API

TL;DR - Efficiently clean and validate messy data using OpenAI's GPT Google Sheets extension and Imagga API, transforming chaos into order just in time for the Black Friday sale.

Picture this: you're an analyst at FashionNova, and you've been assigned to tidy up the store by sorting shirts into more recognizable colors (ROYGBIV). You receive the backend data, but it's chaotic. The tags are a mix of random words related to color, style, and manufacturer.

And guess what? The Black Friday sale starts in 30 minutes. No pressure, right?

Here's a snapshot of the mess you're dealing with: just the image source and the tags determining the colors.

Your mission? Accomplish two tasks: 1) extract the colors from the tags, and 2) verify their accuracy.

Fear not! First, let's activate the GPT Google Sheets extension to help us extract the colors from these chaotic tags.

With the right formula and prompt, task one: check!

=GPT("Please extract the exact colors from this cell. Do not include any hex values. Seperate multiple colors with commas",D2,0.1)

Now, let's validate those colors by pulling them from the images. To do this, you'll need the Imagga API. Good news: they offer 1,000 free calls a month - more than enough for our needs!

Head to https://imagga.com/profile/dashboard, sign up, and grab your API key and API secret.

Next, open your Google Sheets and add a custom function in App Scripts (here's an official tutorial if you need a refresher).

Here's the code you'll need:

function ColorID(url) {
  // Get the active sheet from the active spreadsheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Retrieve the API key and secret from cells B1 and B2
  const apiKey = sheet.getRange("B1").getValue();
  const apiSecret = sheet.getRange("B2").getValue();
  
  // Construct the API URL with the provided image URL
  const apiUrl = 'https://api.imagga.com/v2/colors?image_url=' + encodeURIComponent(url);
  
  // Set up the request options with the API key and secret for authorization
  const options = {
    headers: {
      'Authorization': 'Basic ' + Utilities.base64Encode(apiKey + ':' + apiSecret),
    },
  };
  
  try {
    // Fetch the API response and parse the JSON data
    const response = UrlFetchApp.fetch(apiUrl, options);
    const data = JSON.parse(response.getContentText());
    
    // Extract and process the foreground colors from the API response
    const foregroundColors = data.result.colors.foreground_colors.map(color => ({
      color: color.html_code,
      closest_palette_color: color.closest_palette_color,
      closest_palette_color_parent: color.closest_palette_color_parent,
      percent: color.percent,
    })).sort((a, b) => b.percent - a.percent);
    
    // Return only the largest closest_palette_color_parent
    return foregroundColors[0].closest_palette_color_parent;
  } catch (error) {
    // Handle errors and display a message
    throw new Error('Error fetching image colors. Please try again.');
  }
}

Now, hop back into your spreadsheet and input your API key and API secret in cells B1 and B2.

Finally, call the =ColorID("url") function, pointing it to the cell with the Image URL, and voila! Task two: complete.

Congratulations! You've successfully harnessed the power of OpenAI's GPT and Imagga API to clean up a data disaster and save the day, just in time for the Black Friday rush.

To grab the Spreadsheet template that was discussed in this product, grab it here - https://practicalgpt.gumroad.com/l/imagga1.

If you would like to work with us - contact us here.