Forem: Roger Rosset

How I Show Car Photos Without Storing a Single Image

Roger Rosset — Sun, 22 Feb 2026 13:04:57 +0000

When I started building AutoFeedback — a European car review platform — I ran into an obvious problem early: I needed photos of thousands of car models, but I had no budget for a car photo API, no CDN costs to absorb, and no time to manually source and host images.

The solution turned out to be elegant, free, and hiding in plain sight.

The Problem

Most car data APIs are either expensive, require licensing agreements, or only cover popular markets. I have models like the Renault Clio, SEAT Ibiza, and Fiat Punto — bread-and-butter European cars that might not even appear in a US-centric paid API.

And even if I found a source, hosting thousands of images on R2 or S3 would mean:

A pipeline to download and store images
Storage costs that grow with the catalogue
Maintenance when images go stale

There had to be a better way.

The Solution: Wikimedia Commons

Wikimedia Commons is a free media repository with millions of images — including an enormous collection of car photos, all under Creative Commons licenses. And it has a public JSON API that requires no authentication, no API key, and has no per-request cost.

The trick: search Commons for "BMW 3 Series" and grab the first image result. Done.

const url =
  "https://commons.wikimedia.org/w/api.php" +
  "?action=query" +
  "&generator=search" +
  "&gsrsearch=" +
  encodeURIComponent(`${brand} ${model}`) +
  "&gsrnamespace=6" + // namespace 6 = File/Image namespace only
  "&prop=imageinfo" +
  "&iiprop=url" +
  "&format=json" +
  "&origin=*"; // enables CORS for browser requests

const response = await fetch(url);
const data = await response.json();
const pages = data.query?.pages;
const firstPage = Object.values(pages)[0];
const imageUrl = firstPage?.imageinfo?.[0]?.url;

That's it. I get a direct URL to a full-resolution image hosted on Wikimedia's CDN. My server never touches it.

The Svelte Component

I wrapped this in a VehicleImage component that handles loading, errors, and the fallback gracefully:

<script lang="ts">
  import { onMount } from 'svelte';

  export let brand: string;
  export let model: string;
  export let year: number | null = null;

  let imageUrl: string | null = null;
  let loading = true;
  let error = false;

  $: searchQuery = year ? `${brand} ${model} ${year}` : `${brand} ${model}`;

  async function loadImage() {
    loading = true;
    error = false;
    try {
      const url = 'https://commons.wikimedia.org/w/api.php' +
        '?action=query&generator=search' +
        '&gsrsearch=' + encodeURIComponent(searchQuery) +
        '&gsrnamespace=6&prop=imageinfo&iiprop=url&format=json&origin=*';

      const res = await fetch(url);
      const data = await res.json();
      const pages = data.query?.pages;
      const first = Object.values(pages ?? {})[0] as any;
      imageUrl = first?.imageinfo?.[0]?.url ?? null;
      if (!imageUrl) error = true;
    } catch {
      error = true;
    } finally {
      loading = false;
    }
  }

  onMount(loadImage);
</script>

