below is snapshot where contact columns highlighted as these data in 7 row from snapshot of excel should be under Tenant columns instead of contact columns.
below is script used to get data and push into excel after excel download button is clicked to download data in excel in service portal.
// Get Space users from M2M table
var spaceUserGRs = utils.getCustomerGRsOfApartment({
apartmentID: apartmentGR.getUniqueValue(),
role: 'user',
active: true
});
// Determine the maximum count of space users for alignment purposes
//gs.addInfoMessage('Current spaceUserGRs length: ' + spaceUserGRs.length);
if (spaceUserGRs.length > maxSpaceUsersCount) {
maxSpaceUsersCount = spaceUserGRs.length;
}
// Loop through space users and add their details to the apartmentDetails array
for (var j = 0; j < maxSpaceUsersCount ; j++) {
//gs.addInfoMessage('Message'+j);
if (j < spaceUserGRs.length ) {
// Add space user details under tenant headers
gs.addInfoMessage('spaceUserGRs'+spaceUserGRs[j].getDisplayValue());
apartmentDetails.push(spaceUserGRs[j].getDisplayValue() || " ");
apartmentDetails.push(spaceUserGRs[j].getValue('mobile_phone') || " ");
apartmentDetails.push(spaceUserGRs[j].getValue('email') || " ");
} else {
// If there are fewer space users than the maximum count, add empty strings
apartmentDetails.push(" ");
apartmentDetails.push(" ");
apartmentDetails.push(" ");
}
}
// Add the apartment details row to the Excel
data.excelData.push(apartmentDetails);
}
// Replace OWNER_DATA_PLACEHOLDER, TENANT_DATA_PLACEHOLDER column labels
data.m = {
owner: gs.getMessage('com.ska_wt.contact'),
spaceUser: gs.getMessage('com.ska_wt.space_user'),
email: gs.getMessage('com.ska_wt.email'),
phoneNumber: gs.getMessage('com.ska_wt.phone_number')
};
// Split excelTableHeader: Replace OWNER_DATA_PLACEHOLDER and TENANT_DATA_PLACEHOLDER
var excelTableHeaderArray = excelTableHeader.split(',');
// Generate Excel header labels for owner and tenant data. It could be 1-n
// Use "maxOwnersCount", because this is the number how many columns do we need to have: if one apartment have more owners than the rest
var ownersDetails = [];
var spaceUsersDetails = [];
if (maxOwnersCount > 0) {
for (var i = 1; i <= maxOwnersCount; i++) {
ownersDetails.push(data.m.owner + ' ' + i);
ownersDetails.push(data.m.owner + ' ' + i + ' ' + data.m.phoneNumber);
ownersDetails.push(data.m.owner + ' ' + i + ' ' + data.m.email);
}
}
if (maxSpaceUsersCount > 0) {
for (var j =1; j <= maxSpaceUsersCount; j++) {
gs.addInfoMessage('spacusercount'+j);
spaceUsersDetails.push(data.m.spaceUser + ' ' + j);
spaceUsersDetails.push(data.m.spaceUser + ' ' + j + ' ' + data.m.phoneNumber);
spaceUsersDetails.push(data.m.spaceUser + ' ' + j + ' ' + data.m.email);
}
}
// Construct a new array; it is cleaner to follow
var newExcelTableHeaderArray = [];
newExcelTableHeaderArray.push(excelTableHeaderArray[0]);
newExcelTableHeaderArray.push(excelTableHeaderArray[3]);
newExcelTableHeaderArray.push(excelTableHeaderArray[4]);
if (maxOwnersCount > 0) {
for (var i = 0; i < ownersDetails.length; i++) {
newExcelTableHeaderArray.push(ownersDetails[i]); // Add owner details columns
}
}
if (maxSpaceUsersCount > 0) {
for (var j = 0; j < spaceUsersDetails.length; j++) {
newExcelTableHeaderArray.push(spaceUsersDetails[j]); // Add tenant details columns
}
}
// Then replace the value at excelTableHeaderArrayPosition in the Excel
data.excelData[excelTableHeaderArrayPosition] = newExcelTableHeaderArray.join(",").split(",");
data.columnsLength = data.excelData[excelTableHeaderArrayPosition].length;
// Set Excel table column widths for each column, based on the header row
// Defined in characters, not in pixels.
// You can set this value in the parent widget with options.default_column_width.
// Default value is 20.
data.excelColumnWidths = [];
var excelColumnWidth = input.default_column_width || 20;
for (var i = 0; i < data.columnsLength; i++) {
var columnWidth = {
wch: excelColumnWidth
};
data.excelColumnWidths.push(columnWidth);
}
}
})();
