PowerShell: Upload a CSV file to a SharePoint List

11 Sep 2017
September 11, 2017

Wow, it’s been a while since I last blogged – and here comes a tiny helper to upload one or more CSV file to a SharePoint Online list.
I think its the best documented script I ever created…!!!

The script

The following script checks a folder for files – and opens them. Then each line will be uploaded to the specified SharePoint list defined by the provided mapping.
The mapping is kinda straight forward. The header (if present, skip it with -skipFirstRow flag) is not required, you have to provide it via the -mapping parameter.

If you have the following in your CSV:
Value 1; Value 2

and want those values in a list with Column1 and Column2, then provide the script -mapping Column1, Column2.

Please note: The types are currently not passed and I only tested it with text values – if that is something you need, it could be easily added.

And the rest of the parameters? They are of course documented in the script. Copy it to a file and do get-help -full <yourfile.ps1> to get it!

 Feedback

So is it working for you? If so, please leave a comment!

How about building a magic mirror?!

25 Mar 2017
March 25, 2017

It was winter, dark and cold outside, maybe rainy – something must have been really odd – because I started to build something physically, my very first hardware project – a magic mirror. And having two left hands I am trying to avoid as much as I can to build things outside of Visual Studio if you know what I mean… and it was July.

 

Magic Mirror – the idea

taken from https://www.raspberrypi.org/magpi/wp-content/uploads/2016/01/magicmirrormain.jpg

“It’s called a ‘Magic Mirror’, but a more accurate name would be a ‘Smart Mirror’,” Bradley tells us. “It’s a mirror that displays the information you need to know at a quick glance: the time, the date, the weather, and of course a compliment!” — https://www.raspberrypi.org/magpi/magic-mirror/

And then I talked to Heino at a SharePoint conference who told me that he had one and how easy it was to build one, getting the mirror and all that  – thanks Heino…!!! 🙂

My initial goal was to build a small mirror to show off at the office – show some personalized things like appointments, news, twitter feed, weather, nothing fancy, right?

Getting started

I never thought that getting the first thing to work was so easy. Actually it took me 1 hour to have the first mirror up and running:

  1. Order a perfect starter kit [ref] at Amazon – with a RasberryPi3, a case, adapters,
  2. Plug it in and connect it to a monitor
  3. Boot up noobs and install raspbian
  4. Setup the magicmirror² software with one line of shell script:

4 easy steps and ~1 hour later – the result looked like this:

 

A promising start with a calendar, some nice descriptive text about the viewer and a weather forecast (that did not work on the screenshot) – except no SharePoint, right?

But it doesn’t look like a mirror yet…

Building the frame

I was a little bit afraid of building something out of wood, putting a mirror in front of it – so I decided to make my life as easy as possible:

Step 1: Get your old monitor out of the basement (for free!)
Step 2: Remove everything from the mirror (the foot and everything that you do not need) to get the panel as flat as possible
Step 3: Measure the width and height of the screen
Step 4: Go to building supplies store and get the wood for the frame, some screws and mirror glue
Step 5: Assemble the frame
Step 6: Order a double-sided acrylic mirror with the exact size of your frame
Step 7: Glue everything together

Followed by being super happy about the result:

Wow, you look hot!

Lots of glue and some red color

OK – that was a little abbreviated and sounded too easy – I followed this guide and got a lot of help from the building supplies store guys – and I am thankful for that 🙂

But the quality of the mirror combined with my 10-year-old monitor is really good – if I cover the backside so that it’s entirely dark inside, the monitor is not visible on the front and the display magically appears.

The software

I tried to create some modules for a SharePoint integration – but at that time node.js and the module felt odd – so I took another road and installed Windows IoT core on my Pi – but that’s not done yet and I will follow-up with a blog post once I have something to show.

For just basic stuff the magic mirror software really looks mature, lots of modules and plugins ranging from webcams, Alexa integration, stock ticker – it’s all there and ready to use.

If the weather outside would be nice right now, I would happily accept what I get there for free – but I want to build something by myself – My version of a magic mirror 🙂

SharePoint App, Custom Domain and free SSL certificate

25 Mar 2017
March 25, 2017

Recently I created lots of SharePoint provider hosted apps for several customers – most of them very simple apps to register a remote event receiver or send emails. But a few of them were complex business apps that used the integration options for SharePoint but stood almost separately as websites running on Azure. The first apps we created had the name <app>.azurewebsites.net – and for some customers that looked so odd that they opened tickets and asked for confirmation if the apps are legit. And that makes perfect sense because everybody could create a website and call it superlegitcustomerapp.azurewebsite.net, right? So in this blog post I will show you how to run an app on a custom domain with a free SSL certificate.

For this article I assume that you have your SharePoint App up and running on Azure – and I assume that you have a domain to use.

Register the custom domain on Azure

3 easy steps to register a new hostname

First things first – tell the azure website that it will have a new hostname to respond to. Therefore go to the azure portal, open your website and click on “custom domain”. Then add your domain or subdomain that should be the new entry point of your app. Once you have done that, you need to validate the websites by adding a txt record to your nameserver with the value * mapped to .azurewebsites.net – full guide is here.

domain validation via nameserver entry. The @ symbol was not allowed, but with * it worked.

After validation was successfully it should look like that:

new entry was added successfully

For the custom domain you need at least a shared instance tier for your azure website.

New name – new problems

Ok, now we have setup the new hostname and we can open up the site with that – but that creates some problems:

  1. The app registration (/_layouts/15/appregnew.aspx) needs to be updated
  2. The app manifest and the app points to the old site
  3. There is no SSL certificate for the new name

1 & 2 are easy solvable: Open the app in Visual Studio, change the app domain, upload it to the app store, trust it and re-add it to your sites. But if you open the new app – you will either redirect to http:// and have all you traffic sent over an encrypted connection – or you send it to https:// and will be greated by a security warning in your browser because there is no SSL certificate present for your new domain.

Free SSL certificate

In the past I heavily used free SSL certificates from StartCom because they were trusted in all browsers and were free for 1 year – but they lost trust in google chrome and firefox recently so that is no longer an option. Not using a ssl certificate is no option at all – business apps need security and trust – therefore I needed another option, preferably free so no long discussion with customers, CSRs and all that to request a certificate for your needs. I want easy.

Let’s encrypt for the rescue

There is a really great organization that provides free SSL certificates – Let’s Encrypt – and I use them for this blog with ease, too. So why not use them for my Azure websites? The let’s encrypt certificates only lasts 3 months and should be renewed automatically (way better than certificates that lasts 1 year and have to be renewed manually…) – so there should be some sort of integration right into the azure websites for that – and there is! Thanks to the awesome work of Simon J.K. Pedersen there is a Azure Website Extension for Let’s Encrypt.

The installation was really straightforward for me and is well documented here – I added the extension (choose the correct version for 32 or 64bit!), register a new service principal and give it contribute permissions – open the extension site https://.scm.azurewebsites.net/letsencrypt/ and enter all the required values:

Once you save it the magic happens – two webjobs will be registered that request a new certificate, register them and two weeks before expiry renew them automatically for you. Awesomesauce!

For the custom ssl certificate you need at least a basic app service plan.

Summary

So once you have done all that you will have a nice domain for your app – giving your users trust and easy to recall entry to your business application – and all that is free with zero maintenance. And all your browsers love it, too:

trusted in Google Chrome

trusted in IE11

 

So if you create a new app – register the domain first, follow the guide above and you are good to go – the secure way: customer happy, developer happy, security guy happy! 🙂

 

 

 

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close