Hi DRay,
Thanks for your reply!
I have seen the posts, however I'm stuck at the value part. In the posts the value is a certain number/text. However in my case, the text of the whole cell changes. I want to colour it based on the day but the date always changes.
Is it possible to set the value as 'contains Monday' instead of '== Monday'? I tried the latter but the script cannot find the cell since it contains 'monday [date]'.
Hope the question is clear and thanks for your help in advance!
Hi JennyW​ ,
If I understand it correctly, you need to color the Pivot Table's rows based on the content of a date field. If a row contains 'Monday', a certain color should be applied, and if it contains 'Tuesday', a different color should be set.
If that's the case, please try the script provided below. This script will work if the date field is the first column in the table.
const dayColorMapping = {
'sunday': '#9cbff7',
'monday': '#ee9cf7',
'tuesday': '#f79cba',
'wednesday': '#9cebf7',
'thursday': '#abf79c',
'friday': '#f7f49c',
'saturday': '#f79c9c'
}
const weekday = ["sunday","monday","tuesday","wednesday","thursday","friday","saturday"];
const dateColumnIndex = 0;
widget.transformPivot(
{
},
function setCellBackground(metadata, cell) {
if(metadata.rowIndex > 0){
const date = new Date(metadata.rows[dateColumnIndex].member);
const day = weekday[date.getDay()];
if(day in dayColorMapping) {
cell.style.backgroundColor = dayColorMapping[day];
}
}
}
);Result:
Let me know if that works!
- Hari
