Tag: Office 365
Excel – Setting a Cell Value Based on Background Color
I need to programmatically parse an Excel file where items are grouped with arbitrary group sizes. We don’t want the person filling out the spreadsheet to need to fill in a group # column … so I’m exploring ways to read cell formatting so something like color can be used to show the groups. Reading the formatting isn’t a straight-forward process, so I wondered if Excel could populate a group number cell based on the cell’s attributes.
While it is possible, it’s not a viable solution. The mechanism to access data about a cell cannot be accessed directly and, unfortunately, requires a macro-enabled workbook. The mechanism also requires the user to remember to update the spreadsheet calculations when they have finished colorizing the rows. While I won’t be using this approach in my current project … I thought I’d record what I did for future reference.
We need to define a ‘name’ for the function. On the “Formulas” tab, select “Name Manager”.
Select ‘New’
Provide a name – I am using getBackgroundColor – and put the following in the “refers to” section: =GET.CELL(63,INDIRECT(“rc”,FALSE))
Now we can use this name within the cell formula:
Select the rows for your first group and change the “fill color” of the row.
Repeat this process to colorize all of your groups – you can re-use a color as long as adjacent groups have different colors. Notice that the “ColorGroup” values do not change when you colorize your groups.
On the “Forumlas” tab, select “Calculate Now”
Now the colorized cells will have a non-zero value.
Microsoft Teams Meeting Notes
The trick to understanding this is knowing that “Meeting Notes” are, for some reason, Wiki pages and not OneNote documents. There are two types of meetings — those held in a Teams channel and those held outside of a channel — and the ability to get a useful link to the Meeting Notes depends on which type of meeting you have.
Meetings in a Teams Channel:
When your meeting is in a Teams channel, you can use the ellipsis to grab a link to the Meeting Notes location in Microsoft Teams.
This link points to the “Meeting Notes” tab created in the channel. That tab is available without a link, too — so I can access the meeting notes just by going to the channel where the meeting was held.
Meetings Outside of a Teams Channel:
The meeting notes wiki file is stored in your OneDrive. You can find that file by searching your OneDrive for the name of the meeting. In this example, I have a meeting titled “Super Important”. You can right-click on this and select “copy link” to grab a link to the file.
The problem is that it’s an MHT (basically a self contained web page) file. I can give you a link to the file, but it’s not a convenient link to a OneNote page like you’d expect. For some reason, Chrome wants to save it as an EML (email) so the file opens in Outlook (or change the extension to MHT manually). Firefox keeps the MHT extension, and the file opens up in a browser so you can view the notes.
Did you know … Teams meetings now include closed captioning?
When you record a Teams meeting, Stream can generate a transcript of the meeting. Great for making meeting minutes or creating searchable content from meetings. But it doesn’t help someone who doesn’t here so well *participate* in the call. And the attendee at a noisy aeroport? They’re stuck waiting for the transcript to be generated. Microsoft had demonstrated a few new meeting features earlier in the year — background replacement and live captioning. While I still cannot drop the company logo behind me … live captioning has started to show up in tenants. This is currently in preview — which means you may encounter glitches. Instead of waiting for a transcript to be generated for a recorded meeting, live captions provide real-time on-screen transcription.
To turn on live captioning, click the ellipsis in the call control bar and select “Turn on live captions”.
A real-time transcript will appear in the lower left-hand corner of the screen. The text is large and easily read — at least on my desktop.
Their transcription engine picks up random background noise as interjections — the “oh” in my test, of instance, wasn’t actually uttered. Participating in a discussion with esoteric terms might yield a lot of mis-transcriptions. But it did a decent job with Z-Wave, DSLAM, and antidisestablishmentarianism.
MS Teams on Linux
Microsoft Teams: Private Channels Arrive
WooHoo! When creating a channel, I have a privacy setting!!
Individuals who do not have access to the channel do not see it in their Teams listing, and posts made to a private channel cannot at-mention the Team or individuals who do not have access. I’m glad Microsoft landed on the side of privacy in their implementation here.
Upcoming Features from Ignite 2019
- Private channels should be coming this week … not my tenant yet, but soon
- Multi-window functionality where chats, calls, and such can pop out into another window
- Live captioning should land later this year — this is an obvious great feature for people with reduced hearing or frequency loss, live “closed captioning” is awesome if you’re working from a noisy location too
- Microsoft Whiteboard moved into general availability — it’s been a preview for quite some time now
- “Attendee” roll will prevent people from inadvertently sharing their screen in the middle of a meeting
- My Staff portal that allows managers to perform password resets (and maybe unlocks) for their employees. This is something I’ve done as custom code in IDM platforms, but it’s nice to see Microsoft incorporating ideas that reduce down-time due to password challenges.
- I’ll be curious to see if the healthcare-specific features move into other verticals — MS rolled out a feature that allows you to designate a contact when you’re in surgery (basically redirect all of my messages to Bob because I’m busy right now) that seemed like it would be quite useful in enterprise or education uses. The “patient coordination” feature they talk about might work as a contact management tool out of the medical realm too.
- URLs in Teams will be protected like the links in other Office 365 programs — if you click on a link and see something about “Advanced Threat Protection” … that’d be why 🙂
Microsoft Teams: Cross-posting to multiple channels
Click on “Post in multiple channels”
To post in additional channels, click “Select chann…”
Check off the channels into which you want the post written – this can be a channel in any Teams space where you are able to post messages. Click “Update”.
When your message is posted, an indicator will appear letting everyone know it was posted in multiple channels. No, there doesn’t appear to be a way to see which channels – that’s probably a permission / information leakage nightmare (post something into the “Mass Layoffs” channel that I shouldn’t know exists … I shouldn’t be able to see that channel name). But the glif gives you some confirmation if you think you’ve seen this info elsewhere.
Note – the posts are not linked to each other. If someone replies in one channel, the post in the other channels will not include the reply. So while this is a quick way to disseminate the same information to various teams … you’re starting multiple conversations too.
Also note — there doesn’t appear to be a way to edit cross-posted messages.
Microsoft Teams: Thread Moderation
Did you know … you can add a “Share to Teams” button to your web content?
If you can add script tags to the page head, you can add a “Share to Teams” button on your web site. This can be used to allow employees to share internal sites to Teams, but it can also be used on public sites to allow visitors to post links to their Teams organization.
How? There are two steps – add “<script async defer src=”https://teams.microsoft.com/share/launcher.js” ></script>” in HEAD. The post that is made to Teams is *prettier* if you have meta properties for title, description, and image within the linked page.
Then add a div with class “teams-share-button”. The “data-href” value is the URL to be shared. If you don’t want a page preview to render, you can set “data-preview” to false.
Sample page content:
<head> <title>Teams Share Test</title> <meta property="og:title" content="Lisa Rushworth Home Page"> <meta property="og:description" content="Lisa Rushworth's Home Page"> <meta property="og:image" content="https://www.rushworth.us/lisa/RedR.png" /> <script async defer src="https://teams.microsoft.com/share/launcher.js" ></script> </head> <body bgcolor="black" text="white"> <div class="content"> <P>Here is the really cool information contained on this web site. It is so interesting that you want to share it to Teams.</P> <P>Click the Teams button at the bottom and you'll see a form that allows you to share the URL as a thread in a Teams channel.</P> </div> <div class="teams-share-button" data-href="https://www.rushworth.us/lisa/teamstest.php" data-preview="true"> </div> </body>
Visitors will see a small Teams logo in the teams-share-button div. To share the URL in Teams, they just need to click on the Teams logo.
A new window will load. If the user is not logged into the Team web site, they will be prompted to log in. Once logged in, the share dialogue will be displayed. If your site has title, description, and icon meta tags, a preview card will be included at the bottom.
Click in the “Share to” field and type a Team or Channel name – Teams and Channels from the user’s organization will be displayed.
The user can add text to the thread. Click “Share” to share the link to the selected Teams channel.
A confirmation page will be displayed.
In Teams, a new thread will be created. This is the thread for my shared URL.
The URL used in the teams-share-button DIV doesn’t have to match the page on which it is used — I can add a ‘share to Teams’ button that posts any URL to Teams.