10

I'm currently using the POST and PUT methods to add and modify rows in custom data extensions (as documented here) but i don't see anywhere on the documentation nor the stackexchange a GET method to get rows for a specific ID ?

4 Answers 4

17

You'll have to use the SOAP API to retrieve DataExtensionObjects.

Here's a sample envelope that illustrates the structure of the call:

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Header>
        <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" soapenv:mustUnderstand="1">
            <wsse:UsernameToken xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="UsernameToken-24440876">
                <wsse:Username>XXXXX</wsse:Username>
                <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">XXXXX</wsse:Password>
            </wsse:UsernameToken>
        </wsse:Security>
    </soapenv:Header>
    <soapenv:Body>
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>DataExtensionObject[EXTERNALKEYOFDE]</ObjectType>
                <Properties>EMAIL_ADDRESS</Properties>
                <Properties>CUSTOMER_ID</Properties>
                <Properties>FIRST_NAME</Properties>
                <Properties>SITE_GROUP</Properties>
                <Filter xsi:type="SimpleFilterPart">
                    <Property>EMAIL_ADDRESS</Property>
                    <SimpleOperator>equals</SimpleOperator>
                    <Value>[email protected]</Value>
                </Filter>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </soapenv:Body>
</soapenv:Envelope>

At this point in time, the SFMC SOAP API is significantly more complete/robust/whatever than the REST API. It's a good plan to follow the lead of the SFMC SDK, which utilizes both SOAP and REST.

2019-09-16 Update

This is floating around the community in some comments, but I thought I'd update my answer to include the undocumented REST route for retrieving a Data Extension row. The filter is specified in the URL.

GET /data/v1/customobjectdata/key/YOURDEEXTERNALKEY/rowset?$filter=emailAddress%20eq%20'[email protected]' HTTP/1.1
Host: YOURTENANT.rest.marketingcloudapis.com
Authorization: Bearer YOURAUTHTOKEN
Content-Type: application/json
User-Agent: PostmanRuntime/7.17.1
Accept: */*
Cache-Control: no-cache
Host: YOURTENANT.rest.marketingcloudapis.com
Accept-Encoding: gzip, deflate
Connection: keep-alive
cache-control: no-cache

Results

{
  "links": {
    "self": "/v1/customobjectdata/token/5f9c1ce3-8f0f-499c-a1dd-5a9bf6fbfa01/rowset?$page=1"
  },
  "requestToken": "71dac1af-88ed-48f1-bce2-259d2a1d5009",
  "tokenExpireDateUtc": "2019-09-17T18:48:03.03",
  "customObjectId": "9340eb5c-3a53-4490-a1f1-2d14c1b27c98",
  "customObjectKey": "YOURDEEXTERNALKEY",
  "pageSize": 2500,
  "page": 1,
  "count": 1,
  "items": [
    {
      "keys": {
        "emailAddress": "[email protected]"
      },
      "values": {
        "firstname": "Test",
        "lastname": "Person",
        "createddate": "7/18/2019 6:00:00 PM"
      }
    }
  ]
}
1
6

If you're Data Extension is included in an Attribute Group in Contact Builder, then you can use the search REST API method to retrieve a value from one or more Data Extensions. For example, in this case I'm retrieving the First Name and Last name from a Contacts Data Extension based on a couple of criteria (from the Email address and a different Data Extension):

POST https://www.exacttargetapis.com/contacts/v1/attributes/search
Authorization: Bearer insertAccessTokenHere
Content-Type: application/json

{
    "request": {
        "attributes": [
            {
                "key": "Contacts.First Name"
            },
            {
                "key": "Contacts.Last Name"
            }]

    },
    "conditionSet": {
        "operator": "And",
        "conditionSets": [],
        "conditions": [{
            "attribute": {
                "key": "Email Addresses.Email Address"
            },
            "operator": "Equals",
            "value": {
                "items": ["[email protected]"]
            }
        },
        {
            "attribute": {
                "key": "Centre Subscriptions.valid"
            },
            "operator": "Equals",
            "value": {
                "items": [true]
            }
        }]
    }
}
4
  • I don't think this works if a Contact has not been added for the matching row in the Data Extension. I've been testing this kind of retrieve and it didn't work for rows that had no Contact. Oct 12, 2017 at 19:51
  • Hmm, I didn't try that scenario. Good to know! Oct 12, 2017 at 20:45
  • 2
    It's so crazy that you can add a row to DE with REST but can't retrieve one. Maybe I'm missing something in the documentation. Oct 12, 2017 at 21:11
  • 1
    Yes, that makes sense now I come to think of it. This is based on the Contact Model, so it will only work for contacts. If you're not using Contacts, then you will have to use SOAP. Oct 13, 2017 at 0:01
1

I've found a Rest API v2 endpoint that can be used to retrieve data from data extension. I've also added some optional query parameters to filter data.

GET https://{{et_subdomain}}.rest.marketingcloudapis.com/data/v1/customobjectdata/key/{{DataExtensionKey}}/rowset?$pageSize=20&$page=1&$filter={{columnName}}%20eq%20'{{value}}'
Authorization: Bearer {{access_token}}
Content-Type: application/json

{
  "links": {
      "self": "/v1/customobjectdata/token/xxxxxxxx-xxxx-xxx-xxxxxxxxxxx/rowset?$page=1"
  },
  "requestToken": "{{request-token}}",
  "tokenExpireDateUtc": "2023-02-02T13:49:42.117",
  "customObjectId": "xxxxxxxx-xxxx-xxx-xxxxxxxxxxx",
  "customObjectKey": "xxxxxxxx-xxxx-xxx-xxxxxxxxxxx",
  "pageSize": 20,
  "page": 1,
  "count": 1,
  "top": 0,
  "items": [
      {
          "keys": {
              "id": "xxxxxxxx-xxxx-xxx-xxxxxxxxxxx"
          },
          "values": {
              "title": "Candy",
              "link": "https://www.google.com/",
              "price": "0.99 USD",
              "brand": "ABC",
              "image_link": "https://www.w3schools.com/html/img_girl.jpg",
              "availability": "in stock"
          }
      }
  ]
}

Docs

GET https://{{et_subdomain}}.rest.marketingcloudapis.com/data/v1/rest
0

Please check https://developer.salesforce.com/docs/marketing/marketing-cloud/references/mc-custom_objects?meta=Summary, it has newly added REST APIs

New contributor
Qingsong Yao is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • Can you add the relevant content here? Links (even to salesforce docs) tend to break over time and you'll likely just get downvoted without adding something of use to the answer itself.
    – Matt Lacey
    10 hours ago
  • I'd go so far as to say that Salesforce tends to break their links more frequently than the average site. All the more reason to reproduce the content here. That, and every additional forced click makes it less likely for people to get meaningful information.
    – Derek F
    6 hours ago

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .