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();
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 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"}
var validationStartDate = new Date("" + (currentYear) + "-"+appendZero(months[month])+"-14")
console.log("start date: "+startDate + ", end date: "+endDate);
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);
pushPendingActions(records, name);
pullInquiries(creds, campaignID);
}
function pushPendingActions(records, name){
console.log("pushing records")
sheet = ss.getSheetByName("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, 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}
}
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);
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]);
}
}
}
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;
}
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){
ui.createAddonMenu()
.addItem("setFilter","form")
.addItem("resetFilter","resetFilter")
.addToUi();
};
function form(){
var html = HtmlService.createHtmlOutputFromFile("formUI")
.setWidth(450)
.setHeight(300);
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";
var sheet = ss.getSheets()[0];
var range = sheet.getRange(setCol + "1");
var setColIndex = range.getColumn() - 1;
var ks = str.split("\n");
var text = '=($' + setCol + ':$' + setCol + '="';
var i;
for (i = 0; i < ks.length - 1; i++) {
text += ks[i] + '")+($' + setCol + ':$' + setCol + '="';
}
text += ks[ks.length - 1] + '")';
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();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": lastColumn
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}