Recurrence
{
  "type": "Recurrence",
  "recurrence": {
    "interval": 1,
    "frequency": "Day",
    "timeZone": "Eastern Standard Time",
    "startTime": "2025-07-01T09:00:00Z"
  }
}

List rows present in a table
{
  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "source": "groups/89081b99-8ab2-4f59-aee8-093778d0d441",
      "drive": "b!3kzAcraAakyqX511RmVoJ9WIO311YNtKkeFu7zAlxzEzcxCs2tjvQI_OLuhM1Qgf",
      "file": "01N24XK64KAGKQY5MQMFDI4XCKKQQOWKIA",
      "table": "{00000000-000C-0000-FFFF-FFFF00000000}",
      "dateTimeFormat": "ISO 8601"
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness",
      "connection": "shared_excelonlinebusiness",
      "operationId": "GetItems"
    }
  },
  "runAfter": {},
  "metadata": {
    "01N24XK64KAGKQY5MQMFDI4XCKKQQOWKIA": "/Monthly pharmacy audit.xlsx",
    "tableId": "{00000000-000C-0000-FFFF-FFFF00000000}"
  }
}

Completed emails
{
  "type": "InitializeVariable",
  "inputs": {
    "variables": [
      {
        "name": "CompletedEmails",
        "type": "array"
      }
    ]
  },
  "runAfter": {
    "List_rows_present_in_a_table": [
      "Succeeded"
    ]
  }
}

Map completion data
{
  "type": "Select",
  "inputs": {
    "from": "@outputs('List_rows_present_in_a_table')?['body/value']",
    "select": {
      "CompletionTime": "@item()?['Completion time']",
      "Email": "@item()?['Email']"
    }
  },
  "runAfter": {
    "CompletedEmails_compose": [
      "Succeeded"
    ]
  }
}

Filter this month:
{
  "type": "Query",
  "inputs": {
    "from": "@body('Map_Completion_Data')",
    "where": "@and(\n  not(empty(item()?['Completion time'])),\n  equals(\n    formatDateTime(item()?['Completion time'], 'yyyy-MM'),\n    formatDateTime(utcNow(), 'yyyy-MM')\n  )\n)\n"
  },
  "runAfter": {
    "Map_Completion_Data": [
      "Succeeded"
    ]
  }
}

Apply to each
{
  "type": "Foreach",
  "foreach": "@body('FilterThisMonth')",
  "actions": {
    "Append_to_array_variable": {
      "type": "AppendToArrayVariable",
      "inputs": {
        "name": "CompletedEmails",
        "value": "@{if(empty(item()?['Email']), '', item()?['Email'])}\n"
      }
    }
  },
  "runAfter": {
    "FilterThisMonth_compose": [
      "Succeeded"
    ]
  }
}

Append to array variable
{
  "type": "AppendToArrayVariable",
  "inputs": {
    "name": "CompletedEmails",
    "value": "@{if(empty(item()?['Email']), '', item()?['Email'])}\n"
  }
}

All target emails (note: all "xyz" emails are different)
{
  "type": "Compose",
  "inputs": [
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]"
  ],
  "runAfter": {
    "compose1": [
      "Succeeded"
    ]
  }
}

Remaining user email
{
  "type": "Query",
  "inputs": {
    "from": "@outputs('All_Target_Emails')",
    "where": "@not(contains(variables('CompletedEmails'), item()))\r\n"
  },
  "runAfter": {
    "All_Target_Emails": [
      "Succeeded"
    ]
  }
}

Apply to each
{
  "type": "Foreach",
  "foreach": "@body('RemainingUserEmail')",
  "actions": {
    "Compose_1": {
      "type": "Compose",
      "inputs": "@items('Apply_to_each')"
    }
  },
  "runAfter": {
    "RemainingUserEmail": [
      "Succeeded"
    ]
  }
}

Compose (note: this would be replaced by "send email v2" - only using this now to figure out why its sending to everyone
{
  "type": "Compose",
  "inputs": "@items('Apply_to_each')"
}