Power Automate (Flow) Get Cell From Excel Spreadsheet

Question

I'm looking to automate a process using Power Automate:

User opens a form (built in MS Forms) and enters some figures
Flow takes these figures and adds them to a spreadsheet stored on OneDrive (with date & time)
The spreadsheet performs calculations on the data
Flow then waits 30 seconds (to give the spreadsheet time to perform calculations)
Flow gets the calculation (column called 'Multiple') from the spreadsheet and if it falls between two values, sends a warning to a Teams chat.

I have got the flow to do 1-4 but I'm struggling with 5. Things I've tried:

List Rows Present in a Table, then compose adding last(body('List_rows_present_in_a_table')?['value'])
List Rows Present in a Table, applying an Order By Date, Time Desc, choosing the top 1 rows

Obviously I've no idea what I'm doing and have taken the above from the internet via search engine results.

I need to return the last row and return a value from it. I may need to get other values from other columns at some point too so it'd be handy to know how to extract anything and I can just apply the logic to get this last record. Any help would be appreciated.

This is the output from the list_rows...

{
  "statusCode": 200,
  "headers": {
    "Pragma": "no-cache",
    "Transfer-Encoding": "chunked",
    "Vary": "Accept-Encoding",
    "x-ms-request-id": "6a5e4583-ba65-47f7-9acd-44e3833e14f8;e387566b-9dd6-4916-b749-34f5e4a666ad;e6a2af19-cab5-4d23-b0e4-446f9383411d;492ea8b3-4aa9-4703-82ce-8214cee0d287",
    "OData-Version": "4.0",
    "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
    "X-Content-Type-Options": "nosniff",
    "X-Frame-Options": "DENY",
    "Timing-Allow-Origin": "*",
    "x-ms-apihub-cached-response": "true",
    "x-ms-apihub-obo": "true",
    "Cache-Control": "no-store, no-cache",
    "Date": "Mon, 23 May 2022 10:47:50 GMT",
    "Content-Type": "application/json; odata.metadata=minimal; odata.streaming=true",
    "Expires": "-1",
    "Content-Length": "7785"
  },
  "body": {
    "@odata.context": "https://excelonline-ukw.logic-ase-ukwest.p.azurewebsites.net/$metadata#drives('b%21gfFR-ZV53U-aCUKgEmMbVA4tJoGJWBNHlsM4s2Mc51jO-ENEU1S-Rq63C1uxVytI')/Files('01OPLUCDQHJ7VCPJB5FRD2CAOAKEFV2N7S')/Tables('%7B48F61074-8C66-4AF5-B0F9-6E19B6902C96%7D')/items",
    "value": [
      {
        "@odata.etag": "",
        "ItemInternalId": "710c9d6c-94ea-4c2d-a7a4-3b3ae77e16d6",
        "Date": "44698.6458449074",
        "Time": "44698.6458449074",
        "g/l ni": "4",
        "ECV808Ha": "5",
        "Multiple": "20"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "d6a51bf4-6124-479b-801a-fd064c9e60bf",
        "Date": "44698.6492824074",
        "Time": "44698.6492824074",
        "g/l ni": "5",
        "ECV808Ha": "15",
        "Multiple": "75"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "041e248b-24aa-48cd-8b56-f7b034ebcac0",
        "Date": "44698.6550578704",
        "Time": "44698.6550578704",
        "g/l ni": "5.2",
        "ECV808Ha": "4.8",
        "Multiple": "24.96"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "9bd74e5c-ab80-4441-ac79-68cf960bcb2b",
        "Date": "44698.6656597222",
        "Time": "44698.6656597222",
        "g/l ni": "4",
        "ECV808Ha": "45",
        "Multiple": "180"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "a506830f-2ce0-4d68-ad40-cdc8e8b6f2d8",
        "Date": "44699.6349884259",
        "Time": "44699.6349884259",
        "g/l ni": "8.6",
        "ECV808Ha": "9.3",
        "Multiple": "79.98"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "5c946cba-d923-4abd-b4e2-747ef4d26c61",
        "Date": "44699.6391435185",
        "Time": "44699.6391435185",
        "g/l ni": "15.2",
        "ECV808Ha": "8.2",
        "Multiple": "124.64"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "cfb77053-0587-48c2-8e25-2bfe4130667a",
        "Date": "44699.6422337963",
        "Time": "44699.6422337963",
        "g/l ni": "14.2",
        "ECV808Ha": "18.6",
        "Multiple": "264.12"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "6d1f27e7-18d7-4686-9610-5e4cf8eb18bf",
        "Date": "44699.6454976852",
        "Time": "44699.6454976852",
        "g/l ni": "8.2",
        "ECV808Ha": "2.6",
        "Multiple": "21.32"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "9799d5d1-0a05-4a92-b8a3-26a2e795b460",
        "Date": "44699.6517824074",
        "Time": "44699.6517824074",
        "g/l ni": "15",
        "ECV808Ha": "15.2",
        "Multiple": "228"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "bb58c18a-1b10-418b-9782-7fc9cc2d7bf0",
        "Date": "44699.6672453704",
        "Time": "44699.6672453704",
        "g/l ni": "14",
        "ECV808Ha": "12.2",
        "Multiple": "170.8"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "ef60df80-9658-4e5b-bb03-4176e43670eb",
        "Date": "44699.6729166667",
        "Time": "44699.6729166667",
        "g/l ni": "15",
        "ECV808Ha": "16",
        "Multiple": "240"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "1e1d1f61-cc7d-4a6d-ac6e-3f3ad38def5d",
        "Date": "44699.6741319444",
        "Time": "44699.6741319444",
        "g/l ni": "12.2",
        "ECV808Ha": "13.4",
        "Multiple": "163.48"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "e780c125-3199-4360-854f-f3ef01944225",
        "Date": "44700.502962963",
        "Time": "44700.502962963",
        "g/l ni": "4",
        "ECV808Ha": "2.5",
        "Multiple": "10"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "40319bdb-8923-4149-908c-7d2e10f8555b",
        "Date": "44700.5053356482",
        "Time": "44700.5053356482",
        "g/l ni": "5",
        "ECV808Ha": "8",
        "Multiple": "40"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "3ce6dbf0-919f-4ae7-9a90-483c18382b1a",
        "Date": "44700.527650463",
        "Time": "44700.527650463",
        "g/l ni": "4.5",
        "ECV808Ha": "8.6",
        "Multiple": "38.7"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "3afffdaf-7b2e-4c5a-9407-aa20dedaf1db",
        "Date": "44700.5286111111",
        "Time": "44700.5286111111",
        "g/l ni": "8.5",
        "ECV808Ha": "4.2",
        "Multiple": "35.7"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "312b51be-1fb8-4b5e-ab0c-d0584ce0a947",
        "Date": "44700.5975",
        "Time": "44700.5975",
        "g/l ni": "8",
        "ECV808Ha": "14",
        "Multiple": "112"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "8ffbff5c-c94c-4681-830f-07898984e2dd",
        "Date": "44700.6011689815",
        "Time": "44700.6011689815",
        "g/l ni": "4",
        "ECV808Ha": "8",
        "Multiple": "32"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "a02f1621-c007-483a-982e-e16520135adb",
        "Date": "44700.6019675926",
        "Time": "44700.6019675926",
        "g/l ni": "4",
        "ECV808Ha": "2.5",
        "Multiple": "10"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "3afcc248-15e1-4bdf-911c-baa2531d9c01",
        "Date": "44700.6034837963",
        "Time": "44700.6034837963",
        "g/l ni": "4.5",
        "ECV808Ha": "5.25",
        "Multiple": "23.625"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "bb09f769-5244-410d-8883-ee79e2931487",
        "Date": "44700.6041435185",
        "Time": "44700.6041435185",
        "g/l ni": "80",
        "ECV808Ha": "2.5",
        "Multiple": "200"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "59335733-aae0-48c0-b1db-d9f165ea20a9",
        "Date": "44700.6059722222",
        "Time": "44700.6059722222",
        "g/l ni": "4",
        "ECV808Ha": "2.5",
        "Multiple": "10"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "5ed3abf6-167a-4eaf-92f2-8d2b1905430b",
        "Date": "44700.6153125",
        "Time": "44700.6153125",
        "g/l ni": "8",
        "ECV808Ha": "6",
        "Multiple": "48"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "c78052c0-0c39-40d4-aa59-cb47a1cf087b",
        "Date": "44700.645",
        "Time": "44700.645",
        "g/l ni": "4",
        "ECV808Ha": "2.5",
        "Multiple": "10"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "25cce007-95ef-46d5-b358-1170099cf116",
        "Date": "44700.6467939815",
        "Time": "44700.6467939815",
        "g/l ni": "4",
        "ECV808Ha": "4.5",
        "Multiple": "18"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "40850a3b-4fa8-4a8b-955c-7ae455275d2a",
        "Date": "44700.6484027778",
        "Time": "44700.6484027778",
        "g/l ni": "8",
        "ECV808Ha": "6.2",
        "Multiple": "49.6"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "bdf7718c-04fe-4734-87e0-919e24d2c0ca",
        "Date": "44700.6496412037",
        "Time": "44700.6496412037",
        "g/l ni": "6",
        "ECV808Ha": "6",
        "Multiple": "36"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "ebc60adc-80a1-4c40-b5a1-970e7845a679",
        "Date": "44701.382662037",
        "Time": "44701.382662037",
        "g/l ni": "4",
        "ECV808Ha": "5",
        "Multiple": "20"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "14ef1d2a-4479-46d5-9f1f-9efc489ef91a",
        "Date": "44701.4095486111",
        "Time": "44701.4095486111",
        "g/l ni": "5",
        "ECV808Ha": "5.5",
        "Multiple": "27.5"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "d644b688-8e91-4f81-a4f4-d63650b1837e",
        "Date": "44701.414837963",
        "Time": "44701.414837963",
        "g/l ni": "8",
        "ECV808Ha": "6",
        "Multiple": "48"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "749cb9ee-1214-4154-8c17-9f6de606efe8",
        "Date": "44701.4172337963",
        "Time": "44701.4172337963",
        "g/l ni": "8",
        "ECV808Ha": "65",
        "Multiple": "520"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "22266488-3477-4b10-9670-71b529d20a24",
        "Date": "44701.4225115741",
        "Time": "44701.4225115741",
        "g/l ni": "4",
        "ECV808Ha": "6",
        "Multiple": "24"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "2afa89ca-33d0-4689-b1ba-796ee73ef517",
        "Date": "44701.4230439815",
        "Time": "44701.4230439815",
        "g/l ni": "4",
        "ECV808Ha": "8",
        "Multiple": "32"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "d19bda68-b5ee-4785-88c2-e5ff607fb972",
        "Date": "44701.4236458333",
        "Time": "44701.4236458333",
        "g/l ni": "8",
        "ECV808Ha": "90",
        "Multiple": "720"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "52dc3cf9-fae0-4ab7-ba12-70bcaaf2ebf5",
        "Date": "44701.4238425926",
        "Time": "44701.4238425926",
        "g/l ni": "8",
        "ECV808Ha": "60",
        "Multiple": "480"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "23978938-312a-4f8f-be6d-be7c9b9ec070",
        "Date": "44701.4204513889",
        "Time": "44701.4204513889",
        "g/l ni": "4",
        "ECV808Ha": "5",
        "Multiple": "20"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "337fefdb-3cb4-4b36-a38b-4676f4037896",
        "Date": "44701.4305787037",
        "Time": "44701.4305787037",
        "g/l ni": "18",
        "ECV808Ha": "16",
        "Multiple": "288"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "9bca489c-fa39-44d7-adff-6a4520fbb526",
        "Date": "44704.3115046296",
        "Time": "44704.3115046296",
        "g/l ni": "4",
        "ECV808Ha": "8",
        "Multiple": "32"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "b00a9901-1f45-41b5-b14b-a0f78eced0e0",
        "Date": "44704.4353240741",
        "Time": "44704.4353240741",
        "g/l ni": "6",
        "ECV808Ha": "525",
        "Multiple": "3150"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "1ef7b9c0-e575-424c-9939-d59aa19f4944",
        "Date": "44704.4497337963",
        "Time": "44704.4497337963",
        "g/l ni": "5",
        "ECV808Ha": "6",
        "Multiple": "30"
      }
    ]
  }
}

