Google DFA API - List Placement/Campaign IDs - Apps Script

DETAILS:

Hi everyone,

I've got two functions on App Scripts for my Google Sheets file. These were created using the DFA API Reference for DoubleClick Campaign Manager v3.3.

ERROR:

One is for list Campaign IDs and the other Placement IDs. My problem is that, 9/10 they don't work - throws a JSON Parse error:

SyntaxError: Expected end of stream at char 2292 (Number can vary).

TRIED:

I think I have done everything I need to. I've referenced the API documentation to build it. And used the same logic as my other working functions to process and execute but same error.

The API is enabled in the Script Editor under Resources > Advanced Google Services. And using the latest version - v3.3 as v3.2 is now depreciated.

The problem, I seem to be having is when I call the variable for campaignInfo/placementInfo. Despite specifying what I am searching for with variables "search" and "searchString", I seem to get over 1000 entries, and when I try to parse that into JSON to the variable "data", it crashes there. Also, none of the entries seem to be related to my query as well.

For "campaignInfo" I am providing the specific campaign name. I have tried the specifying the "advertiserId" as well but same problem.

For "placementInfo", I am providing "kind", "campaignId", and "name" of the placement. I have also tried the specifying the "advertiserId" as well but same problem.

I'm using preset values in an example sheet to test the two and nothing so far. Would appreciate if someone can tell me where I've gone wrong.

SCRIPTS:

Campaign IDs Code: - Note: This was working up until a few months ago and now doesn't. I haven't made any changes to it.

function listCampaignIDs() { //and placement end dates
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet15');
  var profileID = _fetchProfileId();

  var campaignNames = sheet.getRange(2, 2, ss.getRange("B1").getDataRegion().getLastRow()).getValues();
  var campaignArray = campaignNames.map(function(r) {
    return [r]
  });
  Logger.log(campaignNames.length)

  try {
    for (i = 2; i <= campaignNames.length; i++) {
      if (sheet.getRange(i, 6).isBlank()) {
        var search = {
          "name": sheet.getRange(i, 2).getValue().toString()
        }
        Logger.log(search);
        var campaignInfo = DoubleClickCampaigns.Campaigns.list(profileID, search).campaigns;
        var data = JSON.parse(campaignInfo);
        //Logger.log(data);
        var campaignID = data["id"];
        var campaignEndDate = data["endDate"];
        var campaignStartDate = data["startDate"];
        sheet.getRange(i, 6).setValue(campaignID);
        sheet.getRange(i, 5).setValue(campaignEndDate);
        sheet.getRange(i, 4).setValue(campaignStartDate);
      }
    }
  } catch (e) {
    Logger.log("Error is: %s", e);
    //SpreadsheetApp.getUi().alert(e);
  }
}

Placement IDs Code: - Note: This was never working... Though follows similar steps to what I did to the campaign version when that was working.

function listPlacementIDs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet15');
  var profileID = _fetchProfileId();

  var campaignIDs = sheet.getRange(2, 1, ss.getRange("A1").getDataRegion().getLastRow()).getValues();
  var campaignIdsArray = campaignIDs.map(function(r) {
    return [r]
  });
  Logger.log(campaignIDs.length)

  var placementNames = sheet.getRange(2, 2, ss.getRange("B1").getDataRegion().getLastRow()).getValues();
  var placementNamesArray = placementNames.map(function(r) {
    return [r]
  });
  Logger.log(placementNames.length)

  try {
    for (i = 2; i <= campaignIDs.length; i++) {
      var campaignId = sheet.getRange(i, 1).getValue().toString()
      Logger.log(campaignId)
      var name = sheet.getRange(i, 2).getValue().toString()
      Logger.log(name)
      if (sheet.getRange(i, 3).isBlank()) {
        var searchString = {
          "kind": "dfareporting#placement",
          "campaignId": campaignId,
          "name": name
        }
        Logger.log(searchString);
        var placementInfo = DoubleClickCampaigns.Placements.list(profileID, searchString).placements;
        Logger.log(placementInfo)
        var data = JSON.parse(placementInfo);
        Logger.log(data);
        var placementId = data["id"];
        sheet.getRange(i, 3).setValue(placementId);
      }
    }
  } catch (e) {
    Logger.log("Error is: %s", e);
    SpreadsheetApp.getUi().alert(e);
  }
}

Thanks for your time, people. I'm open to any suggestions that might help/improve the below.

Thanks, Rajiv Ahmed

Logs were causing errors so removed.

Much thanks to @Jescanellas for the new perspective I needed to solve the problem.

UPDATED CODE - CAMPAIGN:

