How to change SharePoint DataSheet View cell background-color

Hi,
afaik you have two different options:
1. Using JSLink
2. Using custom Javascript via Script Editor on the view page

With JSLink you can overwrite how the content of the cell is written. The below sample will set the span background color (but limited to the span, not the whole cell). Benefit here is, that you have direct access to the item values (the code is run for each item).

(function () {
var overrideContext = {};
overrideContext.Templates = {};
overrideContext.Templates.Fields =
{
   'Status': { 'View': overrideTemplate }
};
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideContext);

})();

function overrideTemplate(ctx) {
   var bgColor = "#FFFFFF"
   var status = ctx.CurrentItem.Status;
   var dueDate = new Date(ctx.CurrentItem.Due_x0020_Date);
   var itemType = ctx.CurrentItem.TaskType;
   var today = new Date();
   if (status == 'Pending' && itemType == 'Internal' && dueDate < today)
   {
      bgColor = "red";
   }

   return "<span style='background-color: " + bgColor + "';>" + status + "</span>";
}

Select allOpen in new window

Just save the above script (after adjusting the column names to your list internal names (!!!)), upload it into your site assets library (e.g. MyCustomBackground.js) and then

1. edit the page of your datasheet view,
2. edit the webpart
3. in the Miscellaneous section set the JS Link value to target your uploaded file - eg.  ~site/SiteAssets/MyCustomBackground.js)

With custom JavaScript (e.g. via jQuery) you can easily set the background colors of the cells BUT it will be somehow harder to get the item values for each row.

HTH
Rainer

thanks. but this appears to color the text. I need the entire cell rectangle color coded like you can do in excel. it's a migration from an excel sheet to a sp list

Hi,
needed some time but here we go:

• Either create a CSS file with your custom class (for the red background and/or font type / color) or reuse one class you already have in your solution
• Upload the file to the site assets library of your site collection
• Upload jQuery JS file to your site assets library (if not yet included in your custom master page etc)
• Update the JSLink JS file with the below code (after e.g. class name adjustments etc)
• Adjust the JS Link of the data sheet view webpart to include both JS files:
  ~sitecollection/SiteAssets/jquery-3.6.0.js|~site/SiteAssets/ee_CustomBackgroundDsv.js

The code is adding jQuery and the CSS file if both are not yet included. It should also work when MDS is enabled.
Logic is:
- Each item is setting a specific class name to the cell content span.
- Afterwards jQuery is used to get for each span with the related class the closest TD and set a new class there (which will then influence the background color).

Sample Code:

Type.registerNamespace('EE')
EE.Templates = EE.Templates || {}
EE.Functions = EE.Functions || {}

EE.Functions.AddJQuery = function (ctx) {
    // check if jQuery is available, otherwise reload it
    if (!window.jQuery) {
        var jq = document.createElement('script'); 
      jq.type = 'text/javascript';
        jq.src = _spPageContextInfo.webAbsoluteUrl + '/SiteAssets/jquery-3.6.0.js';
        document.getElementsByTagName('head')[0].appendChild(jq);
    }
   // Verify that custom CSS class is loaded from Site Assets
   var cssId = 'eeDsv';   
   if (!document.getElementById(cssId))
   {
      var head  = document.getElementsByTagName('head')[0];
      var link  = document.createElement('link');
      link.id   = cssId;
      link.rel  = 'stylesheet';
      link.type = 'text/css';
      link.href = _spPageContextInfo.webAbsoluteUrl + '/SiteAssets/eedsv.css';
      link.media = 'all';
      head.appendChild(link);
   }
}
 
EE.Functions.InitiateScopeList = function () {
    //Add jqery and links
    EE.Functions.AddJQuery(ctx);
}

EE.Functions.ChangeFieldColor = function () {
   jQuery(".redback").closest('td').addClass('tdredback');
   jQuery(".unused").closest('td').removeClass('tdredback');
}

EE.OnPreRender = EE.Functions.InitiateScopeList;
EE.OnPostRender = EE.Functions.ChangeFieldColor;


EE.Functions.OverrideStatusField = function (ctx) {
   var status = ctx.CurrentItem.Status;
   var dueDate = new Date(ctx.CurrentItem.Due_x0020_Date);
   var itemType = ctx.CurrentItem.TaskType;
   var today = new Date();
   var dummyClass = "unused"
   if (status == 'Pending' && itemType == 'Internal' && dueDate < today)
   {
      dummyClass = "redback";
   }

   return "<span class='" + dummyClass + "'>" + status + "</span>";
}

EE.Templates.Fields = {
'Status': { 'View': EE.Functions.OverrideStatusField }
};
 
/*
This function has been used to register the namespace as per Minimal Download Strategy(MDS)
*/
EE.Functions.RegisterField = function () {
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(EE)
}
 
/*
This function has been used to register the JS file as per Minimal Download Strategy(MDS)
*/
EE.Functions.MdsRegisterField = function () {
    var thisUrl = _spPageContextInfo.webServerRelativeUrl + '/SiteAssets/ee_CustomBackgroundDsv.js';
    EE.Functions.RegisterField();
    RegisterModuleInit(thisUrl, EE.Functions.RegisterField)
}
 
/*
This code block used to find Minimal Download Strategy(MDS) enabled on the site or not
>on the basis of that it's registring the JS file
*/
if (typeof _spPageContextInfo != "undefined" && _spPageContextInfo != null) {
    //-- MDS enabled on our site
    EE.Functions.MdsRegisterField()
} else {
    //-- MDS no enabled on our site
    EE.Functions.RegisterField()
}

Select allOpen in new window

Sample screenshot:

HTH and happy new year
Rainer

I will try that. thanks so much.

I have it almost working but a little confused.
I've done a lot of DOM work in JS but JSLINK is all new.
Do I just need the JSLINK above posted most recently or do I need it and also a JS insert via CWEP like I normally do?
I see the jquery closest("td') which I've used before but it's not taking when I do an inspect.
Any ideas?
I even recreated the list - using exactly the internal names you used in your example so there would be no doubt.

Very helpful - we load jquery here in the master and I had added the styles to the other styles on the page.
I use the chrome dev tools and breakpoints so will look at that as some typo, etc.  
Thanks - I'll post when this works - which I hope will be soon.  My customer is going to flip on this as they just love the shading in Excel and don't want to give that up .  Also I added color: white to its more readable, etc.

Yeah  Thanks to you  - success.
I tried using the JSlink approach but it just wouldn't load the file  - I carefully checked the link ~site/siteassets ... with .js
no joy.
So I put it all in a CWEP with some other code and the styles - after a little editing, it worked terrfic.
For some reason, it thought I was using minimal download strategy, which we never use due to doing JS behind the scenes and a terrific free tool called SharePoint Easy Forms (SPEF) by Joe McShea - highly recommend for classic experience.  I added other colors for internal/external, coming up soon, etc.
I'll update it a bit to be more transportable to other applications.
Thanks so very much for the detailed code, quick follow through on a question, etc.
 


 

You are welcome, glad that you could use my blueprint.