Retrieving DE data with API. ContinueRequest is returning the same data

google-apps-scriptmarketing-cloudmarketingcloudapisoap-api

I'm making the following SOAP API call to get records from a data extension. The DE holds more than 2500 records, so if the response says more data is available, my script will insert the request ID in <ContinueRequest>. As far as I can tell, my code is working the way it's supposed to, but it's retrieving the same data with every call. I've logged the Request ID, and it's always the same no matter how many times I've used it in <ContinueRequest>. Is that supposed to happen, or is it supposed to be different with every subsequent request?

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
  <s:Header>
    <a:Action s:mustUnderstand="1">Retrieve</a:Action>
    <a:To s:mustUnderstand="1">https://'+subDomain+'.soap.marketingcloudapis.com/service.asmx</a:To>
    <fueloauth xmlns="http://exacttarget.com">'+docProperties.getProperty('accessKey')+'</fueloauth>
  </s:Header>
  <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
      <RetrieveRequest>
        <ContinueRequest>requestId</ContinueRequest>
        <ObjectType>DataExtensionObject[Clicks by EmailName]</ObjectType>
        <Properties>EmailName</Properties>
        <Properties>LinkName</Properties>
        <Properties>URL</Properties>
        <Properties>LinkContent</Properties>
        <Properties>ClickedDate</Properties>
        <Properties>TClicks</Properties>
        <Properties>UClicks</Properties>
      </RetrieveRequest>
    </RetrieveRequestMsg>
  </s:Body>
</s:Envelope>

This is the Google Apps Script I'm using to call the API and update a Google Sheet.

function getData() {
  var results = [];
  var requestId = null;
  function returnRequestId() {
    if (requestId == null) {
      return '';
    }
    else {
      Logger.log('Old Request ID:'+requestId);
      return '<ContinueRequest>'+requestId+'</ContinueRequest>';
    }
  }
  do {
    var xml = '<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<s:Envelope xmlns:s=\"http://www.w3.org/2003/05/soap-envelope\" xmlns:a=\"http://schemas.xmlsoap.org/ws/2004/08/addressing\" xmlns:u=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd\"><s:Header><a:Action s:mustUnderstand=\"1\">Retrieve</a:Action><a:To s:mustUnderstand=\"1\">https://'+subDomain+'.soap.marketingcloudapis.com/service.asmx</a:To><fueloauth xmlns=\"http://exacttarget.com\">'+docProperties.getProperty('accessKey')+'</fueloauth></s:Header><s:Body xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"><RetrieveRequestMsg xmlns=\"http://exacttarget.com/wsdl/partnerAPI\"><RetrieveRequest>'+
    returnRequestId()
    +'<ObjectType>DataExtensionObject[Clicks by EmailName]</ObjectType><Properties>EmailName</Properties><Properties>LinkName</Properties><Properties>URL</Properties><Properties>LinkContent</Properties><Properties>ClickedDate</Properties><Properties>TClicks</Properties><Properties>UClicks</Properties></RetrieveRequest></RetrieveRequestMsg></s:Body>\n</s:Envelope>';
    Logger.log(xml);
    var options = {
      "method" : "post",
      "contentType" : "text/xml",
      headers:{
        "SoapAction" : "Retrieve",
        "Authorization": 'Bearer ' + docProperties.getProperty('accessKey')
      },
      "payload" : xml,
      "muteHttpExceptions":true
    };
    var response = UrlFetchApp.fetch('https://'+subDomain+'.soap.marketingcloudapis.com/service.asmx', options);
    Logger.log(response);
    var parseXml = XmlService.parse(response.getContentText());
    var root = parseXml.getRootElement();
    var c1 = root.getChildren();
    for (var i = 0; i < c1.length; i++) {
      if (c1[i].getName() == "Body") {
        var c2 = c1[i].getChildren()[0].getChildren(); //at the results level here
        Logger.log('Overall Status is: '+c2[0].getText());
        if (c2[0].getText() == "MoreDataAvailable") {
          requestId = c2[1].getText();
          Logger.log('New Request ID:'+requestId);
        }
        for (var j = 0; j < c2.length; j++) {
          if (c2[j].getName() == "Results") {
            var c3 = c2[j].getChildren();
            for (var k = 0; k < c3.length; k++) {
              if (c3[k].getName() == "Properties") {
                var result = [];
                var ns2 = c3[k].getChildren()[0].getChildren()[0].getNamespace();
                result.push(c3[k].getChildren()[0].getChildText("Value", ns2));
                result.push(c3[k].getChildren()[1].getChildText("Value", ns2));
                result.push(c3[k].getChildren()[2].getChildText("Value", ns2));
                result.push(c3[k].getChildren()[3].getChildText("Value", ns2));
                result.push(c3[k].getChildren()[4].getChildText("Value", ns2));
                result.push(c3[k].getChildren()[5].getChildText("Value", ns2));
                result.push(c3[k].getChildren()[6].getChildText("Value", ns2));
                results.push(result);
              }
            }
          }
        }
      }
    }
    var firstEmptyRow = datasheet.getLastRow() + 1;
    var en = datasheet.getRange(firstEmptyRow,1,results.length,7);
    en.setValues(results);
  } while(c2[0].getText() == "MoreDataAvailable"); 
}

Best Answer

I found my mistake. The solution was to move var results = [] into the "do" operation so that it gets overwritten with every new iteration.

Related Topic