function listCampaignIDs() { //and placement end dates
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Campaigns');
  var profileID = _fetchProfileId();

  var campaignNames = sheet.getRange(2, 2, ss.getRange("B1").getDataRegion().getLastRow()).getValues();
  var campaignArray = campaignNames.map(function(r) {
    return [r]
  });

  try {
    for (i = 2; i <= campaignNames.length; i++) {
      if (sheet.getRange(i, 6).isBlank()) {
        var search = {
          "searchString": sheet.getRange(i, 2).getValue().toString(),
          "advertiserId": sheet.getRange(i, 1).getValue().toString()
        }
        var campaignInfo = DoubleClickCampaigns.Campaigns.list(profileID, search).campaigns;
        Logger.log(campaignInfo);
        var data = JSON.parse(campaignInfo);
        Logger.log(data);
        var campaignID = data["id"];
        var campaignEndDate = data["endDate"];
        var campaignStartDate = data["startDate"];
        sheet.getRange(i, 6).setValue(campaignID);
        sheet.getRange(i, 5).setValue(campaignEndDate);
        sheet.getRange(i, 4).setValue(campaignStartDate);
      }
    }
  } catch (e) {
    Logger.log("Error is: %s", e);
    //SpreadsheetApp.getUi().alert(e);
  }
}

UPDATED CODE - PLACEMENT:

function listPlacementIds() { //and placement end dates
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet15');
  var profileID = _fetchProfileId();

  var campaignIDs = sheet.getRange(2, 1, ss.getRange("A1").getDataRegion().getLastRow()).getValues();
  var campaignIdsArray = campaignIDs.map(function(r) {
    return [r]
  });

  var placementNames = sheet.getRange(2, 2, ss.getRange("B1").getDataRegion().getLastRow()).getValues();
  var placementNamesArray = placementNames.map(function(r) {
    return [r]
  });

  try {
    for (i = 2; i <= campaignIDs.length; i++) {
      var campaignId = sheet.getRange(i, 1).getValue().toString()
      var name = sheet.getRange(i, 2).getValue().toString()
      if (sheet.getRange(i, 3).isBlank()) {
        var search = {
          "kind": "dfareporting#placement",
          "campaignId": campaignId,
          "searchString": name
        }
        var placementInfo = DoubleClickCampaigns.Placements.list(profileID, search).placements;
        Logger.log(placementInfo)
        var data = JSON.parse(placementInfo);
        Logger.log(data);
        var placementId = data["id"];
        sheet.getRange(i, 3).setValue(placementId);
      }
    }
  } catch (e) {
    Logger.log("Error is: %s", e);
    SpreadsheetApp.getUi().alert(e);
  }
}

Much thanks to @Jescanellas for the new perspective I needed to solve the problem.

UPDATED CODE - CAMPAIGN:

function listCampaignIDs() { //and placement end dates
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Campaigns');
  var profileID = _fetchProfileId();

  var campaignNames = sheet.getRange(2, 2, ss.getRange("B1").getDataRegion().getLastRow()).getValues();
  var campaignArray = campaignNames.map(function(r) {
    return [r]
  });

  try {
    for (i = 2; i <= campaignNames.length; i++) {
      if (sheet.getRange(i, 6).isBlank()) {
        var search = {
          "searchString": sheet.getRange(i, 2).getValue().toString(),
          "advertiserId": sheet.getRange(i, 1).getValue().toString()
        }
        var campaignInfo = DoubleClickCampaigns.Campaigns.list(profileID, search).campaigns;
        Logger.log(campaignInfo);
        var data = JSON.parse(campaignInfo);
        Logger.log(data);
        var campaignID = data["id"];
        var campaignEndDate = data["endDate"];
        var campaignStartDate = data["startDate"];
        sheet.getRange(i, 6).setValue(campaignID);
        sheet.getRange(i, 5).setValue(campaignEndDate);
        sheet.getRange(i, 4).setValue(campaignStartDate);
      }
    }
  } catch (e) {
    Logger.log("Error is: %s", e);
    //SpreadsheetApp.getUi().alert(e);
  }
}

UPDATED CODE - PLACEMENT:

function listPlacementIds() { //and placement end dates
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet15');
  var profileID = _fetchProfileId();

  var campaignIDs = sheet.getRange(2, 1, ss.getRange("A1").getDataRegion().getLastRow()).getValues();
  var campaignIdsArray = campaignIDs.map(function(r) {
    return [r]
  });

  var placementNames = sheet.getRange(2, 2, ss.getRange("B1").getDataRegion().getLastRow()).getValues();
  var placementNamesArray = placementNames.map(function(r) {
    return [r]
  });

  try {
    for (i = 2; i <= campaignIDs.length; i++) {
      var campaignId = sheet.getRange(i, 1).getValue().toString()
      var name = sheet.getRange(i, 2).getValue().toString()
      if (sheet.getRange(i, 3).isBlank()) {
        var search = {
          "kind": "dfareporting#placement",
          "campaignId": campaignId,
          "searchString": name
        }
        var placementInfo = DoubleClickCampaigns.Placements.list(profileID, search).placements;
        Logger.log(placementInfo)
        var data = JSON.parse(placementInfo);
        Logger.log(data);
        var placementId = data["id"];
        sheet.getRange(i, 3).setValue(placementId);
      }
    }
  } catch (e) {
    Logger.log("Error is: %s", e);
    SpreadsheetApp.getUi().alert(e);
  }
}