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){
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();
if(lockingDate >= startDate && lockingDate <= endDate){
console.log("date:" + item.Action_Date)
return item;
}
});
records = pendingRecords;
console.log("records length: " + records.length);
pushPendingActions(records, name);
pullInquiries();
}
function pushPendingActions(records, name){
console.log("pushing records")
sheet = ss.getSheetByName(""+name+ " - Validations");
if (sheet){
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);
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}
}
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);
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]);
}
}
}
return dataRecords;
}
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){
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;
}