The Challenge: When APIs Cost Too Much

Picture this: you need to import data from a third-party system, but their API costs $25,000 annually. Meanwhile, they'll let you export the same data for free as a file. What option would you choose?

This exact scenario recently came up with a client using Concur. While we'd normally build a sleek API integration (and trust us, we love integrations at Semaphore Partners), the astronomical API pricing forced us to get creative. The client could only drop files on their MID Server, so we needed a solution that worked within those constraints.

A Bit of History

Back in 2014, there was a fantastic app called Remote File Importer on ServiceNow Share (thank you to SNC Guru Mark Stanger!). It solved this exact problem, but ServiceNow Share is no longer active, and finding those old update sets is like searching for buried treasure.

The good news? ServiceNow's newer capabilities make this easier than ever.

Our Solution: A Two-Step Flow

We created a custom Flow Action that grabs files from the MID Server and attaches them to a Data Source in ServiceNow. Once attached, the Data Source treats it like any manually uploaded file and imports it seamlessly.

Here's how it works:

Step 1: MID Server Script

The first step runs a script on the MID Server to locate and read the target file. This step handles all the file system operations and returns the file's base64 encoded content to ServiceNow.

(function execute(inputs, outputs) {
  function getMimeType(extension) {
    switch (extension) {
      case 'jpg':
      case 'jpeg':
        return 'image/jpeg';
      case 'png':
        return 'image/png';
      case 'gif':
        return 'image/gif';
      case 'xlsx':
        return 'application/xlsx';
      default:
        return 'application/octet-stream';
    }
  }
  
  //const file = new Packages.java.io.File(inputs.filePath);
  const file = new Packages.java.io.File(
    inputs.file_path+"\\"+inputs.file_name
  );

  var result = [];

  // Read file and encode to base64
  var fileInputStream = null;
  var base64Data = "";

  var skipReason = "";
  var fileName = "";
  var fileSize = 0;
  var fileExtension = "";
  var fileMimeType = "";
  var fileLastModified = "";
  var base64Data = "";

  // Get basic file info even if we skip it
  try {
    fileName = file.getName() + "";
    fileSize = file.length();
    fileExtension =
      fileName.indexOf(".") !== -1
        ? fileName.split(".").pop().toLowerCase()
        : "";
    fileMimeType = getMimeType(fileExtension);
    fileLastModified = file.lastModified();
  } catch (infoError) {
    skipReason = "Unable to read file information";
  }

  try {
    fileInputStream = new Packages.java.io.FileInputStream(file);
    const bytes = new Packages.java.lang.reflect.Array.newInstance(
      Packages.java.lang.Byte.TYPE,
      fileSize
    );

    fileInputStream.read(bytes);
    base64Data = Packages.java.util.Base64.getEncoder().encodeToString(bytes);
  } finally {
    if (fileInputStream) {
      fileInputStream.close();
    }
  }
   outputs.result = JSON.stringify({
      fileName: fileName,
      fileSize: fileSize,
      fileExtension: fileExtension,
      fileMimeType: fileMimeType,
      fileLastModified: fileLastModified,
      base64Data: base64Data
    });
})(inputs, outputs);

Step 2: Server Script

The second step takes the file content from Step 1 and creates an attachment on your Data Source using server-side code. This transforms the raw file data into a properly formatted ServiceNow attachment.

(function execute(inputs, outputs) {
  const fileInfo = JSON.parse(inputs.attachment);
  const data_source = inputs.data_source;
  outputs.file_name_2 = fileInfo.fileName;

  const attachment = new GlideSysAttachment();

  attachment.deleteAll(data_source);

  var binData = GlideStringUtil.base64DecodeAsBytes(fileInfo.base64Data);

  const attachment_id = attachment.write(
    data_source,
    fileInfo.fileName,
    fileInfo.fileMimeType,
    binData
  );
  outputs.attachment_id = attachment_id;
})(inputs, outputs);

Bonus: Tie it to a Data Source

Want to take this further? You can integrate this flow directly with your data import process using this snippet as a Scheduled Import Set pre-script:

The flow automatically detects which MID Server your Data Source is configured to use, ensuring it works correctly across different environments.

(function() {

    try {
        var inputs = {};

        var data_source = new GlideRecord("sys_data_source");
        data_source.get(DATA_SOURCE_SYS_ID);

        inputs['file_name'] = "my_report.xlsx"; // String 
        inputs['file_path'] = "\\\\myFileshare\\ServiceNow"; // String 
        inputs['data_source'] = data_source; // GlideRecord of table: sys_data_source 

        var result = sn_fd.FlowAPI.getRunner().action('global.import_excel_from_mid_server').inForeground().withInputs(inputs).run();
        var outputs = result.getOutputs();

        // Current action has no outputs defined.		
    } catch (ex) {
        var message = ex.getMessage();
        gs.error(message);
    }
})();

Why This Matters

This approach gives you the best of both worlds: the cost savings of file-based imports with the automation and reliability of a proper integration. Plus, it's built using ServiceNow's native Flow Designer, making it maintainable and upgradeable.

Have you run into similar API pricing challenges? We'd love to hear how you've solved them. Sometimes the most elegant solution isn't the most obvious one.

Need help building custom integrations or solving tricky ServiceNow challenges? Semaphore Partners specializes in creative solutions that work within real-world constraints.