Catalytic is now PagerDuty Workflow Automation

Excel: Extract range

🕙  3 minute read

Use this action to save data from a cell or range in an Excel spreadsheet as a data table or JSON object.

screen readers look here

To save an entire spreadsheet to a table, use the Excel: Save spreadsheet to table action.

How to configure this action

With this action, you choose an Excel cell or cell range and extract the data to your preferred format. The action is easy to configure and uses familiar Excel notations.

The cell or range reference should use standard Excel notation. For example:

  • The range B1:C5 extracts the first 5 rows of second and third column.
  • The cell reference D2 extracts just a single cell’s data into a text field.

The following range types are not supported, A:B, A, 1, 1:2. Your ranges must be distinct cells, rather than entire rows or columns such as.

The action outputs in JSON or a data table. See What will this output? for examples of both.

Fields for this action

  • Spreadsheet file

    • Choose the Excel file you want to extract from. Use a field reference to a file uploaded in a prior task or instance.
  • Sheet

    • The worksheet within the spreadsheet that contains the data. Enter the sheet name or the sheet order.
      • The default Excel sheet names are “Sheet1”, “Sheet2”, etc.
      • Enter a number to select by worksheet order, “1”, “2”, “3”, etc.
  • Header row number

    • Enter the row number that contains the names of the columns. Leave blank if the file does not have a header row.
      • Enter a header row number, and when the Output type is TABLE, the table’s header row are the column names.
      • Leave blank, and when the Output type is TABLE, the table’s header row are Excel style columns, A, B, C, etc.
  • Range

    • Specify the cell or range to extract, such as A4, B1:C5, A2:C2, etc.
    • screen readers look here
  • Output type

    • Select whether to save the range to a Table or JSON object.
      • Enter JSON to save the range to a JSON object.
      • Enter TABLE or leave blank to save the range to a data table.
      • If the range is a single cell, such as A3, the output type is always a string saved to a field.
  • Output field name

    • The name of the field in which the result should be saved. The step name is used by default. The step name is used by default.This new field will store the result as a JSON object or text field.

What will this output?

The output of this action is the data extracted from the Excel file. The format of the output depends on the configuration:

If a range was used, the output is a JSON object or Table depending on the chosen output type.

Example JSON object output
screen readers look here

As a JSON object, the output for B1:C5 looks like this:

[
  ["Qty", "Price"],
  ["10", "1.25"],
  ["5", "1.50"],
  ["7", "1"],
  ["4", "1.75"]
]

As a JSON object, the output for A2:C2 looks like this:

[
  ["Mango", "10", "1.25"]
]

The output for A4 would not be a JSON object, and would instead output as a string to a single field.

Example data table output
screen readers look here

The output for B1:C5, with a header row number selected, looks like this:

screen readers look here

The output for B1:C5, without a header row number, looks like this:

screen readers look here

If a single cell reference was used, the output is the value in a text field.

The name of the output field for this action will be the Output field name defined during configuration.

Output fields for this action

  • Output field name

    • The JSON object, data table, or string extract from the spreadsheet.

Sorry about that. What was the most unhelpful part?









Thanks for your feedback

We update the Help Center daily, so expect changes soon.

Link Copied

Paste this URL anywhere to link straight to the section.