How do I create a function to batch upload actions?

See more:

Javascript

Google

google-api

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!

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