sharepoint online – Power Apps custom form question on formatting

I’ve designed a custom input form for a Sharepoint list in Power Apps, and I had two questions on formatting:

  1. My formatting in preview mode looks fine, however once published in the Sharepoint list itself the field heights, text positioning, etc. is changed. Any idea how I can ensure that the formatting of the form remains consistent from Power Apps to Sharepoint? For reference, the formatting is shown below.

  2. Upon opening the form in Sharepoint, certain fields (see Issue Type, Priority, Status in the images below) in the form are highlighted/selected without any action on my end – any thoughts on how to remove this?

Custom form in Power Apps previewer:

enter image description here

Custom form in Sharepoint:

enter image description here

Thanks in advance!

json formatting – Lists: Show image overlay on mouse over

This might not be exactly what you need, but here is a good start.

In order to display a custom hover card, you need to use customCardProps element. See more details here. It has the following properties:

openOnEvent

  • hover – card appears on mouse hover;
  • click – card appears on click on the field.

directionalHint

  • topCenter bottomCenter leftCenter rightCenter
  • isBeakVisible – boolean. if true the card has a beak.

How to display an image using JSON formatting

{ 
  "elmType": "img",
  "attributes": {
    "src":  "[$Photo]" 
  }
}

And here is a complete script

{
    "elmType": "div",    
    "attributes": {
        "iconName": "Search"
    },
    "customCardProps": {
        "openOnEvent": "hover",
        "directionalHint": "rightCenter",
        "isBeakVisible": true,
        "formatter": {
            "elmType": "div",
            "style":{ "display": "block", "font-size": "15px", "padding": "10px" },
            "children": [
                {
                    "elmType": "div",
                    "style": { "display": "block", "width":"100%", "text-align": "center", "padding": "10px" },
                      "children":[
                      { "elmType": "img",
                            "attributes": {
                                "src":  "[$Photo]" 
                            }
                      }
                      ]
                   
                }
            ]
        }
    }
}

Result

enter image description here

json formatting – How to display past list items related to a column value?

I have the following SharePoint list in which the “Participant” columns are text values (names of people) and each item in this list is a training event. “Evenement” is the type of training and “Jour” means day of the training.

What I need to do is whenever someone hovers on the name of a participant – I need their past training events to show.
For example if “Participant 1” is Alpha and his “Jour 1” is on 11-05-2021 then “Jour 2” is on 12-05-2021 and “Jour 3” is 13-05-2021 and finally “Jour 4” is 14-05-2021. I need all this information to show on a hovercard if possible?

I am not sure if this makes complete sense or not and if it’s even possible but any guidance would be appreciated, thank you in advance to anyone who replies and please be advised I’m only starting with JSON .

Sharepoint List Preview

conditional formatting – Using a custom formula on a sheet that is constantly expanding

A custom formula for 5 possible colors, by day is used to highlight entries by DayOfWeek

Is it possible to use a format like A2:A instead of manually editing it as the sheet grows bigger?

Formula works fine, just don’t want to be editing it continuously

Example for rows A2 to A641 – can this be $A$2:$A ?

=and(isblank($A$2:$A$641)=false,OR(weekday($A$2:$A$641)=7))

How do I apply more than one conditional formatting rule using JSON to a SP Online List column?

You cannot apply multiple JSON formatting codes to single column. So, you have to manage multiple conditions/rules within same JSON.

For changing color to red when number is negative, add below code in your existing JSON:

...

"style": {
      "color": "=if(@currentField < 0, 'red', '#323130')"
}

...

Documentation: Use column formatting to customize SharePoint

formatting – Organising and examining a complicated CSV file

I am being sent data in CSV files. The sender is providing helpful comments and lots of headings so that it is fine to look at in a spreadsheet environment. However, when I import it into Mathematica it becomes difficult to read using TableForm mostly because many of the cells contain comments that are very long.

How do I restrict the length of TableForm entries?

Here is a minimum working example of the results of using Import[] on the CSV file.

ip = {{{"", "", ""}, {"", "", ""}, {"", 
     "Data for tests on the 14th April 2021", ""}, {"", "", ""}, {"", 
     "Here is a very long line. Only the start of the line needs to 
be seen since an overview of the spreadsheet is required. ", ""}, {"",
      "", ""}, {"", 
     "The data is in 4 four sheets this sheet is the summary", 
     ""}, {"", "", ""}, {"", "Start ", "End"}, {"Data 1", 2.`, 
     5.`}, {"Data 2", 5.`, 9.`}, {"Data 3", 20.`, 25.`}, {"Data 4", 
     12.`, 17.`}}, {{"x vals", "y vals"}, {2.`, 
     0.3330287479237922`}, {3.`, 0.19273318644865778`}, {4.`, 
     0.2689069410951601`}, {5.`, 0.08207429250157161`}}, {{"x vals", 
     "y vals"}, {5.`, 0.45418961829920845`}, {6.`, 
     0.571585992020961`}, {7.`, 0.2085062721975699`}, {8.`, 
     0.4228854382612791`}, {9.`, 0.8204853099017002`}}, {{"x vals", 
     "y vals"}, {20.`, 0.004555883050753273`}, {21.`, 
     0.9461665188994238`}, {22.`, 0.5266492863708679`}, {23.`, 
     0.993247994762222`}, {24.`, 0.40219540885589733`}, {25.`, 
     0.7634085960638198`}}, {{"x vals", "y vals"}, {12.`, 
     0.15418438184183136`}, {13.`, 0.6223264477185402`}, {14.`, 
     0.9009276274909394`}, {15.`, 0.6832380927198871`}, {16.`, 
     0.16021536248120283`}, {17.`, 0.27399014026061996`}}};