{#if loading}
  <!-- Spinner -->
{:else if error || !imageUrl}
  <!-- Fallback placeholder with car icon -->
{:else}
  <img src={imageUrl} alt="{brand} {model}" class="w-full h-full object-cover" />
{/if}

The key insight: the fetch happens in the browser, not on the server. This means:

Zero server load
Zero storage costs
Images are served from Wikimedia's global CDN directly to the user
If Wikimedia is down, we show a graceful fallback — no error thrown

Why Client-Side?

I'm running on Cloudflare Workers, which has a 10ms CPU limit on free tier for some operations. A server-side fetch to Wikimedia for every page load would be wasteful and could hit timeouts. By doing it client-side with onMount, the page loads instantly with SSR content, and the image "pops in" after — a much better perceived performance story.

The Caveat: First Result Isn't Always Perfect

The first Wikimedia search result isn't guaranteed to be a perfect photo of the exact trim. Sometimes you get:

A photo from a slightly different year
A factory or motor show image
Occasionally an interior shot

That's why I added a disclaimer across all 6 languages:

"Images are illustrative and may not exactly represent the described model."

For a community review platform, this is totally acceptable. Users aren't buying from us — they're reading reviews. An approximate image is far better than a blank box.

The Legal Side: Attribution Matters

Here's something easy to miss: Wikimedia Commons images are not public domain by default.

Most are under Creative Commons licenses, which have real requirements:

License	What it requires
CC BY	Credit the author
CC BY-SA	Credit the author + share derivatives under the same license
Public Domain	No restrictions — use freely

If you just grab the URL and display the image with no attribution, you may be violating the license — even though Wikimedia doesn't technically block hotlinking.

Fetching Attribution Metadata

The good news: the same API call that returns the image URL can also return everything you need to comply. You just need to add extmetadata to the iiprop parameter:

const url =
  "https://commons.wikimedia.org/w/api.php" +
  "?action=query" +
  "&generator=search" +
  "&gsrsearch=" + encodeURIComponent(searchQuery) +
  "&gsrnamespace=6" +
  "&prop=imageinfo" +
  "&iiprop=url|extmetadata" +                          // <-- add this
  "&iiextmetadatafilter=Artist|LicenseShortName|LicenseUrl" + // only what we need
  "&format=json" +
  "&origin=*";

The extmetadata object in the response contains:

Artist.value — the author, often as an HTML string like <a href="...">John Doe</a>
LicenseShortName.value — e.g. "CC BY-SA 4.0"
LicenseUrl.value — e.g. "https://creativecommons.org/licenses/by-sa/4.0"

Rendering the Attribution

Once you have the metadata, strip the HTML from the author field (since you don't want to blindly inject it) and render a proper <figcaption>:

<figure>
  <img src={imageUrl} alt="{brand} {model}" class="w-full object-cover" />
  <figcaption class="text-xs text-gray-400 mt-1">
    Via <a href={pageUrl} target="_blank" rel="noopener noreferrer">Wikimedia Commons</a>
    {#if author} · {author}{/if}
    {#if licenseShortName}
      · <a href={licenseUrl} target="_blank" rel="noopener noreferrer license">{licenseShortName}</a>
    {/if}
  </figcaption>
</figure>

This renders something like:

Via Wikimedia Commons · Vauxford · CC BY-SA 4.0

Which is exactly what the license requires — and it adds credibility to the page rather than detracting from it.

What About Hotlinking?

Wikimedia explicitly allows external image embedding via their CDN (upload.wikimedia.org). They don't block it. But their Terms of Use and the individual file licenses still apply. Serving images without attribution is technically a license violation even if it works technically.

The correct mental model: hotlinking is permitted, attribution is required.

The Result

0 images stored in my database or on any storage bucket
0 API keys to manage or rotate
0 monthly costs for image serving
Works for every European car brand that has any Wikipedia/Commons presence
Graceful fallback for truly obscure models
Full legal compliance with a single extra API field and four lines of HTML

The entire solution is ~50 lines of Svelte. Sometimes the best engineering is finding the data that already exists, pointing at it — and reading the license.

Adding Content Moderation to a SvelteKit App with OpenAI's Moderation API

Roger Rosset — Sun, 22 Feb 2026 12:28:09 +0000

When you build a platform where users can submit free-text content, it's only a matter of time before someone tries to post something nasty. My project, AutoFeedback, a European car review platform built with SvelteKit and deployed on Cloudflare Pages, was no exception. I needed a way to catch harmful content before it ever reached the database, without over-blocking legitimate (even strongly-worded) car criticism.

Here's how I did it in under 70 lines of server-side code.

Why Not Just Block Bad Words?

The first instinct most developers have, myself included, is to build a keyword blocklist. Maintain a list of slurs, flag anything that matches. Simple, right?

In practice, it falls apart fast. Consider hate speech: it's not just a list of banned words. Hate speech relies on context, phrasing, and intent. Someone might write "people like that shouldn't be allowed to drive", no slurs, no flagged keywords, but clearly hateful depending on context. Or they might use coded language, deliberate misspellings, or Unicode tricks to bypass your list. You'd be playing an endless game of whack-a-mole, maintaining an ever-growing blocklist across multiple languages (AutoFeedback supports six), and still missing things.

And then there's the flip side: false positives. A car review that says "this car is a killer deal" or "the acceleration is insane, it absolutely murders the competition", perfectly legitimate, but a naive keyword filter would flag them. On a car review site, people use strong, emotional language all the time. That's the whole point.

Here's a quick comparison:

	Keyword Blocklist	AI Moderation
Hate speech detection	Catches known slurs only	Understands context, coded language, intent
Multi-language support	Need separate lists per language	Works across languages out of the box
Maintenance	Constant manual updates	Model improves automatically
False positives	High ("killer deal", "this car is a beast")	Very low, understands product context
Evasion	Trivial (misspellings, Unicode, spacing)	Much harder to circumvent
Setup time	Hours of curating word lists	~70 lines of code, one API call

So I looked at proper moderation APIs:

Perspective API (Google/Jigsaw), solid and battle-tested, but another Google dependency, more complex setup with API key provisioning, and a separate scoring model you need to interpret yourself
OpenAI Moderation, free to use (even without a paid OpenAI plan), purpose-built for exactly this use case, and supports their latest omni-moderation-latest model with a dead-simple API

I went with OpenAI. The endpoint checks text against categories like hate speech, harassment, self-harm, sexual content, and violence. It returns both a boolean flagged result and per-category scores. Critically for a car review site, it won't flag someone saying "this engine is terrible" or "worst purchase of my life", it understands the difference between a frustrated car owner and actual harmful content.

The Architecture

My stack:

SvelteKit for the full-stack framework
Cloudflare Pages (Workers) for hosting
Cloudflare D1 for the database
Server-side form actions for review submission

The moderation check sits between form validation and database insertion, a simple gate that rejects flagged content with a 400 error.

User submits review
    → Turnstile CAPTCHA verification
    → Zod schema validation
    → OpenAI moderation check
    → Insert into D1 database
    → Redirect to model page

The Moderation Utility

I created a single utility file at src/lib/server/moderation.ts:

interface ModerationResult {
  flagged: boolean;
  categories?: Record<string, boolean>;
}

export async function moderateReview(
  text: string,
  apiKey: string,
): Promise<ModerationResult> {
  // Fail open, if no API key, allow the review
  if (!apiKey || !text.trim()) {
    return { flagged: false };
  }

  try {
    const response = await fetch("https://api.openai.com/v1/moderations", {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        Authorization: `Bearer ${apiKey}`,
      },
      body: JSON.stringify({
        model: "omni-moderation-latest",
        input: text,
      }),
    });

    if (!response.ok) {
      console.error(
        `[Moderation] API error: ${response.status} ${response.statusText}`,
      );
      return { flagged: false }; // Fail open
    }

    const data = await response.json();
    const result = data.results?.[0];

    if (!result) {
      console.error("[Moderation] No results in response");
      return { flagged: false };
    }

    if (result.flagged) {
      const triggered = Object.entries(result.categories)
        .filter(([, v]) => v)
        .map(([k]) => k);
      console.warn(
        `[Moderation] Content flagged. Categories: ${triggered.join(", ")}`,
      );
    }

    return {
      flagged: result.flagged,
      categories: result.categories,
    };
  } catch (err) {
    console.error("[Moderation] Failed to call API:", err);
    return { flagged: false }; // Fail open
  }
}

Key Design Decisions

1. Fail open, not closed. If the OpenAI API is down, slow, or the API key isn't configured, reviews go through. I'd rather have one bad review slip past than block every legitimate user because of a third-party outage.

2. Log flagged categories. When content is rejected, I log exactly which categories were triggered. This helps me understand what's being caught and tune if needed.

3. Single concatenated string. Rather than making separate API calls for each field, I concatenate all text fields into one string. One API call, one latency hit.

Integrating Into the Form Action

In my SvelteKit form action (+page.server.ts for the review page), the integration is minimal:

import { moderateReview } from "$lib/server/moderation";

// ... inside the form action, after Zod validation passes:

// Content moderation, check text fields before storing
const apiKey = platform?.env?.OPENAI_API_KEY;
if (apiKey) {
  const { recommendation, pros, cons, summary_line } = parseResult.data;
  const textToModerate = [recommendation, pros, cons, summary_line]
    .filter(Boolean)
    .join("\n");
  const modResult = await moderateReview(textToModerate, apiKey);
  if (modResult.flagged) {
    return fail(400, {
      error:
        "Your review contains content that violates our guidelines. Please revise and try again.",
      values: data,
    });
  }
}

// If we get here, content is clean, proceed with createReview()

That's it. The user sees the same error format as a validation failure, their form values are preserved, and they can edit and resubmit.

Setting Up the API Key on Cloudflare

The OPENAI_API_KEY is stored as a Cloudflare Pages secret, never in code, never in wrangler.toml:

npx wrangler pages secret put OPENAI_API_KEY

Then in app.d.ts, I declare the type so TypeScript knows about it:

interface Platform {
  env?: {
    DB: D1Database;
    OPENAI_API_KEY: string;
    // ... other bindings
  };
}

What It Catches (and Doesn't)

Blocked:

Hate speech, including subtle, context-dependent phrasing that no keyword list would ever catch
Slurs and derogatory language targeting protected groups
Threats and incitement to violence
Sexually explicit content
Graphic violence descriptions
Self-harm content

Allowed (correctly):

"This car is absolute garbage"
"Worst money I ever spent, the dealer was useless"
"The engine sounds like it's dying"
"This thing is a death trap on wheels"
"It kills every competitor in its class"
Strong but legitimate criticism with mild profanity

This is where the AI approach really shines compared to a blocklist. A keyword filter would choke on half of those "allowed" examples. Meanwhile, the omni-moderation-latest model understands that someone ranting about their unreliable Renault is not the same as someone posting genuinely harmful content, even if both use aggressive language. In my testing across multiple languages, I haven't hit a single false positive on legitimate car reviews.

Performance Impact

The OpenAI moderation endpoint is fast, typically 50-150ms from Cloudflare Workers. Since this only runs on form submission (not page loads), users barely notice. The total review submission flow goes from ~200ms to ~300ms. Completely acceptable.

What I'd Do Differently

Rate limiting per user, Currently, a determined user could keep tweaking phrasing to bypass moderation. Adding per-user rate limits on review submissions would help.
Category-specific thresholds, The API returns confidence scores per category. I could allow borderline content in some categories (like harassment/threatening with low confidence) while being stricter on others (like sexual/minors).
Moderation queue, Instead of outright rejecting, I could put flagged reviews in a moderation queue for manual review. But for a small project, the binary accept/reject is simpler.

Wrapping Up

Adding content moderation to AutoFeedback took about an hour of work:

~70 lines for the moderation utility
~10 lines to integrate it into the form action
A few lines of translation strings per language
One wrangler secret command for the API key

The OpenAI moderation endpoint is free, fast, and remarkably accurate for UGC platforms. If you're building anything where users submit text, reviews, comments, forums, this is one of the easiest safety nets you can add.

Maximizing Your Salesforce QCP Potential: Overcoming the Character Limit Challenge

Roger Rosset — Wed, 02 Oct 2024 14:00:50 +0000

Goals

End the character limitation in Salesforce Quote Calculator Plugin (QCP) while retaining the current implementation.
Leverage the proper use of QCP for future customizations and enhancements by removing the existing code limitation.

Specifications

Salesforce QCP code is stored within a custom script field (SBQQ_CustomScript_c). Since this is a standard Salesforce object, the usual limitations, rules, and execution guidelines for Salesforce objects apply. In particular, the code is stored in the SBQQ_Code_c field, which has a maximum character limit of 131,072 characters due to its field type (Long Text Area).

In typical JavaScript development, code can be lengthy and, practically speaking, unlimited. Modern JavaScript allows the use of modules, enabling developers to separate and organize code in ways that improve readability, reusability, and maintenance across an entire application. However, when using QCP in Salesforce CPQ, the custom script is transpiled in real time by the CPQ application (usually in the Quote Line Editor), which means we are limited to having only one record read as a plugin at a time.

The setting for this can be found here:

Setup > Installed Packages > Salesforce CPQ > Configure > Plugins Tab > Quote Calculator Plugin

This configuration tells the Quote Line Editor which specific custom script record to read as the active plugin.

Unfortunately, due to the Salesforce CPQ architecture and the out-of-the-box (OOB) behavior, JavaScript modules are not natively supported for QCP. To address this limitation, a creative workaround involves adapting the code architecture to work more effectively within Salesforce, using Static Resources.

Static Resource Approach

QCP and custom scripts in Salesforce allow the invocation of Apex methods from JavaScript code by leveraging the conn parameter, which is powered by JSForce (a JavaScript library for interacting with Salesforce) to make Apex REST calls. By using this mechanism, we can design an architecture that overcomes the character limitation. Here’s how it works:

Static Resources: Store the main JavaScript logic in a Static Resource, which can hold the full, modular JavaScript code without the character limitations of a text area field.
Custom Script Record: Use the SBQQ_CustomScript_c record to include only the logic needed to call the appropriate methods or initialize processes from the Static Resource.
Apex Handler: Write an Apex handler class to facilitate communication between the QCP script and Salesforce, making it possible to evaluate the JavaScript code in the Static Resource at runtime.

This approach leverages JavaScript's eval() function in a controlled manner to load the code from the Static Resource dynamically. It’s important to note that while eval() can introduce security risks if misused, using it in this context—carefully and within the Salesforce controlled environment—can be a pragmatic solution.

Implementation Details

Custom Script Record

let QCP;

export async function onInit(quoteLineModels, conn) {
    try {
        const responseString = await conn.apex.get('/qcphandler/');
        const response = JSON.parse(responseString);
        const qcpCode = response.result;

        if (qcpCode) {
            await readQCP(qcpCode);
        } else {
            throw new Error("QCP code is not available. Check for the static resource name that should be exactly 'qcpsourcecode'");
        }
    } catch (e) {
        console.error(e);
    }

    return Promise.resolve();
}

async function readQCP(qcpCode) {
    await eval(qcpCode);

    if (window.QCPCode !== undefined) {
        QCP = window.QCPCode;
    } else {
        QCP = globalThis.QCPCode;
    }

    try {
        QCP.test();
    } catch (error) {
        console.error("Error executing QCP.test():", error);
    }
}

The SBQQ__CustomScript__c should contain a minimal script that connects to the Static Resource. This script will handle any initial setup and call the Apex methods needed for the rest of the processing.

Static Resource (JavaScript TXT file)

class QCPCode{
    static test(){
        console.log('Hello from QCP Static Resource');
    }
}

if(typeof window !== 'undefined'){
    window.QCPCode = QCPCode;
}else{
    globalThis.QCPCode = QCPCode;
}

The main logic of the QCP is stored here, including modular code that otherwise would exceed character limits. This allows you to use modern JavaScript coding practices like functions and code separation for better readability.
In this example we are just implementing one simple method that does a simple console.log, but you can basically replicate your existing logic for all the QCP functions such as onBeforeCalculate(), onAfterCalculate(), etc.

Basically, the code needs to be a JS class, that after it's definition will be saved to the window or globalThis context of the browser. This is because it's not possible to use things like localStorage or sessionStorage here.

Apex Handler

@restresource(urlmapping='/qcphandler/*')
global with sharing class CPQ_QCPHandler {

    @HttpGet
    global static String startQcp(){
        String responseString = '';
        try {
            String qcpSourceCode = [SELECT Body FROM StaticResource WHERE Name = 'qcpsourcecode'].Body.toString();
            QCPHandlerResponse response = new QCPHandlerResponse(qcpSourceCode, true, null);
            responseString = JSON.serialize(response);
        } catch (Exception e) {
            System.debug('Exception: ' + e.getMessage());
            QCPHandlerResponse response = new QCPHandlerResponse(null, false, e.getMessage());
            responseString = JSON.serialize(response);
        }
        return responseString;
    }

    private class QCPHandlerResponse{
        public QCPHandlerResponse(String result, Boolean success, String errorMessage){
            this.result = result;
            this.success = success;
            this.errorMessage = errorMessage;
        }
        public String result;
        public Boolean success;
        public String errorMessage;
    }
}

The Apex handler acts as a REST endpoint that the JavaScript can call to fetch or execute resources as needed. This makes integration straightforward and keeps test coverage easy to maintain, as only a single, stable Apex method is used.

In this sample case where we basically only added one single method that does a console.log, this should be the outcome when entering the Quote Line Editor (QLE) and calculating the quote: (Screenshot taken from browser's console)

Here it's a simple boilerplate about what would the code really look like for using this approach to properly use it in real QCP scenarios:

export function onBeforeCalculate(quote, lines, conn) {
    return CPQ.onBeforeCalculate(quote,lines,conn);
}

Benefits of This Approach

Readability and Maintenance: By storing the bulk of the JavaScript in Static Resources, the QCP code becomes significantly more readable and modular. It also facilitates easier long-term maintenance.
Character Limit Removed: The QCP code size is effectively unrestricted since the Static Resource can hold much larger JavaScript files than the text field in Salesforce.
Easy Rollback: During testing and implementation, it is easy to revert to the current setup using Salesforce’s OOB configuration. This ensures minimal disruption during experimentation.
Simplified Test Coverage: Since this solution involves only one Apex class that operates as a REST resource with a single method, the Apex test coverage requirements remain stable. Deploying the QCP code is simplified to deploying a new version of the Static Resource.
Simplified Testing: This approach is more about restructuring the way code is accessed rather than adding new features. Thus, testing focuses on verifying stability rather than introducing new potential bugs, making the process less risky.

Drawbacks

Existing Code Complexity: While this approach addresses the character limit issue, it doesn’t inherently improve the quality of the existing codebase. The same logic and practices (whether well-structured or not) are preserved, meaning any pre-existing messiness or inefficiencies in the code will remain. Further refactoring could be pursued in the future to optimize code quality and structure.

Conclusion

This approach provides an effective workaround to Salesforce CPQ's limitations by leveraging Static Resources and a streamlined Apex handler. It brings significant improvements in readability, scalability, and maintenance without requiring a complete overhaul of existing business logic. Although this solution does not inherently improve the underlying code practices, it sets the stage for easier future optimizations and more robust customizations.

Important🥰

Please like this post and don't forget to make a proper reference to this article if it's useful for you.
You can find more information about me HERE
😃

How create CPF Input Mask on Salesforce Aura Framework

Roger Rosset — Tue, 18 May 2021 17:10:57 +0000

Brazilians systems that are related to person accounts and customer information, always use CPF inputs, and this type of input has it's own definitions. One of them is about the pattern.

When it comes to provide custom masks within Salesforce Aura lightning inputs, everything goes into a dark zone. One often used workaround, is to use custom regex pattern attribute with patternMismatch message:

messageWhenPatternMismatch="CPF Inválido. Por favor utilize o padrão 000.000.000-00"
pattern="[0-9]{3}.[0-9]{3}.[0-9]{3}-[0-9]{2}"

It works, but in terms of UX we can say it is not one of the best solutions possible.

When working with Salesforce, specially Aura and LWC, we are always being careful about limitations, and Shadow Dom, so there is no default way to implement input masks as would be possible using pure JavaScript for example.

The good news is that after a little and simple development using pure JavaScript with Aura peculiarities, you can implement a automatic CPF input mask into your lightning:input field.

First of all, you need to create the following attribute:
<aura:attribute name="cpfValue" type="String" default=""/>

After that, create your lightning:input field that will be used to store the CPF information. The type here will be default, because we need the special characters on this input, otherwise, we would set the type as number, but it's not the case.
<lightning:input aura:id="cpf" label="{!v.cpfLabel}" value="{!v.cpfValue}" onchange="{!c.handleCpfChange}" class="input sfdc_usernameinput sfdc" maxlength="14" required="true"/>

In my case, all the inputs uses a defined label that comes from the design file, but you don't have to worry about this. The attributes that are needed for this implementation to work, is:
-maxlength
-value
-aura:id
-onchange

Now, going to the js controller, we will have the following code:

    handleCpfChange: function (component, event){
        let inputCpf = event.getParam("value");
        component.set("v.cpfValue", inputCpf);
        let size = component.get("v.cpfValue").length;
        if(size === 3 || size === 7){
            component.set("v.cpfValue", inputCpf+'.');
        }
        if(size === 11){
            component.set("v.cpfValue", inputCpf+'-');
        }
    }

What this code is doing, is running every time the value on the cpf field changes, by the onchange attributed that we've set before. Each time it runs, it will save the event value on the inputCpf variable, and set the attribute v.cpfValue with that variable value. Also, each time it will read for the length of the attribute, and when this length reaches 3 or 7, it will understand that it's time to put a dot, and when it reaches a size of 11, it will understand that it's time to put a slash.

So, there you have. A custom made and ready to go CPF input mask. You can also use this logic to develop your own custom masks, the reasoning will be the same.

I hope this be useful!

Roger Rosset

How to Import Salesforce Custom Metadata Records using CSV/JSON

Roger Rosset — Thu, 27 Aug 2020 13:17:10 +0000

Importing Salesforce Custom Metadata Records can be really tricky. Forget about using import wizard or dataloader to do that. These tools are amazing but simply do not support operations on Custom Metadata Records.

If you perform a quick search on Google about this theme, you probably will, at certain point of your search, get into an guide that will tell you to use the Custom Metadata Loader Loader. This tool may be useful if you have just s few records to import, and none of your records has an special character on it, otherwise you certainly will fail after a lot of bugs and errors.

I've developed an class using the official Salesforce Documentation as guidelines to achieve that requirement, and I'll help you to import your Salesforce Custom Metadata Records following steps below:

1:Prepare your Data

You can use any online service that converts csv into json. I recommend the service below:
https://csvjson.com/csv2json

Simply upload your CSV file containing the records you want to upload, click on CONVERT and copy the generated JSON.

1.1:Make your JSON inline

What we have to do next, is to parse that JSON into a single line, you can easily do that accessing the following link:
https://jsonformatter.curiousconcept.com/

Simply paste your JSON, select COMPACT on the JSON Template Option and click PROCESS. Copy the generated compact JSON and keep that safe for later steps.

2:Create the mdtImport Apex Class using the code below



//author: Roger Rosset
//description: Upload custom metadata records using CSV/JSON

public class mdtImport  {    
    public static void insertMetadata(String metaDataTypeName, String jsonString){
        try{
            Integer count = 1;
            Metadata.DeployContainer mdContainer = new Metadata.DeployContainer();
            JSONCsvTemplate csv = (JSONCsvTemplate)JSON.deserialize(jsonStringMdt(jsonString), JSONCsvTemplate.class);
            for(JSONCsvTemplate.mdtRecords item : csv.data.mdtRecordsList){
            //Sets the custom metadata type you'll insert your records on

            //If you're using namespaces on your org set it here
            String nameSpacePrefix ='';                                     
            Metadata.CustomMetadata rec =  new Metadata.CustomMetadata();
            String label = 'Record '+count;                      
            rec.fullName = nameSpacePrefix+metaDataTypeName+'.'+label;
            rec.label = label; 

            //Sets the custom metadata custom fields to be inserted
            /*
            * Use the template below to setup any fields you want to:
            *    
            Metadata.CustomMetadataValue fieldX = new Metadata.CustomMetadataValue();     //New instance
            fieldX.field = 'Custom_Field_Name__c';              //Custom Metadata Field you want to fill
            field1.value = item.JSON_Matching_Key_Value;        //The matching key value on the wrapper
            rec.values.add(fieldX);                             //adds the value and the matching field
            *
            */

            Metadata.CustomMetadataValue field1 = new Metadata.CustomMetadataValue();
            field1.field = 'SubGroup__c';
            field1.value = item.SubGroup;
            rec.values.add(field1);            
            Metadata.CustomMetadataValue field2 = new Metadata.CustomMetadataValue();
            field2.field = 'Description__c';
            field2.value = item.Description;
            rec.values.add(field2);                       
            Metadata.CustomMetadataValue field3 = new Metadata.CustomMetadataValue();
            field3.field = 'keyId__c';
            field3.value = item.keyId;
            rec.values.add(field3);                        
            mdContainer.addMetadata(rec);  
            count++;
    } 
            Id jobId = Metadata.Operations.enqueueDeployment(mdContainer, null);
            system.debug('Container>>'+mdContainer);
            system.debug('Id>>'+jobId);
            return;
        }

            catch(Exception ex){             
            system.debug('Error on insert');
            system.debug('Error:'+ex.getMessage());
        }

    }
    private static String jsonStringMdt(String jsonString){
        String resultJson = '{"data":{"mdtRecordsList":'+jsonString+'}}';
        return resultJson;
    }

    private class JSONCsvTemplate{
        private class Data{
            private List<mdtRecords> mdtRecordsList;
    }
        private class mdtRecords{
            private String keyId;
            private String SubGroup;
            private String Description;
        }
        private Data data;
    }
}

As you can see you can execute the methods insertMetaData passing the metaDataTypeName and jsonString as attributes, where metaDataTypeName is the API Name of your Custom Metadata Type (ending with __mdt) and jsonString is your inline JSON that you've copied on the step 1.1, But first you have to adjust the attributes on the JSONCsvTemplate>mdtRecords to fit like your csv database.

3:Adjust the Wrapper class to your requirements



    private class JSONCsvTemplate{
        private class Data{
            private List<mdtRecords> mdtRecordsList;
    }
        private class mdtRecords{
            private String keyId;
            private String SubGroup;
            private String Description;
        }
        private Data data;
    }
}

As you can see above, we are uploading records that has 3 columns:
-keyId
-SubGroup
-Description

These values matches exactly as my csv database headers, and you can add, delete or modify the template to meet your criteria. Keep in mind that your csv database must fit the wrapper class, not only about the field names, but also the primitive data types (Integer, String, Etc.)

Csv Database used:

4:Execute the method

After following all the previous steps and making sure you've created and saved the class on your Salesforce Org, all you have to do next is to execute the insertMetadata
Get your Custom Metadata Type API Name (ending with __mdt) and your JSON String generated in the step 1.1, and execute the method on the Apex Anonymous Window inside the developer console (CTRL+E)

Keep in mind that your JSON String may be really big, and to avoid problems, it's highly recommended you to place the method template and quotes before pasting the "super string" on the parameter inside the method execution, as below:

After preparing the the method, simply paste your big JSON String inside the quotes, and click execute.

If you have checked the Open Log option, you should be taken to the log of this execution, where you can find the ID of your enqueued deploy, and check it's status on the deployment status of Salesforce on:
SETUP > DEPLOY > DEPLOYMENT STATUS

Attention

I've already tested the deploy of 600 records per execution, try to respect that limit to avoid errors.

Feel free to modify the code as you need to, keeping the credits if possible.

Hope it be useful.

Regards,
Roger Rosset

Based documentation:
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_class_Metadata_Operations.htm