PMG Documentation

# Excel

The Excel, or 'Spreadsheet' actions allow for the management of Microsoft Excel 'XLS' documents. The actions use file variables to read Excel Data and manage Excel worksheet data.

# Create Worksheet

excel.create-worksheet

The Create Worksheet action inserts a newly named worksheet into an existing spreadsheet.

The action has the following outcomes.

Successful - the action ran without error

Unsuccessful - the action encountered some error

The action has the following properties.

Source Excel File Variable - specifies the file variable which contains the source spreadsheet

Name of new Worksheet - the name to use for the new worksheet to be added

Destination Excel File Variable - specifies the file variable to store the updated spreadsheet

# Edit Range

excel.edit-range

The Edit Range action allows for the setting of data into an Excel range on a specified worksheet.

The action has the following outcomes.

Successful - the action ran without error

Unsuccessful - the action encountered some error

The action has the following properties.

Excel File Variable - specifies the file variable which contains the source spreadsheet

Sheet Name - specifies the name of the target worksheet

Cell Range - The 'Excel' range to insert the new data. i.e.

A1

Range Values Object String - a specially formatted JSON document with the data to be inserted into the range

Example range documents are provided below.

URL example:

[{
    "value": "Company Website",
    "url": "http://www.company.com",
    "type": "URL"
}]

Formula example:

[{
    "value": "IFERROR(G4/H4,0)",
    "type": "formula"
}]

Numeric example:

[{
    "value": "2",
    "type": "numeric"
}]

String example:

[{
    "value": "Some Name",
    "type": "string"
}]

Formatted numeric example with workflow value:

[{
    "value": "X{{XMLItr_RangeVar.value}}",
    "type": "numeric",
    "format": "$#,##0_);($#,##0)"
}]

Formatted numeric example with workflow value:

[{
    "value": "X{{XMLItr_RangeVar.value}}",
    "type": "numeric",
    "format": "0.00%"
}]

Destination File Variable - specifies the file variable to store the updated spreadsheet

# Get Data from Range

excel.get-data-range

The Get Data from Range action allows for reading of a range of data from a specified worksheet.

The action has the following outcomes.

Successful - the action ran without error

Unsuccessful - the action encountered some error

The action has the following properties.

Excel Workbook File Variable - specifies the file variable which contains the source spreadsheet

Sheet Name - specifies the name of the source worksheet

Cell Range - The Excel syntax range for the source data. An example cell range is below.

G14:G14

The data returned will be in an array like below.

[{
    "value": "1866032",
    "type": "value",
    "format": null,
    "url": null
}]

String Variable for Range Data - specifies the target value for the returned range data

Status Variable - an optional string variable which will store any error or status messages from the activity

# Get Value from Cell

excel.get-cell-value

The Get Value from Cell action allows for reading of data from a specified worksheet cell.

The action has the following outcomes.

Successful - the action ran without error

Unsuccessful - the action encountered some error

The action has the following properties.

Excel Workbook File Variable - specifies the file variable which contains the source spreadsheet

Sheet Name - specifies the name of the source worksheet

Cell Reference - The Excel syntax of the cell for the source data. An example is below.

G14

Message Variable - an optional string variable which will store any error or status messages from the activity

Variable to store result - The target variable to store the value from the cell

← XML Editor →