Skip to main content

Compare two XLS spreadsheets

Suhas Reddy
Updated

The compareSpreadSheets extension downloads two Excel spreadsheet files, parses a selected worksheet from each file, compares their populated cell addresses and cell values, and returns a list of differences.

This is useful when a journey needs to verify that an exported spreadsheet matches a known master file, such as validating generated reports, downloaded reconciliation files, or Excel-based data outputs after an application action.

Parameters:

  • fileOneUrl required, the URL string of the master Excel file to download and compare from;
  • fileTwoUrl required, the URL string of the second Excel file to compare against the master file;
  • fileOneSheetName optional, the intended sheet name for the master file. In the supplied source code, this input is not currently applied because the script sets the sheet name with 'Data' || fileTwoSheetName, which always resolves to Data;
  • fileTwoSheetName optional, the intended sheet name for the second file. In the supplied source code, this input is not currently applied because the script sets the sheet name with 'Data' || fileOneSheetName, which always resolves to Data;

Note: The supplied implementation compares the worksheet named Data in both workbooks. Although fileOneSheetName and fileTwoSheetName are configured as inputs to match the described extension interface, the current source code does not use those values at runtime.

How to apply this to your journey

Use the extension in a journey by calling compareSpreadSheets with the execute command. Pass each value to the matching extension input using as inputName.

Note: This extension is asynchronous. It downloads both spreadsheet files, parses them with the SheetJS XLSX library, and returns through done(). If download, parsing, or sheet lookup fails, the step fails through doneError().

execute "compareSpreadSheets" using "https://example.com/master.xlsx" as fileOneUrl, "https://example.com/export.xlsx" as fileTwoUrl returning $result
execute "compareSpreadSheets" using "https://example.com/master.xlsx" as fileOneUrl, "https://example.com/export.xlsx" as fileTwoUrl, "Data" as fileOneSheetName, "Data" as fileTwoSheetName returning $result

You can store file URLs in variables first, then pass those variables into the extension.

execute "compareSpreadSheets" using "$masterFileUrl" as fileOneUrl, "$exportedFileUrl" as fileTwoUrl returning $result
store value "https://example.com/master.xlsx" in $masterFileUrl
store value "https://example.com/export.xlsx" in $exportedFileUrl
execute "compareSpreadSheets" using "$masterFileUrl" as fileOneUrl, "$exportedFileUrl" as fileTwoUrl returning $result

When the spreadsheets match, the extension returns an empty array. When differences are found, it returns an array of message strings describing missing cells or value differences.

[
  "fileTwoFile cell B2 with value: 200 differs from fileOne with value: 100",
  "fileTwoFile as more cells than fileOne => cell C4 with value: Approved"
]

This extension requires the following resources:

The extension should be configured as:

  • Run asynchronously: Yes
  • Scope: Global

Limitation: This extension depends on browser-based fetch(), the external SheetJS XLSX resource, and spreadsheet URLs that are reachable from the journey execution environment. CORS rules, authentication, expiring download links, proxy restrictions, content security policy, file size, workbook corruption, unsupported formats, or resource-loading failure can prevent comparison. The current source compares only the worksheet named Data; the supplied fileOneSheetName and fileTwoSheetName inputs are not actually used because 'Data' || fileTwoSheetName and 'Data' || fileOneSheetName always evaluate to Data. The comparison is cell-address based and checks raw cell v values, not formulas, formatting, styles, merged cells, hidden rows, charts, comments, metadata, row order semantics, number/date display formatting, locale-specific rendering, or workbook-level settings. Empty cells and SheetJS worksheet metadata keys starting with ! are skipped. Large spreadsheets may take longer to download and parse and must complete within Virtuoso's documented 120-second maximum execution window for asynchronous extension scripts. Cross-browser note: because this extension uses browser networking, ArrayBuffer handling, and the SheetJS browser library, behavior can differ across Chromium, Firefox, Safari, Edge, iOS, Android, and remote-grid executions, especially for downloads, authentication, memory limits, and network restrictions. Validate it in each browser/device configuration used by your plan.

