1

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"); 
}
4
  • Seems pretty intense, how did you figure the google code part out? Commented Sep 27, 2022 at 22:48
  • @StanAlachniewicz Google's documentation for Apps Script is pretty rubust: developers.google.com/apps-script/reference/xml-service In the end this project didn't pan out because I had to pull in far too much data and the script always exceeded the time limit.
    – K K
    Commented Sep 28, 2022 at 15:36
  • Thanks, what did you find was the limit? I have close to 1M records Commented Sep 28, 2022 at 16:11
  • 1
    I tried to get Inclu-cat's solution to work, but never got there in the end: inclu-cat.net/2021/12/14/…
    – K K
    Commented Sep 28, 2022 at 19:04

1 Answer 1

1

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

You must log in to answer this question.

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