Data validations error “cannot read property 'foreach' of undefined”

See more:

Javascript

Google

Hi everyone!

I'm creating some data validations with google sheets and I'm getting this error:

TypeError: Cannot read property 'forEach' of undefined

json2D @ Code.gs:194

Could someone please explain what am I doing wrong here? - The error is on the second line of the last function:

function json2D(obj){
  var done = [];
  obj.forEach(function(object){
    //"OID", "Action Date", "Locking Date", "Sale Amount", "Payout", "Promo Code", "Customer Status", "Location", "Customer ID", "Action Tracker", "Media Partner ID", "Media Partner"
      done.push([object.OID,object.Action_Date, object.locking_date, object.Sale_Amount ? parseFloat(object.Sale_Amount).toFixed(2) : 0, object.Payout ? parseFloat(object.Payout).toFixed(2) : 0 , object.Promo_Code, object.Customer_Status, object.Geo_Location, object.Customer_Id, object.AT_Id, object.mp_id, object.Media_Partner]);
  });
  return done;
}

Settings Spreadsheet: https://docs.google.com/spreadsheets/d/1LCUc7YFnLchBwTfZG6Czz5Q-wb2hLUNEve1BGfjiRU8/edit?usp=sharing

Let me know if you need more clarification or anything else to be able to understand the issue.

Thanks,

Mireia

What I have tried:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();

var settings = ss.getSheetByName("Settings");

var settingsCID = settings.getRange("A1").getValue();
var settingsStartDate = settings.getRange("D1").getValue();
var settingsEndDate = settings.getRange("E1").getValue();
var settingsValidationStartDate = settings.getRange("F1").getValue();

function onOpen() {
  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Pull Data')
      .addItem('Pending Actions', 'pullPendingActions')
      .addToUi();

};

function pullPendingActions(){
  var startDate = Utilities.formatDate(settingsStartDate, "GMT", "yyyy-MM-dd");
  startDate = startDate.slice(0,10);
  var endDate = Utilities.formatDate(settingsEndDate, "GMT", "yyyy-MM-dd");
  endDate = endDate.slice(0,10);

  console.log("start date: "+startDate + ", end date: "+endDate);
  var values = settings.getDataRange().getValues();
  var creds;
  var campaignIDs = [];
  var credentials = [];
  var actions;
  var names =[];
  var records = [];


  


  creds = settings.getRange(1, 3).getValue();
  name = settings.getRange(1, 1).getValue()
  campaignID = settings.getRange(1,2).getValue();

  if (!(ss.getSheetByName(name + " - Validations"))){
    ss.insertSheet(name + " - Validations");    
    var sheet = ss.getSheetByName(name + " - Validations");
    headers = ([["OID", "Action Date", "Locking Date", "Sale Amount", "Payout", "Promo Code", "Customer Status", "Location", "Customer ID", "Action Tracker", "Media Partner ID", "Media Partner"]]);
    
    sheet.getRange("A1:L1").setValues(headers); 
  }

  var sheet = ss.getSheetByName(name + " - Validations");

  var sheetLastRow = sheet.getLastRow();
  if (sheetLastRow >= 2){
    sheet.deleteRows(2, sheetLastRow-1);
    
  }



  var record = getAllData(startDate, endDate, campaignID,creds);
  console.log("record length pre filter:" + record.length)

  pendingRecords = record.filter(function(item){
    var lockingDate = item.locking_date;
    var lockingYear = +lockingDate.substring(0, 4);
    var lockingMonth = +lockingDate.substring(5, 7);
    var lockingDay = +lockingDate.substring(8, 10);
    lockingDate = new Date(lockingYear, lockingMonth - 1,lockingDay ).getTime();
    var startDate = settingsValidationStartDate.getTime();
    var endDate = settingsEndDate.getTime();
    //console.log("locking date: " + lockingDate + " startDate: " + startDate + " endDate: "+ endDate);
    if(lockingDate >= startDate && lockingDate <= endDate){
      console.log("date:" + item.Action_Date)
      return item;
    }
  });

  records = pendingRecords;
  
  console.log("records length: "  + records.length);

  //writeData(records, names);
  pushPendingActions(records, name);
  pullInquiries();
}



function pushPendingActions(records, name){
  console.log("pushing records")
  // for (i=0; i<names.length;i++){
  //   if (!(ss.getSheetByName(""+names[i]))){
  //     ss.insertSheet(""+names[i]);
  //   }
  // }

  sheet = ss.getSheetByName(""+name+ " - Validations");
  if (sheet){
    // sheet.appendRow(["OID", "Action Date", "Locking Date", "Sale Amount", "Payout", "Action Tracker", "Media Partner ID", "Media Partner", "Customer ID"])
    if (records.length>1){
      writeData(records, sheet); 
    }
    else{
      console.log("err")
    }
  }
  else {
    console.log("err")
  }

}


