Update test data table rows – Virtuoso

The Update/setTestData extension updates one or more rows in a Virtuoso test data table from inside a journey.

This is useful when a journey needs to refresh reusable test data, change a customer or status value, or prepare rows for later data-driven steps while the journey is running.

Parameters:

env required, a string that selects the Virtuoso API environment to use: PRODUCTION, UK, US, or APP2;
token required, a string containing the Virtuoso API token. Store this as an environment variable where possible rather than typing the token directly in the journey;
table required, a string containing the exact name of the test data table to update;
data required, a JSON string containing the column names and values to write. The JSON keys must match column names in the selected test data table;
row required, a row number string such as "0", or a JSON array string such as "[0,1,2]" when the same data should be written to multiple rows;
projectId required, a string or number containing the Virtuoso project ID where the data table exists.

Note: Row numbers start from 0. For example, the first row is 0, the second row is 1, and so on. The data value must be valid JSON, and each JSON key must match an existing column name in the test data table.

How to apply this to your journey

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

Note: Script inputs are passed as text in Virtuoso. Pass data as a JSON string. For multiple rows, pass row as a JSON array string, for example "[0,1,2]". The extension returns the status text Table Updated when the update completes.

To update a single row in a test data table:

execute "Update/setTestData" using "PRODUCTION" as env, "$setTestDataToken" as token, "CustomerData" as table, '{"CustomerName":"John Smith","Status":"Active"}' as data, "0" as row, "$projectId" as projectId returning $updateStatus

To update multiple rows with the same values:

execute "Update/setTestData" using "PRODUCTION" as env, "$setTestDataToken" as token, "CustomerData" as table, '{"Status":"Inactive"}' as data, "[0,1,2]" as row, "$projectId" as projectId returning $updateStatus

You can also use Virtuoso variables to make the same step reusable across different environments, tables, rows, or projects:

execute "Update/setTestData" using "$environment" as env, "$setTestDataToken" as token, "$tableName" as table, "$jsonData" as data, "$rowIndex" as row, "$projectId" as projectId returning $updateStatus

Example setup using variables before calling the extension:

store value "CustomerData" in $tableName
store value '{"CustomerName":"John Smith","Status":"Active"}' in $jsonData
store value "0" in $rowIndex
execute "Update/setTestData" using "$environment" as env, "$setTestDataToken" as token, "$tableName" as table, "$jsonData" as data, "$rowIndex" as row, "$projectId" as projectId returning $updateStatus

For example, if your table has columns called CustomerName and Status, the JSON should use the same names:

{"CustomerName":"John Smith","Status":"Active"}

Example output stored in $updateStatus:

Table Updated

This extension requires the following resources:

https://cdnjs.cloudflare.com/ajax/libs/axios/0.21.1/axios.min.js

The extension should be configured as:

Run asynchronously: Yes
Scope: Global

Limitation: This extension updates rows by row number after fetching the selected table and its current values through the Virtuoso API. The row must already exist, the table name must match exactly, and every JSON key in data must match an existing table column. Invalid JSON in data or row, expired or insufficient API token access, API validation errors, rate limits, network failures, CORS or browser security policy restrictions, or a blocked axios CDN resource can fail the step. Because the implementation uses axios.get and axios.put without a custom request timeout, retry, or rollback mechanism, partial intent mistakes such as updating the wrong row number are not automatically reversed by the extension. Async extension scripts must complete within Virtuoso's documented 120-second maximum execution window. Cross-browser note: This extension does not intentionally inspect or modify the page DOM; it depends on standard JavaScript, the browser-compatible axios resource, and Virtuoso API network access. Validate it in each browser/device configuration used by your plans when the execution environment, proxy, content security policy, or remote grid may affect external resource loading or API requests.

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: 15/09/2023

Resources:

https://cdnjs.cloudflare.com/ajax/libs/axios/0.21.1/axios.min.js

  
const endpoints = {
  PRODUCTION: "https://api.virtuoso.qa/api",
  US: "https://api-US.virtuoso.qa/api",
  UK: "https://api-UK.virtuoso.qa/api",
  APP2: "https://api-app2.virtuoso.qa/api"
};

const BASE_URL = endpoints[env];

if (!BASE_URL) {
    doneError("Invalid environment " + env + ". Please use PRODUCTION, US, UK, or APP2");
}

async function getTable() {
    const endpoint = `${BASE_URL}/projects/${projectId}/dataTables?envelope=false&token=${token}`;
    const dataTables = await axios.get(endpoint)
        .catch(error => {
            doneError('Error fetching data table id: ' + error);
        });
    const dataTable = Object.values(dataTables.data).find(value => value.name === table);
    if (!dataTable) return doneError(`Table ${table} not found`);
    return dataTable;
}

function getColumnId(table, columnName) {
    const column = Object.values(table.attributes).find(attribute => attribute.name === columnName);
    if (!column) {
        doneError(`Could not find column ${columnName} in table`);
    }
    return column.id;
}

function getColumnName(table, columnId) {
    const column = Object.values(table.attributes).find(attribute => attribute.id === columnId);
    if (!column) {
        doneError(`Could not find column with ID ${columnId} in table`);
    }
    return column.name;
}

async function getTableValues(tableId) {
    const endpoint = `${BASE_URL}/testdata/tables/${tableId}/values?envelope=false&token=${token}`;
    const { data: tableValues } = await axios.get(endpoint)
        .catch(error => {
            doneError('Error fetching data table values: ' + error);
        });
    return tableValues;
}

async function updateTable(tableId, newValues, modifiedDate) {
    const endpointUrl = `${BASE_URL}/testdata/tables/${tableId}/values?envelope=false&token=${token}`;
    const requestData = {
        modifiedDate: modifiedDate,
        newTestDataValues: newValues
    };
    await axios.put(endpointUrl, requestData)
        .catch(error => {
            doneError('Error updating table');
        });
}

function updateTableValues(table, rows, oldValues, data) {
    let dataObj = {};
    try {
        dataObj = JSON.parse(data);
    } catch (e) {
        doneError("Could not parse data, make sure that it's correctly formatted in JSON");
    }
    for (const [key, value] of Object.entries(dataObj)) {
        const columnId = getColumnId(table, key);
        try {
            rows.forEach(row => {
                if (!oldValues[row]) oldValues[row] = {};
                oldValues[row][columnId] = value;
            });
        } catch (e) {
            doneError(`Error updating value ${key} in row ${row} of table`);
        }
    }
    return oldValues;
}

async function setTableData(data, rows) {
    const tableObj = await getTable();
    const oldValues = await getTableValues(tableObj.id);

    // Convert single row input (string or number) to an array with one element
    const rowInput = typeof rows === 'string' || typeof rows === 'number' ? [rows] : rows;

    const newValues = updateTableValues(tableObj, rowInput, oldValues, data);
    await updateTable(tableObj.id, newValues, tableObj.modifiedDate);
    done("Table Updated");
}

const rowInput = typeof row === 'string' ? JSON.parse(row) : [row];
setTableData(data, rowInput);

How to apply this to your journey

Related articles