Power Automate Smartsheet to ArcGIS Online Connection Help

Hi everyone! So I'd like to start by stating that I don't have much experience with coding or using Power Automate. I've been trying to teach myself but I keep running into issues while creating this flow.

Background: I'll do my best to explain my situation. I have a Smartsheet (SS) with several columns, including x,y coordinates and Unique IDs associated with the data. It is connected to other sheets within Smartsheet from where the data is pulled. I have an ArcGIS Online (AGOL) feature layer with the exact same data, using the xy coordinates to plot points on a map. For those not familiar with AGOL, think of the data within the feature layer as a table, just like in Smartsheet or Excel. Essentially, the array pulled from the feature layer will look the same as the array pulled from Smartsheet.

Goal: The data on this sheet is constantly changing, so I want to build a flow that is either triggered or reoccurring, that compares the data from the Smartsheet with the data within the AGOL feature layer, and updates or create rows (if new rows are added). The data can be compares using the common Unique ID.

Progress: So, I've been able to start the flow and extract the arrays from both SS and AGOL using "Get sheet data (dynamic schema)" for SS and "Get feature layer data" for AGOL with JSON as the output. I had to use a "Parse JSON" action on the SS because some data type errors. Now, I'm not sure where to go from here. When I try to use conditions or filter array, it keeps trying to make me use for each loops and my flow runs infinitely and gets stuck here. I've scoured the internet and ChatGPT trying to fix this. I've created variables, yada yada to try and work around the loops but I can't reference the data without it prompting a for each loop. Please help!

Answers

  • ChristianFinke
    ChristianFinke ✭✭✭
    edited 10/29/24

    Hi @MapGal,

    Since both datasets are arrays, some looping is unavoidable, but you can streamline it. Store the Unique IDs from both Smartsheet and AGOL in variables for easy comparison. Use Filter Array to identify new or updated rows, and loop only over those with Apply to Each to avoid infinite loops. Then, use Update/Create Feature actions for AGOL as needed. You can schedule the flow or trigger it on Smartsheet changes. Let me know if you run into any trouble!

    Best,

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting