Get and parse XLS sheets

The getAndParseXLS extension downloads an XLS or XLSX spreadsheet from a provided URL and parses one worksheet into journey-friendly data. It can return the selected sheet as JSON rows for assertions, or as an HTML table string that can be rendered later with a DOM-writing extension such as WriteHTML.

This is useful when a journey needs to verify generated spreadsheet exports, downloaded reports, customer data files, or tabular content that is easier to validate after it has been converted into structured rows.

Parameters:

• file required, a string URL pointing to the XLS or XLSX file to download. The URL must be reachable from the browser execution context and must allow the request through CORS and network rules;
• sheetName optional, a string containing the exact worksheet name to parse. If it is not provided, the extension parses the first worksheet in the workbook;
• toHtml optional, a value that switches the output from JSON rows to an HTML table string when it is truthy. Use true or "true" when HTML output is required.

How to apply this to your journey

Use the extension in a journey by calling getAndParseXLS with the execute command. Pass each value to the matching extension input using as inputName, and store the returned parsed data in a variable for later assertions.

navigate to "https://s3-eu-west-1.amazonaws.com/virtuoso-downloaded-files/test.html"
execute "getAndParseXLS" using "https://example.com/report.xlsx" as file returning $json
assert $json[0].Name equals "John"

navigate to "https://s3-eu-west-1.amazonaws.com/virtuoso-downloaded-files/test.html"
execute "getAndParseXLS" using "https://example.com/report.xlsx" as file and "another-sheet" as sheetName returning $json
assert $json[3].Value equals "100"

You can also store the file URL, sheet name, and HTML mode in variables before calling the extension. This is useful when the same exported spreadsheet URL is reused across several assertions or when the sheet name is created earlier in the journey.

store value "https://example.com/report.xlsx" in $xlsFileUrl
store value "another-sheet" in $sheetName
execute "getAndParseXLS" using $xlsFileUrl as file and $sheetName as sheetName returning $json
assert $json[0].Name equals "John"

store value "https://example.com/report.xlsx" in $xlsFileUrl
store value "sheetName" in $sheetName
store value "true" in $toHtml
execute "getAndParseXLS" using $xlsFileUrl as file and $sheetName as sheetName and $toHtml as toHtml returning $html
execute "WriteHTML" using $html as content
look for "John"

Example JSON output when toHtml is not provided:

[
  {
    "Name": "John",
    "Value": "100"
  },
  {
    "Name": "Jane",
    "Value": "200"
  }
]

Example HTML output when toHtml is provided as a truthy value:

<table>
  <tr>
    <td>Name</td>
    <td>Value</td>
  </tr>
  <tr>
    <td>John</td>
    <td>100</td>
  </tr>
</table>

This extension requires the following resources:

• https://cdnjs.cloudflare.com/ajax/libs/axios/0.19.2/axios.min.js 
• https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js 

The extension should be configured as:

• Run asynchronously: Yes
• Scope: Global

Add the extension to your Virtuoso instance

Select the domain that matches your Virtuoso account.

// Last updated: 26/01/2023, 13:42:33 UTC
// Resources:
//   https://cdnjs.cloudflare.com/ajax/libs/axios/0.19.2/axios.min.js
//   https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js

async function getAndParseXLS(file, sheetName, toHtml = false) {
  const { data } = await axios.get(file, { responseType: 'blob' })

  return new Promise((resolve) => {
    const reader = new FileReader()
    reader.onload = function () {
      const fileData = reader.result
      const wb = XLSX.read(fileData, { type: 'binary' })

      // setting this to true will output HTML instead of JSON
      if (toHtml) {
        resolve(XLSX.utils.sheet_to_html(wb.Sheets[sheetName || wb.SheetNames[0]]))
        return
      }
      
      resolve(XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName || wb.SheetNames[0]]))
    }
    reader.readAsBinaryString(data)
  })
}

getAndParseXLS(file, sheetName, toHtml).then(done).catch(doneError)