Can you provide a screenshot showing the cell(s) you want to return? Also, is it always the same set of cells? Can it be set to a named range? To make it easier for the calling application? — Skin
– Skin, Commented May 22, 2022 at 5:45
Did you get an error because if I do exactly what you've listed, I can get the table from the table and also easily retrieve the last row. — Skin
– Skin, Commented May 22, 2022 at 6:20
Cool, but now you need to show us the expression in the condition action, that’s what it’s conplaining about. — Skin
– Skin, Commented May 23, 2022 at 8:15
@Skin Huzzah!!!! It worked :) I get it now. Thank you so much for your help. — David
– David, Commented May 26, 2022 at 10:33

Skin · Accepted Answer · 2022-05-23 11:31:08Z

1

If you want to easily refer to any of the fields from the last item in the array from the Excel table, you should use a Parse JSON action but you should pass in an example object to set the schema.

That will generate the dynamic content that you can refer to at any subsequent step in the process.

If I pass an example object in to define the schema that looks like this ...

{
  "@odata.etag": "",
  "ItemInternalId": "4b329100-49e6-41ef-8ae0-a5ecaf82f8f6",
  "Date": "44704.44973",
  "Time": "44704.44973",
  "g/l ni": "5",
  "ECV808Ha": "6",
  "Multiple": "30"
}

