We have one custom button in service portal to download data in excel which is having mismatched data in columns of excel this is pure custom widget script can anyone please help.
as below highlighted columns contact1 and 2 having data so there are columns named tenant in excel this highlighted7th row data should be under the tenant columns.
below is the script related to this.
// 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);
}
}
})();
