Identify the Type of an Automation Activity using SSJS WSProxy

Question

I'm trying to create an API call that will return the SQL Code from all Query Activities within an given Automation.
Using the information provided in this question Retrieve multiple tasks in an Automation via API, I have been able to develop the following SSJS on a Cloud Page:

var api = new Script.Util.WSProxy();
var cols1 = ['ObjectID'];
var Program = api.retrieve('Program', cols1, { Property : 'CustomerKey', SimpleOperator : 'equals', Value : '##automationCustomerKey##' });
var objectid = Program.Results[0].ObjectID;

var cols2 = ['ObjectID','Name'];
var Activity = api.retrieve('Activity', cols2, { Property : 'Program.ObjectID', SimpleOperator : 'equals', Value : objectid });
Write(Stringify(Activity));

This successfully outputs a list of all the Automation Activities in the specified Automation.

From here I want to identify which of the Activities in the Automation are the "SQL Query" type, and call the "QueryDefinition" API to get the "QueryText" Value (the SQL Code).

However there is no clear way to identify what TYPE of activity each of them are; SQL Query, SSJS Script, Send Email, etc.

The following table corresponds to the "PartnerAPIObjectID" values that match each activity type:

42 User-initiated Email
43 Import Definition
45 Group Definition
53 File Transfer Activity
73 Data Extract Activity
84 Report Activity
300 Query Activity
303 Filter Activity
423 Server Side Javascript Activity
425 ELT Activity
427 Build Audience Activity
467 Program Wait
724 Mobile Automation List Refresh Instance
725 MobileConnect Message Instance
726 Mobile File Import Instance
733 InteractionStudio
736 MobilePush Message Object Instance
749 Interaction Studio Event
756 Interaction Studio Date Event
771 Salesforce Send Activiy
783 GroupConnect
1010 Thunderhead Transfer Activity
1101 Interaction Studio Decision Activity
1701 PredictiveIntelligenceRecommendationActivity
Source: Salesforce Tier 2 Technical Support, Oct 2019

As above (and in the linked question), the "Activity" endpoint does not seem to have any reference to the "Activity Type" or a "PartnerAPIObjectID" field.

I'm looking for any information on how to achieve my goal - getting the SQL code of all activities within a given Automation.

Answer 1

If you don't mind using an undocumented REST endpoint, you could use the /automation/v1/automations/{{objID}} endpoint.

GET /automation/v1/automations/{{yourAutoObjId}} 
Host: {{subdomain}}.rest.marketingcloudapis.com
Authorization: Bearer {{authToken}}
Content-Type: application/json

This will return all the steps/activities inside the automation including ID, objectTypeId and Name.

The objectTypeID will relate to your 'PartnerAPIObjectID' values. From there you can use the corresponding REST endpoints or SOAP objects to gather the content of each step.

Example Response:

{
    "id": "XXXXXXXXXXXXXXXXXXXXXXXXXX",
    "name": "my_Automation",
    "description": "",
    "key": "XXXXXXXXXXXXXXXXXXXXXXX",
    "typeId": 1,
    "type": "scheduled",
    "statusId": 2,
    "status": "Ready",
    "categoryId": XXXXX,
    "lastRunTime": "2019-10-21T14:57:00.58",
    "lastRunInstanceId": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
    "schedule": {
        "scheduleStatus": "none"
    },
    "steps": [
        {
            "id": "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
            "name": "",
            "step": 1,
            "activities": [
                {
                    "id": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                    "name": "Salesforce Send",
                    "activityObjectId": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                    "objectTypeId": 771,
                    "displayOrder": 1
                },
                {
                    "id": "XXXXXXXXXXXXXXXXXXXXXXXX",
                    "name": "Query Activity",
                    "activityObjectId": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                    "objectTypeId": 300,
                    "displayOrder": 2
                },
                {
                    "id": "XXXXXXXXXXXXXXXXXXXXXXXXX",
                    "name": "Refresh Group",
                    "activityObjectId": "XXXXXXXXXXXXXXXXXXXXXXXXX",
                    "objectTypeId": 45,
                    "displayOrder": 3
                }
            ]
        },
        {
            "id": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
            "name": "",
            "step": 2,
            "activities": [
                {
                    "id": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                    "name": "Data Extract",
                    "activityObjectId": "XXXXXXXXXXXXXXXXXXXXXXXXX",
                    "objectTypeId": 73,
                    "displayOrder": 1
                },
                {
                    "id": "XXXXXXXXXXXXXXXXXXXXXXXXX",
                    "name": "File Transfer",
                    "activityObjectId": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                    "objectTypeId": 53,
                    "displayOrder": 2
                },
                {
                    "id": "XXXXXXXXXXXXXXXXXXXXXXXXX",
                    "name": "Data Factory Utility",
                    "activityObjectId": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                    "objectTypeId": 425,
                    "displayOrder": 3
                }
            ]
        },
        {
            "id": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
            "name": "",
            "step": 3,
            "activities": [
                {
                    "id": "XXXXXXXXXXXXXXXXXXXXXX",
                    "name": "Filter Activity",
                    "activityObjectId": "XXXXXXXXXXXXXXXXXXXX",
                    "objectTypeId": 303,
                    "displayOrder": 1,
                    "targetDataExtensions": [
                        {
                            "id": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                            "name": "DEName",
                            "key": "DEKey",
                            "description": "",
                            "rowCount": 3
                        }
                    ]
                },
                {
                    "id": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                    "name": "Fire Event",
                    "activityObjectId": "XXXXXXXXXXXXXXXXXXXXXXX",
                    "objectTypeId": 749,
                    "displayOrder": 2
                }
            ]
        }
    ]
}

Here is my list of ObjectTypeIds:

Salesforce Send
"objectTypeId": 771

Query Activity
"objectTypeId": 300

Data Extract
"objectTypeId": 73

File Transfer
"objectTypeId": 53

Filter Activity
"objectTypeId": 303

Fire Event
"objectTypeId": 749

Import File
"objectTypeId": 43

Script Activity
"objectTypeId": 423

Guided Send
"objectTypeId": 42

Wait Activity
"objectTypeId": 467

Verification Activity
"objectTypeId": 1000

Refresh Group
"objectTypeId": 45

Data Factory Utility
"objectTypeId": 425

Send SMS
"objectTypeId": 725

Import Mobile Contacts
"objectTypeId": 726

Refresh Mobile Filtered List
"objectTypeId": 724

Send GroupConnect
"objectTypeId": 783

Report Definition
"objectTypeId": 84

Send Push
"objectTypeId": 736