... the schema comes out like this ...

{
    "properties": {
        "@@odata.etag": {
            "type": "string"
        },
        "Date": {
            "type": "string"
        },
        "ECV808Ha": {
            "type": "string"
        },
        "ItemInternalId": {
            "type": "string"
        },
        "Multiple": {
            "type": "string"
        },
        "Time": {
            "type": "string"
        },
        "g/l ni": {
            "type": "string"
        }
    },
    "type": "object"
}

... and the expression to populate the content is as you had in relation to getting the last item ...

last(body('List_rows_present_in_a_table')?['value'])

... so that helps you with the any field part of your question.

When it comes to the condition, this is what will work for you ...

The expression on the left had side is ...

float(body('Parse_JSON')?['Multiple'])

I need to encapsulate it in the float() function so it turns it into a number.

From there, I get a result where the comparison will be determined.

For the last value in your table, it evaluates to true.

answered May 23, 2022 at 11:31

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David Over a year ago

I'm getting the following error... InvalidJSON. The 'content' property of actions of type 'ParseJson' must be valid JSON. The provided value cannot be parsed: 'Unexpected character encountered while parsing value: l. Path '', line 0, position 0.'.

Skin Over a year ago

Paste your expression here as a comment that you used in the content field.

David Over a year ago

"content": "last(body('List_rows_present_in_a_table')?['value'])", (this is in the JSON raw inputs section)

Skin Over a year ago

Right’o, next thing, add a screenshot of the Parse JSON action as you have it in your flow definition.

David Over a year ago

I'll add it to the main question. I need to leave for the day now but will pick up again when I'm back in office on Wednesday. I appreciate your help.

Collectives™ on Stack Overflow

Power Automate (Flow) Get Cell From Excel Spreadsheet

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related