Link Menu Expand (external link) Document Search Copy Copied
This website is for Zenphi public documentation.

List Rows

Definition

The List Rows action in Smartsheet allows you to retrieve a specific range of rows from a designated sheet within your Smartsheet account. This action is essential for workflows that need to process batches of data, migrate records, or generate reports based on sheet content.

Key capabilities include:

  • Targeting a specific sheet to extract data from.
  • Defining a precise range of rows to retrieve by specifying start and end row numbers.
  • Returning data in a raw JSON format that can be parsed for complex data manipulation.

This action serves as a critical entry point for reading Smartsheet data, allowing you to bring sheet information into your automation flow for further processing.



Inputs

1. Connection

  • Purpose: Establishes the link between Zenphi and your Smartsheet account.
  • Practical Guidance: Select an existing connection from the dropdown list. If you haven’t connected yet, click the + icon to authorize Zenphi to access your Smartsheet data.
  • Use Case Context: Ensures the action has permission to read the specific sheets in your corporate account.

2. Sheet Id

  • Purpose: Identifies the specific sheet from which you want to retrieve rows.
  • Practical Guidance: Select the desired sheet directly from the dropdown menu.
  • Use Case Context: Tells Zenphi exactly which “Project Tracker” or “Budget” sheet to pull data from.

3. Row This section allows you to define the specific range of rows you wish to retrieve.

From Row Number:

  • Purpose: Specifies the starting row number for your data retrieval.
  • Practical Guidance: Enter the visual row number where you want to begin reading (e.g., 1 to start from the top).

To Row Number:

  • Purpose: Specifies the ending row number for your data retrieval.
  • Practical Guidance: Enter the row number of the last row you want to include. To ensure you get all rows, enter a number larger than your actual row count (e.g., 10000). The action will return all available rows up to that limit without throwing an error.



Outputs

When you retrieve a row from Smartsheet, the output does not automatically display the fields as structured objects. Instead, the action returns the Result as raw JSON text.

You must parse this JSON on your end. The most straightforward way to do this is by passing the Result into a Convert JSON to Object action.


How Smartsheet Row Data Is Structured

Each retrieved row is returned as an object that includes:

  • id (The system Row ID)
  • rowNumber
  • Only the fields that contain values

Fields are represented by their column IDs, not their column names. For example, a single filled field may look like this:

"360258290274180": "Task 2"

Notice that:

  • The key is the column ID.
  • The column name does not appear in the output.


Example 1 – Row with 3 Filled Fields

If a sheet contains hundreds of columns but only 3 are filled, the output object would look like this:

{
  "id": 6197623273492356,
  "rowNumber": 1,
  "360258290274180": "Task 1",
  "4863857917644676": "Details 1",
  "2612058103959428": "diana@demo.mbfcorp.com"
}

Only non-empty fields are included.


Example 2 – Multiple Rows

If another row only contains the email field, the output would be:

[
  {
    "id": 6197623273492356,
    "rowNumber": 1,
    "360258290274180": "Task 1",
    "4863857917644676": "Details 1",
    "2612058103959428": "diana@demo.mbfcorp.com"
  },
  {
    "id": 6197623273492356,
    "rowNumber": 2,
    "2612058103959428": "diana@demo.mbfcorp.com"
  }
]

Again, only populated fields are returned.


Attachments

If a row includes an attachment, it will appear like this:

{
  "FileName": "adminConsentRequestDetails.png",
  "ContentType": "image/png",
  "Size": 197,
  "payload": "urn:file:8fbd52ff886945779a295ea344f01b4a:70479dfeab0e4731aa758fd3e2262f68"
}

To access the file:

  1. Pass the payload value to the Find Zenphi File action.
  2. Use the returned file from that action in your next step.


Important Note About Parsing

To correctly parse the output using the Convert JSON to Object action, you must:

  1. Retrieve a sample row where all fields are filled.
  2. Use that full row’s JSON in the Schema > Sample JSON Payload section.

This ensures that all possible fields are generated in the output object, even if some rows later contain empty values.



Example Use Cases

1. Generate Daily Status Reports: Retrieve all rows from a project tracking sheet, parse the data, and compile a summary email of daily progress.

2. Migrate Data to External Databases: Extract a range of rows from Smartsheet to insert them into a SQL database or BigQuery for long-term warehousing.

3. Bulk Audit Compliance: Fetch rows from an expense report sheet to programmatically validate that all entries adhere to company policy before approval.

4. Sync with CRM Systems: Pull client data from a master sheet to update records in Salesforce or HubSpot.

5. Process Attachments: Retrieve rows to extract uploaded invoice files (using the attachment payload) and save them to Google Drive.



Example Scenario: The Automated Sales Pipeline Report

Goal

You manage a “Sales Pipeline” sheet. Every Monday, you want to retrieve all current deal data to generate a PDF report.


Steps to Implement

1. Scheduled Flow: Use a Scheduled trigger to run the flow every Monday at 9:00 AM.

2. Configure “List Rows”:

  • Connection: Select your Smartsheet connection.
  • Sheet Id: Choose “Sales Pipeline” from the dropdown.
  • From Row Number: 1
  • To Row Number: 5000 (to ensure all rows are captured).

3. Parse the Output:

  • Add a Convert JSON to Object action.
  • Map the Result token from the List Rows step into the JSON Text input.
  • Follow the Important Note About Parsing above to set up your Schema accurately.

4. Loop and Process: Add a Foreach Loop to iterate through the parsed objects, extracting the Deal Values (via their Column IDs) to populate a document template.



Best Practices

1. Always Parse the Result: The output is raw JSON. You must use the Convert JSON to Object action to turn this text into usable data tokens.

2. Use the “Fully Populated Row” Schema Trick: Because Smartsheet omits empty fields from the JSON, your Convert JSON to Object schema might miss columns if your sample row has blank cells. Best practice: Create a dummy row in your Smartsheet where every single column is filled. Run a test flow to retrieve that row’s JSON, and use that exact JSON as your Schema Sample.

3. Identify Columns by ID: When mapping your parsed data later in the flow, you will see numbers (Column IDs) instead of names (like “Client Name”). You can find a column’s ID in Smartsheet by right-clicking the column header and selecting “Column Properties”.

4. Use Safe Upper Limits: When retrieving all rows, set the To Row Number significantly higher than your actual row count (e.g., 10000). The action will return all available rows up to that limit without error.

5. Filter Data Post-Retrieval: This action retrieves rows strictly by position (row number). To process only specific rows (e.g., Status = Open), retrieve the range first, parse the JSON, and then use a Query Collection action to filter the structured data.