Add the extension to your Virtuoso instance

Select the domain that matches your Virtuoso account.

Add extension to Virtuoso - https://app.virtuoso.qa/ → Add extension to Virtuoso Free Trial - https://app2.virtuoso.qa/ →
Add extension to Virtuoso US - https://app-us.virtuoso.qa/ → Add extension to Virtuoso UK - https://app-uk.virtuoso.qa/ →
View source

Last updated: 29/04/2025

Resources:

// Last updated: 29/04/2025, 09:03:03 UTC
const errors = [];

async function getFile(fileUrl) {
  const data = await (await fetch(fileUrl)).arrayBuffer();
  return data;
}

function checkCellCount(fileOneData, fileData) {
  if (!fileOneData || !fileData) {
    throw new Error('fileOne or file data is undefined/null');
  }

  const fileOneKeys = Object.keys(fileOneData);
  const fileKeys = Object.keys(fileData);

  for (const key of fileOneKeys) {
    if (!fileOneData[key]) {
      throw new Error('Missing data for key: ' + key);
    }

    if (key.startsWith("!")) continue;

    if (!fileKeys.find((element) => key == element)) {
      console.log("fileOne " + key);
      console.log(fileOneData[key]["v"]);
      errors.push(`fileOne as more cells than fileTwoFile => cell ${key} with value: ${fileOneData[key]["v"]}`);
    }
  }

  for (const key of fileKeys) {
    if (!fileData[key]) {
      throw new Error('Missing data for key: ' + key);
    }

    if (key.startsWith("!")) continue;

    if (!fileOneKeys.find((element) => key == element)) {
      errors.push(`fileTwoFile as more cells than fileOne => cell ${key} with value: ${fileData[key]["v"]}`);
    }
  }
}

function checkCellContent(fileOneData, fileData) {
  if (!fileOneData || !fileData) {
    throw new Error('fileOne or file data is undefined/null');
  }

  const fileOneKeys = Object.keys(fileOneData);

  for (const key of fileOneKeys) {
    if (!fileOneData[key]) {
      throw new Error('Missing data for key: ' + key);
    }

    if (!fileData[key]) {
      throw new Error('Missing data for key: ' + key);
    }

    if (key.startsWith("!")) continue;

    if (fileData[key]["v"] != fileOneData[key]["v"]) {
      errors.push(`fileTwoFile cell ${key} with value: ${fileData[key]["v"]} differs from fileOne with value: ${fileOneData[key]["v"]}`);
    }
  }
}

async function main() {
  const fileOneFile = await getFile(fileOneUrl);
  const fileOne = XLSX.read(fileOneFile, { type: 'buffer' });
  if (!fileOne) {
    throw new Error('Failed to parse fileOne file');
  }

  const fileOneSheetName = 'Data' || fileTwoSheetName;
  const fileOneSheet = fileOne.Sheets[fileOneSheetName];
  if (!fileOneSheet) {
    throw new Error('fileOne missing ' + fileOneSheetName + ' sheet');
  }

  const fileOneData = fileOneSheet;

  const fileTwoFile = await getFile(fileTwoUrl);
  const fileTwo = XLSX.read(fileTwoFile, { type: 'buffer' });
  if (!fileTwo) {
    throw new Error('Failed to parse fileTwo file');
  }
  const fileTwoSheetName = 'Data' || fileOneSheetName ;
  const fileData = fileTwo.Sheets[fileTwoSheetName];
  if (!fileData) {
    throw new Error('File missing Data sheet');
  }

  checkCellCount(fileOneData, fileData);
  checkCellContent(fileOneData, fileData);

  return errors;
}

main().then(done).catch(doneError);

Comments

0 comments

Please sign in to leave a comment.