function writeData(data, sheet){
  console.log("writing data")
  allRecords = json2D(data);
 
  // write result
  //console.log(allRecords);
  sheet.getRange(sheet.getLastRow()+1, 1, allRecords.length, allRecords[0].length).setValues(allRecords);
}


function getAllData(startDate, endDate, campaignID){

  console.log("getting data");
              
  var creds = settings.getRange(1, 3).getValue();

  var credentials = Utilities.base64Encode(creds);

  var authHeader = "Basic " + credentials;

  var user = creds.split(":");

  user = user[0];

  var options = {
    headers: {Authorization: authHeader}
  }
  
  //dateA = new Date();
  
  var url = "https://api.impact.com/Advertisers/"+user+"/Reports/adv_action_listing_pm_only.json?Page="+1+"&ADV_AFFILIATE_MEDIA_SOURCE=0&ACTION_TYPE=0&PARTNER_RADIUS_SOLR=0&SUPERSTATUS_MS=PENDING&ACTION_ID=0&OID_ALL=0&ACTION_NAME=0&MP_GROUP=0&RELATIONSHIP_TYPE=0&SHAREDID=0&ADV_IO=0&REFERRAL_TYPE=0&ADV_CUSTOMER_STATUS=0&CUSTOMER_ID=0&ADV_PROMOCODE=0&ADV_CATEGORY_2=0&AD_TYPE2=0&CAM_AD_2=0&SHOW_CUSTOMER_ID=1&SHOW_CUSTOMER_STATUS=1&SHOW_LOCKING_DATE=1&SHOW_GEO_LOCATION=1&START_DATE="+startDate+"&END_DATE="+endDate+"&timeRange=CUSTOM&ompareEnabled=false&SUBAID="+campaignID;

  console.log(url);
  options = {muteHttpExceptions: true};
  var set = UrlFetchApp.fetch(url , options);


//  dateB = new Date();
//  console.log(dateB - dateA);
  
  var dataAll = JSON.parse(set.getContentText()); //
  dataRecords = dataAll.Records;

  if((dataAll["@numpages"] > 1)){
    for(i=2; i<=dataAll["@numpages"]; i++){
      console.log("getting page:"+i);
      page = i;

      var url = "https://api.impact.com/Advertisers/"+user+"/Reports/adv_action_listing_pm_only.json?Page="+page+"&ADV_AFFILIATE_MEDIA_SOURCE=0&ACTION_TYPE=0&PARTNER_RADIUS_SOLR=0&SUPERSTATUS_MS=PENDING&ACTION_ID=0&OID_ALL=0&ACTION_NAME=0&MP_GROUP=0&RELATIONSHIP_TYPE=0&SHAREDID=0&ADV_IO=0&REFERRAL_TYPE=0&ADV_CUSTOMER_STATUS=0&CUSTOMER_ID=0&ADV_PROMOCODE=0&ADV_CATEGORY_2=0&AD_TYPE2=0&CAM_AD_2=0&SHOW_CUSTOMER_ID=1&SHOW_CUSTOMER_STATUS=1&SHOW_LOCKING_DATE=1&SHOW_GEO_LOCATION=1&START_DATE="+startDate+"&END_DATE="+endDate+"&timeRange=CUSTOM&ompareEnabled=false&SUBAID="+campaignID;

      options = {muteHttpExceptions: true};
      set = UrlFetchApp.fetch(url, options);
      
      var data = JSON.parse(set.getContentText());

      for (var obj in data.Records){
        dataRecords.push(data.Records[obj]);
      }
      //dataRecords.push(data.Records);
    }
  }
  return dataRecords;
}

// Based on https://stackoverflow.com/a/54897035/1027723
const flatten_ = (obj, prefix = '', res = {}) => 
Object.entries(obj).reduce((r, [key, val]) => {
  const k = `${prefix}${key}`;
  if(typeof val === 'object' && val !== null){ 
    flatten_(val, `${k}_`, r);
  } else {
    res[k] = val;
  }
  return r;
}, res);


function json2D(obj){
  var done = [];
  obj.forEach(function(object){
    //"OID", "Action Date", "Locking Date", "Sale Amount", "Payout", "Promo Code", "Customer Status", "Location", "Customer ID", "Action Tracker", "Media Partner ID", "Media Partner"
      done.push([object.OID,object.Action_Date, object.locking_date, object.Sale_Amount ? parseFloat(object.Sale_Amount).toFixed(2) : 0, object.Payout ? parseFloat(object.Payout).toFixed(2) : 0 , object.Promo_Code, object.Customer_Status, object.Geo_Location, object.Customer_Id, object.AT_Id, object.mp_id, object.Media_Partner]);
  });
  return done;
}