var print = Logger.log;
var webEngageLicense = '~2024b5d8';
var usersUrl = "https://api.webengage.com/v1/accounts/" + webEngageLicense + "/bulk-users";
var eventsUrl = "https://api.webengage.com/v1/accounts/" + webEngageLicense + "/bulk-events";
var headers = {
"Authorization": "Bearer 426a654c-89b6-4595-af9c-fd3712af56f9",
"Content-Type": "application/json"
}
/////////////////////////////////// MENU BUTTONS ///////////////////////////////////
// function onOpen() {
// var ui = SpreadsheetApp.getUi();
// var add_modules = ui.createMenu("USERS_DATA");
// add_modules.addItem('TEST', 'users_to_WE_TEST').addToUi();
// add_modules.addItem('UPSC FORM RESP', 'users_to_WE_UPSC_FORM_RESP').addToUi();
// }
/////////////////////////////////// MAP KEYS ///////////////////////////////////
function map_keys(sheetName) {
var mapper_keys = {}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ActiveSheet = ss.getSheetByName(sheetName);
var lastColumn = ActiveSheet.getLastColumn()
var headers = ActiveSheet.getRange(1, 1, 1, lastColumn).getValues()[0]
for (var k = 0; k < lastColumn; k++) {
mapper_keys[headers[k]] = k
}
return (mapper_keys)
}
/////////////////////////////////// CALL API ///////////////////////////////////
function callApi(targetURL, method, payload, headers) {
var options = {
'method': method,
'payload': payload,
'headers': headers
}
try {
var response = UrlFetchApp.fetch(targetURL, options);
} catch (e) {
// print(e)
return [e, 0]
}
var respText = JSON.parse(response.getContentText());
var respStatus = response.getResponseCode();
return [respText, respStatus]
}
///////////////////////////////////////////// GENERATE USER ID /////////////////////////////////////////////
function generateUserId(row, prefix, startCounter) {
// var prefix = "fmp_gf_";
// var startCounter = 198230;
var zeros = "000000";
var idNumber = (startCounter + row - 1).toString()
var uniqueUserId = prefix + (zeros + idNumber).slice(-7);
return uniqueUserId;
}
//////////////////////////////////////// USER FETCHING FROM RESPONSE SHEETs ////////////////////////////////////////
function fetch_users_from_sheet(sheetName) {
var sheetConfigurations = {
"UPSC_FORM_1": {
url: "https://docs.google.com/spreadsheets/d/1a0AglRKvK8aqee1wWZnpkQDGQXLtMa9Gy3g7EgjgzSI/edit?resourcekey#gid=1768755200",
shName: "Form Responses 1",
startRow: 1,
destinationSheetName: "Users_to_WE UPSC_FORM_1",
emailIdx: 1,
phoneIdx: 3,
nameIdx: 6,
prefix: "fmp_upsc1_",
startCounter: 000000,
},
"UPSC_FORM_2": {
url: "https://docs.google.com/spreadsheets/d/1a0AglRKvK8aqee1wWZnpkQDGQXLtMa9Gy3g7EgjgzSI/edit?resourcekey#gid=1244699291",
shName: "google_form_leads",
startRow: 1,
destinationSheetName: "Users_to_WE UPSC_FORM_2",
emailIdx: 1,
phoneIdx: 2,
nameIdx: 0,
prefix: "fmp_upsc2_",
startCounter: 000000,
},
"JUDI_FORM": {
url: "https://docs.google.com/spreadsheets/d/1DZ9hwfJiW5-6JckwicpaVeGotgLEYm6RVScs2GhnXIk/edit#gid=0",
shName: "Sheet1",
startRow: 1,
destinationSheetName: "Users_to_WE JUDI_FORM",
emailIdx: 1,
phoneIdx: 2,
nameIdx: 0,
prefix: "fmp_jud1_",
startCounter: 000000,
},
};
var sheetConfig = sheetConfigurations[sheetName];
var sourceSheet = SpreadsheetApp.openByUrl(sheetConfig.url).getSheetByName(sheetConfig.shName);
var lastRow = sourceSheet.getLastRow();
var numRows = lastRow - sheetConfig.startRow + 1;
var sourceData = sourceSheet.getRange(sheetConfig.startRow, 1, numRows, sourceSheet.getLastColumn()).getValues();
var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetConfig.destinationSheetName);
var destinationData = destinationSheet.getDataRange().getValues();
if (destinationData.length < sourceData.length) {
var newRows = sourceData.slice(destinationData.length);
for (var i = 0; i < newRows.length; i++) {
var row = destinationData.length + i + 1;
var email = String(newRows[i][sheetConfig.emailIdx]);
var phone = String(newRows[i][sheetConfig.phoneIdx]);
phone = (phone.length === 10) ? "91" + phone : phone;
var name = String(newRows[i][sheetConfig.nameIdx]);
if (email != "#ERROR!" && phone != "#ERROR!" && name != "#ERROR!" && phone.length == 12) {
var uniqueUserId = generateUserId(row, sheetConfig.prefix, sheetConfig.startCounter);
destinationSheet.getRange(row, 1).setValue(uniqueUserId);
destinationSheet.getRange(row, 2).setValue(email);
destinationSheet.getRange(row, 3).setValue(phone);
destinationSheet.getRange(row, 4).setValue(name);
destinationSheet.getRange(row, 5).setValue("TRUE");
}
}
}
}
///////////////////////////////////////// USERS API - UPSC_FORM_1 /////////////////////////////////////////
function users_to_WE_UPSC1() {
var mapper_keys = map_keys('Users_to_WE UPSC_FORM_1')
SpreadsheetApp.flush()
fetch_users_from_sheet("UPSC_FORM_1");
var webEngageSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users_to_WE UPSC_FORM_1');
var StartRow = 2;
var RowRange = webEngageSheet.getLastRow() - StartRow + 1;
var WholeRange = webEngageSheet.getRange(StartRow, 1, RowRange, 6);
var AllValues = WholeRange.getDisplayValues();
var userList = [];
var batchSize = 25;
var userIndexList = [];
var errorUserList = [];
for (var idx in AllValues) {
var currentRow = AllValues[idx];
var status = currentRow[mapper_keys['status']];
if (status !== "Success" && status !== "Error" && status !== "User Integrated") {
var user_id = String(currentRow[mapper_keys['user_id']]);
var email = String(currentRow[mapper_keys['email']]);
var mobile = String(currentRow[mapper_keys['phone']]);
var name = String(currentRow[mapper_keys['first_name']]);
if (email != "#ERROR!" && mobile != "#ERROR!" && name != "#ERROR!" && mobile.length == 12) {
var user = {
'userId': user_id,
'email': email,
'firstName': name,
'phone': mobile,
'whatsappOptIn': true,
};
userList.push(user);
userIndexList.push(idx);
if (userList.length === batchSize) {
var payload = { "users": userList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(usersUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
var userList = [];
var userIndexList = [];
}
if (idx % 50 === 0) {
print("Delay Added: 5 Sec");
Utilities.sleep(5000);
}
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('User Integrated');
}
else {
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Error');
}
}
}
if (userList.length > 0) {
var payload = { "users": userList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(usersUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
}
if (errorUserList.length > 0) {
print(errorUserList);
for (var e in errorUserList) {
webEngageSheet.getRange(parseInt(errorUserList[e]) + StartRow, mapper_keys['status'] + 1).setValue('Failed User Api');
}
}
print("Done User Integration - UPSC_FORM_1");
}
////////////////////////////////////////////////// EVENTS API - UPSC_FORM_1 //////////////////////////////////////////////////
function users_to_events_UPSC1() {
var mapper_keys = map_keys('Users_to_WE UPSC_FORM_1')
SpreadsheetApp.flush()
var webEngageSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users_to_WE UPSC_FORM_1');
var StartRow = 2;
var RowRange = webEngageSheet.getLastRow() - StartRow + 1;
var WholeRange = webEngageSheet.getRange(StartRow, 1, RowRange, 6);
var AllValues = WholeRange.getDisplayValues();
var eventName = "UPSC_FA";
var eventUserList = [];
var batchSize = 25;
var userIndexList = [];
var errorUserList = [];
for (var idx in AllValues) {
var currentRow = AllValues[idx];
var status = currentRow[mapper_keys['status']];
if (status === "User Integrated") {
var user_id = String(currentRow[mapper_keys['user_id']]);
if (user_id) {
var eventUser = {
"userId": user_id,
"eventName": eventName
};
eventUserList.push(eventUser);
userIndexList.push(idx);
if (eventUserList.length === batchSize) {
var payload = { "events": eventUserList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(eventsUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
var eventUserList = [];
var userIndexList = [];
}
if (idx % 50 === 0) {
print("Delay Added: 5 Sec");
Utilities.sleep(5000);
}
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Success');
}
else {
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Error');
}
}
}
if (eventUserList.length > 0) {
var payload = { "events": eventUserList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(eventsUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
}
if (errorUserList.length > 0) {
print(errorUserList);
for (var e in errorUserList) {
webEngageSheet.getRange(parseInt(errorUserList[e]) + StartRow, mapper_keys['status'] + 1).setValue('Failed Event Api');
}
}
print("Done User To Event - UPSC_FORM_1")
}
////////////////////////////////////////////////// USERS API - UPSC_FORM_2 //////////////////////////////////////////////////
function users_to_WE_UPSC2() {
var mapper_keys = map_keys('Users_to_WE UPSC_FORM_2')
SpreadsheetApp.flush()
fetch_users_from_sheet("UPSC_FORM_2");
var webEngageSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users_to_WE UPSC_FORM_2');
var StartRow = 2;
var RowRange = webEngageSheet.getLastRow() - StartRow + 1;
var WholeRange = webEngageSheet.getRange(StartRow, 1, RowRange, 6);
var AllValues = WholeRange.getDisplayValues();
var userList = [];
var batchSize = 25;
var userIndexList = [];
var errorUserList = [];
for (var idx in AllValues) {
var currentRow = AllValues[idx];
var status = currentRow[mapper_keys['status']];
if (status !== "Success" && status !== "Error" && status !== "User Integrated") {
var user_id = String(currentRow[mapper_keys['user_id']]);
var email = String(currentRow[mapper_keys['email']]);
var mobile = String(currentRow[mapper_keys['phone']]);
var name = String(currentRow[mapper_keys['first_name']]);
if (email != "#ERROR!" && mobile != "#ERROR!" && name != "#ERROR!" && mobile.length == 12) {
var user = {
'userId': user_id,
'email': email,
'firstName': name,
'phone': mobile,
'whatsappOptIn': true,
};
userList.push(user);
userIndexList.push(idx);
if (userList.length === batchSize) {
var payload = { "users": userList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(usersUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
var userList = [];
var userIndexList = [];
}
if (idx % 50 === 0) {
print("Delay Added: 5 Sec");
Utilities.sleep(5000);
}
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('User Integrated');
}
else {
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Error');
}
}
}
if (userList.length > 0) {
var payload = { "users": userList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(usersUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
}
if (errorUserList.length > 0) {
print(errorUserList);
for (var e in errorUserList) {
webEngageSheet.getRange(parseInt(errorUserList[e]) + StartRow, mapper_keys['status'] + 1).setValue('Failed User Api');
}
}
print("Done User Integration - UPSC_FORM_2");
}
////////////////////////////////////////////////// EVENTS API - UPSC_FORM_2 //////////////////////////////////////////////////
function users_to_events_UPSC2() {
var mapper_keys = map_keys('Users_to_WE UPSC_FORM_2')
SpreadsheetApp.flush()
var webEngageSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users_to_WE UPSC_FORM_2');
var StartRow = 2;
var RowRange = webEngageSheet.getLastRow() - StartRow + 1;
var WholeRange = webEngageSheet.getRange(StartRow, 1, RowRange, 6);
var AllValues = WholeRange.getDisplayValues();
var eventName = "UPSC_FA";
var eventUserList = [];
var batchSize = 25;
var userIndexList = [];
var errorUserList = [];
for (var idx in AllValues) {
var currentRow = AllValues[idx];
var status = currentRow[mapper_keys['status']];
if (status === "User Integrated") {
var user_id = String(currentRow[mapper_keys['user_id']]);
if (user_id) {
var eventUser = {
"userId": user_id,
"eventName": eventName
};
eventUserList.push(eventUser);
userIndexList.push(idx);
if (eventUserList.length === batchSize) {
var payload = { "events": eventUserList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(eventsUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
var eventUserList = [];
var userIndexList = [];
}
if (idx % 50 === 0) {
print("Delay Added: 5 Sec");
Utilities.sleep(5000);
}
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Success');
}
else {
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Error');
}
}
}
if (eventUserList.length > 0) {
var payload = { "events": eventUserList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(eventsUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
}
if (errorUserList.length > 0) {
print(errorUserList);
for (var e in errorUserList) {
webEngageSheet.getRange(parseInt(errorUserList[e]) + StartRow, mapper_keys['status'] + 1).setValue('Failed Event Api');
}
}
print("Done User To Event - UPSC_FORM_2")
}
////////////////////////////////////////////////// USERS API - UPSC_FORM_2 //////////////////////////////////////////////////
function users_to_WE_JUDI() {
var mapper_keys = map_keys('Users_to_WE JUDI_FORM')
SpreadsheetApp.flush()
fetch_users_from_sheet("JUDI_FORM");
var webEngageSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users_to_WE JUDI_FORM');
var StartRow = 2;
var RowRange = webEngageSheet.getLastRow() - StartRow + 1;
var WholeRange = webEngageSheet.getRange(StartRow, 1, RowRange, 6);
var AllValues = WholeRange.getDisplayValues();
var userList = [];
var batchSize = 25;
var userIndexList = [];
var errorUserList = [];
for (var idx in AllValues) {
var currentRow = AllValues[idx];
var status = currentRow[mapper_keys['status']];
if (status !== "Success" && status !== "Error" && status !== "User Integrated") {
var user_id = String(currentRow[mapper_keys['user_id']]);
var email = String(currentRow[mapper_keys['email']]);
var mobile = String(currentRow[mapper_keys['phone']]);
var name = String(currentRow[mapper_keys['first_name']]);
if (email != "#ERROR!" && mobile != "#ERROR!" && name != "#ERROR!" && mobile.length == 12) {
var user = {
'userId': user_id,
'email': email,
'firstName': name,
'phone': mobile,
'whatsappOptIn': true,
};
userList.push(user);
userIndexList.push(idx);
if (userList.length === batchSize) {
var payload = { "users": userList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(usersUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
var userList = [];
var userIndexList = [];
}
if (idx % 50 === 0) {
print("Delay Added: 5 Sec");
Utilities.sleep(5000);
}
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('User Integrated');
}
else {
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Error');
}
}
}
if (userList.length > 0) {
var payload = { "users": userList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(usersUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
}
if (errorUserList.length > 0) {
print(errorUserList);
for (var e in errorUserList) {
webEngageSheet.getRange(parseInt(errorUserList[e]) + StartRow, mapper_keys['status'] + 1).setValue('Failed User Api');
}
}
print("Done User Integration - JUDI_FORM");
}
////////////////////////////////////////////////// EVENTS API - JUDI_FORM //////////////////////////////////////////////////
function users_to_events_JUDI() {
var mapper_keys = map_keys('Users_to_WE JUDI_FORM')
SpreadsheetApp.flush()
var webEngageSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users_to_WE JUDI_FORM');
var StartRow = 2;
var RowRange = webEngageSheet.getLastRow() - StartRow + 1;
var WholeRange = webEngageSheet.getRange(StartRow, 1, RowRange, 6);
var AllValues = WholeRange.getDisplayValues();
var eventName = "JUDI_FA";
var eventUserList = [];
var batchSize = 25;
var userIndexList = [];
var errorUserList = [];
for (var idx in AllValues) {
var currentRow = AllValues[idx];
var status = currentRow[mapper_keys['status']];
if (status === "User Integrated") {
var user_id = String(currentRow[mapper_keys['user_id']]);
if (user_id) {
var eventUser = {
"userId": user_id,
"eventName": eventName
};
eventUserList.push(eventUser);
userIndexList.push(idx);
if (eventUserList.length === batchSize) {
var payload = { "events": eventUserList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(eventsUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
var eventUserList = [];
var userIndexList = [];
}
if (idx % 50 === 0) {
print("Delay Added: 5 Sec");
Utilities.sleep(5000);
}
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Success');
}
else {
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Error');
}
}
}
if (eventUserList.length > 0) {
var payload = { "events": eventUserList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(eventsUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
}
if (errorUserList.length > 0) {
print(errorUserList);
for (var e in errorUserList) {
webEngageSheet.getRange(parseInt(errorUserList[e]) + StartRow, mapper_keys['status'] + 1).setValue('Failed Event Api');
}
}
print("Done User To Event - JUDI_FORM")
}
/////////////////////////////////////////////////////////////////////////////// TESTING ///////////////////////////////////////////////////////////////////////////////
function fetchUserData_TEST() {
var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ResponseSheet TEST");
var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Users_to_WE TEST");
var sourceData = sourceSheet.getDataRange().getValues();
var destinationData = destinationSheet.getDataRange().getValues();
if (destinationData.length < sourceData.length) {
var newRows = sourceData.slice(destinationData.length);
for (var i = 0; i < newRows.length; i++) {
var row = destinationData.length + i + 1;
var email = String(newRows[i][1]);
var phone = String(newRows[i][2]);
phone = (phone.length === 10) ? "91" + phone : phone;
var name = String(newRows[i][3]);
if (email != "#ERROR!" && phone != "#ERROR!" && name != "#ERROR!" && phone.length == 12) {
var prefix = "test_1_"
var startCounter = 400000
var uniqueUserId = generateUserId(row, prefix, startCounter);
destinationSheet.getRange(row, 1).setValue(uniqueUserId);
destinationSheet.getRange(row, 2).setValue(email);
destinationSheet.getRange(row, 3).setValue(phone);
destinationSheet.getRange(row, 4).setValue(name);
destinationSheet.getRange(row, 5).setValue("TRUE");
}
}
}
print("Done Test Fetching!");
}
////////////////////////////////////////////////// USERS API - UPSC_FORM_2 //////////////////////////////////////////////////
function users_to_WE_TEST() {
var mapper_keys = map_keys('Users_to_WE TEST')
SpreadsheetApp.flush()
fetchUserData_TEST();
var webEngageSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users_to_WE TEST');
var StartRow = 2;
var RowRange = webEngageSheet.getLastRow() - StartRow + 1;
var WholeRange = webEngageSheet.getRange(StartRow, 1, RowRange, 6);
var AllValues = WholeRange.getDisplayValues();
var userList = [];
var batchSize = 25;
var userIndexList = [];
var errorUserList = [];
for (var idx in AllValues) {
var currentRow = AllValues[idx];
var status = currentRow[mapper_keys['status']];
if (status !== "Success" && status !== "Error" && status !== "User Integrated") {
var user_id = String(currentRow[mapper_keys['user_id']]);
var email = String(currentRow[mapper_keys['email']]);
var mobile = String(currentRow[mapper_keys['phone']]);
var name = String(currentRow[mapper_keys['first_name']]);
if (email != "#ERROR!" && mobile != "#ERROR!" && name != "#ERROR!" && mobile.length == 12) {
var user = {
'userId': user_id,
'email': email,
'firstName': name,
'phone': mobile,
'whatsappOptIn': true,
};
userList.push(user);
userIndexList.push(idx);
if (userList.length === batchSize) {
var payload = { "users": userList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(usersUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
var userList = [];
var userIndexList = [];
}
if (idx % 50 === 0) {
print("Delay Added: 5 Sec");
Utilities.sleep(5000);
}
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('User Integrated');
}
else {
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Error');
}
}
}
if (userList.length > 0) {
var payload = { "users": userList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(usersUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
}
if (errorUserList.length > 0) {
print(errorUserList);
for (var e in errorUserList) {
webEngageSheet.getRange(parseInt(errorUserList[e]) + StartRow, mapper_keys['status'] + 1).setValue('Failed User Api');
}
}
print("Done User Integration - TEST");
}
////////////////////////////////////////////////// EVENTS API - JUDI_FORM //////////////////////////////////////////////////
function users_to_events_TEST() {
var mapper_keys = map_keys('Users_to_WE TEST')
SpreadsheetApp.flush()
var webEngageSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users_to_WE TEST');
var StartRow = 2;
var RowRange = webEngageSheet.getLastRow() - StartRow + 1;
var WholeRange = webEngageSheet.getRange(StartRow, 1, RowRange, 6);
var AllValues = WholeRange.getDisplayValues();
var eventName = "Testing User Event";
var eventUserList = [];
var batchSize = 25;
var userIndexList = [];
var errorUserList = [];
for (var idx in AllValues) {
var currentRow = AllValues[idx];
var status = currentRow[mapper_keys['status']];
if (status === "User Integrated") {
var user_id = String(currentRow[mapper_keys['user_id']]);
if (user_id) {
var eventUser = {
"userId": user_id,
"eventName": eventName
};
eventUserList.push(eventUser);
userIndexList.push(idx);
if (eventUserList.length === batchSize) {
var payload = { "events": eventUserList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(eventsUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
var eventUserList = [];
var userIndexList = [];
}
if (idx % 50 === 0) {
print("Delay Added: 5 Sec");
Utilities.sleep(5000);
}
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Success');
}
else {
webEngageSheet.getRange(parseInt(idx) + StartRow, mapper_keys['status'] + 1).setValue('Error');
}
}
}
if (eventUserList.length > 0) {
var payload = { "events": eventUserList }
payload = JSON.stringify(payload);
var [uploadResponse, respStatus] = callApi(eventsUrl, "post", payload, headers);
if (respStatus != 201) {
print(payload);
print(respStatus);
errorUserList = errorUserList.concat(...userIndexList);
}
}
if (errorUserList.length > 0) {
print(errorUserList);
for (var e in errorUserList) {
webEngageSheet.getRange(parseInt(errorUserList[e]) + StartRow, mapper_keys['status'] + 1).setValue('Failed Event Api');
}
}
print("Done User To Event - TEST")
}