Dimensions[ip]

The Dimensions[] tells me that there are 5 sheets. So I look at the first sheet using TableForm.

TableForm[ip[[1]], TableSpacing -> {0, 1}]

Image of output

As you can see the long comments in some CSV cells make some entries in the table much longer than others. I just want an overview of the contents so I don’t want to see all the details in each entry. I probably need to do some form of converting to strings and then limiting the number of characters in each string. I am sure there are other possibilities as well. Thanks for helping.

google sheets – Conditional formatting with GOOGLEFINANCE

I wanted to keep a track of my portfolio using google sheet.

e.g: if i buy a share let’s say on 07th April 2021 and i know my trigger price, Target 1 , target 2 & SL. i want google sheet to mark each of them with specific color as soon as CMP matches one and another of trigger price, T1, T2 & SL until i sell them.

Share CMP BUY T1 T2 SL Buy Date Sell Date
HDFC 2450 2400 2550 2600 2390 07/04/2021

How to acheive it, i tried comparing CMP columns with other columns but as the prices keeps changing i am unable to track it.

CONDITIONAL FORMATTING – Unable to format column

I have a SharePoint LIST that’s being used to manage events. There are 6 participants for each event and each Participant has a column:

“Participant 1”, “Participant 2”, “Participant 3”, “Participant 4”, “Participant 5”, “Participant 6”

I need the FILL color of Participant 1-3 to be Green if EMPTY and Transparent if NOT EMPTY
I need the FILL color of Participant 4-6 to be YELLOW if EMPTY and Transparent if NOT EMPTY

I have been able to accomplish the above – my issue is that the “($Evenement)” column has two choices; Orientation/Formation. If the selected choice is Orientation – I need the Participant 4/5 columns to have GREEN fill IF EMPTY and Transparent if NOT EMPTY. Below is the code that’s not working for what I need. Any help would be appreciated – I am a complete newbie to JSON.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField == '', '#98FB98', if(@currentField == 'blocked', '#FF6A6A', if(($Evenement) == 'Orientation', '#FFFF66', '')))"
  }
}

google sheets – How to Apply Conditional Formatting Based on Adjacent Cell Value for Entire Column?

I am looking to apply conditional formatting to all cells in a column (excluding the header) where a colour will be applied depending on if its value is greater, equal, or less than the adjacent cell.

Simple example:

A B
1 100 200
2 50 150
3 80 20

Here is a simple spreadsheet that is more complex implementing @RubĂ©n’s solution:

https://docs.google.com/spreadsheets/d/1pl_UOyyE8r-JOcGWM2zsA2fFAbuvH3EUg7YnonaTZqY/edit?usp=sharing

Range B:B has the following custom formulas:

  • =B2>=A2 for green (if the cell on the right is greater than or equal to the cell on the left)
  • =B2<A2 for red (if the cell on the right is less than the cell on the left)

If the value in column B is greater than or equal to the value in column A, colour the B cell green. If the value in column B is less than the value in column A, colour the B cell red.

So rows 1 and 2 should be green, and 3 red.

I am familiar with how to do this individually on a per cell basis, but if I have hundreds of rows and need to do this over multiple columns I don’t want to manually do this hundreds of times. It looks like I need to use the Custom formula is on Conditional formatting rules, but I can’t figure out how to target a relative cell by position.


What I’ve tried:

  • Conditional formatting on one cell such as Great than or equal to $A2, then dragging this row down hoping the 2 increments. It doesn’t. This also wouldn’t work in scenarios where there are unique formulas or static values in the cells without going back and updating the cell values.
  • Using the Custom formula is, but unable to find the correct function to pair it with to again make that 2 value increment. Looked at MATCH and other descriptions of functions and didn’t see anything suitable for dynamic cell.
  • Looked at using the Script Editor, but this didn’t seem right and seemed like overkill for what I feel is pretty simple.
  • Googling, but somehow not finding anyone who’s wanted to do this before. Stack has questions that are close, such as Conditional Formatting based on other column that also use the MATCH function, but I wasn’t able to make this work.