Click here to Skip to main content
15,038,345 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:

JavaScript
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:

JavaScript
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;
}
Posted
Updated 27-Jul-21 7:38am
Comments
Richard MacCutchan 25-Jul-21 3:25am
   
The error is telling you that obj is null, i.e. it has not been set to a valid reference.

1 solution

The error is not at the next to last line, but as Richard stated, the error is telling you that it cannot call forEach on obj because it does not know what obj is because obj is undefined. You'll need to debug your code to figure out what is going on with obj.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900