Tag: Formatting

Why does Google Sheets Conditional Formatting not apply properly to all cells?

Alright, so I’ve been having this issue for a few hours now and am confident to say that I can’t fix it by myself.

I’ve been playing around with Google Sheets trying to apply Conditional Formatting when another referenced cell is not empty.

Here’s an image of the issue, it will definitely help you understand what I’m trying to do.

However, here’s what happens when I do the following two things (using an IF function to determine the state and then parse the true or false state towards the Conditional Formatting rule), here’s where the broken magic comes into play.

When I set it to =IF(ISBLANK(B2), false, true), this happens.
But when I reverse it and set it to =IF(ISBLANK(B2), true, false), this happens again.

I am lost to why this happens as from what you can see in the screenshots, B2 is never empty.

EDIT: For whatever reason, when I set it to =IF(ISBLANK(B2), true, true), it works as expected, as seen here. I would still like to hear an explanation if anybody has one.

list – Sharepoint column formatting carried over in all views

Column formatting is applied regardless of what view it’s used in. It isn’t possible to target a specific view with column formatting. You can, however, do this with view formatting. That may or may not meet your needs, though.

If you have more details about what you’re trying to accomplish, there may be other options.

google sheets – formatting currency to be used in an email

I’m summing a range from a Google Sheet, then putting the results in the body of an email as currency.

But I’m running into formatting issues that seems surprisingly hard to deal with.

The range of the sum can be anywhere from a small amt (e.g. $1.23) to a large amt (e.g. $98,123.45).

I would like the comma to be added when necessary, and the decimal fixed to 2 places.

I found this code from another thread:

var dollars = Utilities.formatString("$%d,%02d%1.2f", totalOfOrders/1000, totalOfOrders%1000/10,totalOfOrders%10);

And that works well when there is supposed to be a comma, but for smaller amts, it still adds the comma.

e.g. if the amt is 981.97, it shows $0,981.97

If the amt is 1981.97, it shows $1,981.97

Here’s the code snippet:


var values = range.getValues();
var totalOfOrders = +sum(values,1);
var dollars = Utilities.formatString("$%d,%02d%1.2f", totalOfOrders/1000, totalOfOrders%1000/10,totalOfOrders%10);

Do I actually need to add something like an if statement to handle sums under 1000.00? Or is there a better way to do this?

sharepoint online – JSON view formatting [$Editor] en [$Modified] not visible

Try this JSON for test (it is working for me):

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
    "hideSelection": true,
    "hideColumnHeader": true,
    "rowFormatter": {
        "elmType": "div",
        "attributes": {
            "class": "sp-row-card"
        },
        "children": (
            {
                "elmType": "div",
                "attributes": {
                    "class": "sp-row-title"
                },
                "txtContent": "($Modified)"
            },
            {
                "elmType": "div",
                "attributes": {
                    "class": "sp-row-title"
                },
                "txtContent": "($Editor.email)"
            }
        )
    }
    
}

Note:

In order to access the Modified By and Modified columns in JSON, it should be included in the list view.

You can include the column in list view from view settings like:

enter image description here

official documentations:

  1. Use view formatting to customize SharePoint
  2. Use column formatting to customize SharePoint – You can find supported functions and guidelines for JSON formatting in this link

The people field object has the following properties (with example values):

{
   "id": "122",
   "title": "Kalya Tucker",
   "email": "kaylat@contoso.com",
   "sip": "kaylat@contoso.com",
   "picture": "https://contoso.sharepoint.com/kaylat_contoso_com_MThumb.jpg?t=63576928822",
   "department":"Human Resources",
   "jobTitle":"HR Manager"
}

Also, to convert the date columns in other formats, you can use toLocaleDateString() or toLocaleString() functions.

Excel cell formatting not working for some cells when trying to write in paragraphs

I have an excel sheet and I want to write content in a cell. There are around 2-3 paragraphs and bullet points. When I using the cell formatting feature, it works properly. But once I closed that file, it get back to the default condition. I am attaching both conditions here.enter image description here

excel – Apply general conditional formatting for all cells in worksheet

I’ll explain my plan. With Java I’m creatting formatted tables, each one is different in columns and rows, but all of them need a conditional formatting which is, if cell value is a date, and this date is less than today, print that date in red.

What I’m trying to use is a general .xltx blank template, with a general conditional formatting for all the worksheet (this is my nightmare), and every time I use this file to paste a table from Java, this conditional should be applied.

Is that even posible or I’m just drunk? Thanks guys!