Click here to Skip to main content
15,441,149 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi Everyone!

So I created this code to validate some data on Google Sheets.

Now I need to create some functions to create conversions/actions via an API for when I need to batch uploaded actions that weren't tracked for some reason.

I will need the conversion endpoint: https://integrations.impact.com/impact-brand/reference#submit-a-conversion

Is there anyone who could help me with the code? I'm feeling a bit stuck tbh.

Thanks very much!

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')
      .addItem('Reverse Actions', 'reverseActions')
      .addToUi();

};

function pullPendingActions(){

  var months = {
    "January": 1,
    "February": 2,
    "March": 3,
    "April": 4,
    "May": 5,
    "June": 6,
    "July": 7,
    "August": 8,
    "September": 9,
    "October": 10,
    "November": 11,
    "December": 12
  }


  var currentDate = new Date();
  var currentYear = currentDate.getYear() + 1900;

  var sheetName = ss.getName().split(" ");
  var hyphen = (element) => element == "-"
  var hyphIndex = sheetName.findIndex(hyphen)
  console.log(hyphIndex)
  var month = sheetName[hyphIndex+1];
  var name = sheetName[0];

  // 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);

  // start date and end date for API call. Needs to be 365 days or less which is why I subtract a year from end date. 
  var endDate;
  var startDate = "" + (currentYear-1) + "-"+appendZero(months[month]+1)+"-06"
  if (month == "December"){ endDate = "" + (currentYear) + "-"+appendZero(months["January"])+"-04" }
  else { endDate = "" + (currentYear) + "-"+appendZero(months[month]+1)+"-04"}

  // start date for validations filter
  var validationStartDate = new Date("" + (currentYear) + "-"+appendZero(months[month])+"-14")

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

  campaignID = "8511"
  creds = "IRdxH3tXsxc71251785LpBrcdRC8ZMymx1:3XRS3sXUWKXuzTQFDKqU.ZmyxUkLCP_w";

  if (!(ss.getSheetByName("Validations"))){
    ss.insertSheet("Validations");    
    var sheet = ss.getSheetByName("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("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 = validationStartDate.getTime();
    var validationsEndDate = new Date(endDate);
    validationsEndDate = validationsEndDate.getTime();
    console.log("locking date: " + lockingDate + " startDate: " + startDate + " endDate: "+ validationsEndDate);
    if(lockingDate >= startDate && lockingDate <= validationsEndDate){
      console.log("date:" + item.Action_Date)
      return item;
    }
  });

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

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



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("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, creds){

  console.log("getting data");
              
  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);

  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;

      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;
}

function appendZero(month){
  if (month < 10){
    return "0" + month
  }
  else return month
}


What I have tried:

This is what I have tried so far however I am getting some errors. Any suggestions?

var ui = SpreadsheetApp.getUi();


function onOpen(e){
  // Create menu options
  ui.createAddonMenu()
      .addItem("setFilter","form")
      .addItem("resetFilter","resetFilter")
    .addToUi();
};


function form(){ 
  //Call the HTML file and set the width and height
  var html = HtmlService.createHtmlOutputFromFile("formUI")
    .setWidth(450)
    .setHeight(300);
  
  //Display the dialog
  var dialog = ui.showModalDialog(html, "Вставьте данные");
 
};


function setFilter(str) {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var dataSheet = ss.getActiveSheet();
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();
  
  var setCol = "A";
  
  //Find Column Index by Column Letters
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange(setCol + "1");
  var setColIndex = range.getColumn() - 1;
  
  //Get text from input in HTML form and split it by lines
  var ks = str.split("\n");
 
  //Build formula like this "=(BU:BU="89650322371")+(BU:BU="89817073385")+..."
  var text = '=($' + setCol + ':$' + setCol + '="';
  var i;
  for (i = 0; i < ks.length - 1; i++) {
    text += ks[i] + '")+($' + setCol + ':$' + setCol + '="';
  }  
  text += ks[ks.length - 1] + '")';  
  //SpreadsheetApp.getUi().alert(text);
  
  //Set filter
  var filterSettings = {};
  
  filterSettings.range = {
    "sheetId": sheetId,
    "startRowIndex": 1,
    "endRowIndex": lastRow,
    "startColumnIndex": 0,
    "endColumnIndex": lastColumn
  };
 
  var filterCriteria = {
    "type":"CUSTOM_FORMULA",
    "values": [
      {
        "userEnteredValue": text
      }
    ]
  };
  
  filterSettings.criteria = {};
  filterSettings['criteria'][setColIndex] = {
    'condition': filterCriteria
  };
  
  var requests = [{
    "setBasicFilter": {
      "filter": filterSettings
    }
  }];

  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}


function resetFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var dataSheet = ss.getActiveSheet();
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();
  
  //Reset filter
  var filterSettings = {
    "range": {
      "sheetId": sheetId,
      "startRowIndex": 1,
      "endRowIndex": lastRow,
      "startColumnIndex": 0,
      "endColumnIndex": lastColumn
    }
  };
  
  var requests = [{
    "setBasicFilter": {
      "filter": filterSettings
    }
  }];
  
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);  
}
Posted
Updated 25-Jul-21 6:46am
v2

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