Image analysis with AI

Media: Project media

Contents

This automation was requested by one of the startups. Their request was to create an automated system for analyzing TV series or movie posters for subsequent transcription of these posters into a text assignment for a graphic editor, so that the editor could copy the style of the finished image.

The AI’s task was to “view” the image and determine its content.

Example of a single cell.

Media: Project media

Part of the code

const OPENAI_API_KEY = 'XXX';

// This is the main function that analyzes all creative images in the "Creatives" sheet
// It calls OpenAI Vision API for each image and fills columns D-I with analysis results
function analyzeCreativesWithChatGPT() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Creatives');
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  for (var i = 1; i < values.length; i++) {
    var id = values[i][0];
    var advertiser = values[i][1];
    var imgUrl = values[i][2];
    if (!imgUrl) continue;

    var fields = analyzeImageWithOpenAI(imgUrl);

    // Convert keywords array to comma-separated string if needed
    if (Array.isArray(fields.keywords)) {
      fields.keywords = fields.keywords.join(", ");
    }

    sheet.getRange(i+1, 4, 1, 6).setValues([[
      fields.aspect_ratio,
      fields.keywords,
      fields.topic,
      fields.short_description,
      fields.audience,
      fields.task_for_designer
    ]]);

    Logger.log('Row ' + (i+1) + ' filled: ' + JSON.stringify(fields));
  }
}

// "XXX", this function sends image URL to OpenAI Vision API (gpt-4o model) and returns structured analysis
function analyzeImageWithOpenAI(imageUrl) {
  var apiUrl = 'https://api.openai.com/v1/chat/completions';
  var promptText = 
    'You are an expert advertising creative analyst. Your task is to analyze advertising creative images and provide structured insights for designers.\n\n' +
    'Analyze the image and return your response in JSON with these keys:\n' +
    '- aspect_ratio\n' +
    '- keywords\n' +
    '- topic\n' +
    '- short_description\n' +
    '- audience\n' +
    '- task_for_designer';

  var payload = {
    model: "gpt-4o",
    messages: [
      {
        role: "user",
        content: [
          { type: "text", text: promptText },
          { type: "image_url", image_url: { url: imageUrl } }
        ]
      }
    ],
    max_tokens: 500
  };

  var options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + OPENAI_API_KEY
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  var response = UrlFetchApp.fetch(apiUrl, options);
  var json = JSON.parse(response.getContentText());
    Logger.log('API Response: ' + JSON.stringify(json));
      if (!json.choices || !json.choices[0]) {
            throw new Error('Invalid API response: ' + JSON.stringify(json));
              }
  var content = json.choices[0].message.content;

  // OpenAI sometimes wraps JSON in markdown code blocks, so extract it
  var jsonMatch = content.match(/```json\s*([\s\S]*?)\s*```/);
  if (jsonMatch) {
    content = jsonMatch[1];
  }

  var result = JSON.parse(content);
  return result;
}

You can view the entire table at the link.