Learn how to customize lists in SharePoint by creating an Excel table with sample records and then use the SharePoint Import Spreadsheet app to create the list.
- [Lecturer] I've created a site just for the work that I'm doing in this course, I'm working in SharePoint Online, so this is what my site looks like. Your site might look a little bit different, but find yourself in the site that you want to use for the work that we'll be doing together. If you're already familiar with this, this is a little bit of a review, if you're not familiar with this, this is one of my favorite techniques to work with Excel and SharePoint together, because anything I can model in Excel, as a list, as a table, I can then import into SharePoint and this allows me to, very quickly, create apps where users can enter information, whether that app is going to allow them to enter in SharePoint or to enter it, in this case, in a PowerApp that we're going to create from this custom list.
If you have a premium subscription to the lynda.com Training Library, then in the Exercise Files folder you will find a file called KinetEco product inventory and this range has already been formatted as a table. This is information about our inventory in the West Region and this is what we'll be importing into SharePoint. If you don't have access to the Exercise Files, just use any Excel workbook that includes a table that you would like to use and identify where that workbook is at this time.
Let's swing back to SharePoint. Click Site contents, create a new item and we want to create a new List. Now this would allow me to enter information about a list, I want to use the app to do this. So, I'm going to click New again, choose App, and here are my apps that are available. The app I want is on the second page in My Apps, and that is the Import Spreadsheet app.
Now, a word at this point, this particular app uses ActiveX, and that means that if I'm going to use this app, I really need to be using Internet Explorer. And that is my default browser for use in SharePoint. Edge is fine, Chrome is fine, they're all nice, but because I like using the ActiveX apps like this one, I pretty much stick to Internet Explorer while I'm doing development work in SharePoint. Let's fire up this app and I'm going to call this Inventory.
Actually, I'm going to call this west_inventory, and I'm going to put an underscore and the reason, is I'm not being asked to provide a URL, that means this name will be the URL, I'll clean this up afterwards. And this is Inventory for the West Region. Where's my file? Click Browse, and remember that we are finding this on our Desktop in our Exercise Files right here. I can anticipate this will cause a little bit of a problem because I haven't closed this file.
Let me go do that real quick. I'll simply close this and now I'm going to swing back into SharePoint and click Import. So at this point if you're seeing message that says, "An unexpected error has occurred," then there's an issue that won't allow SharePoint and Excel to work together dynamically in this way and this is usually an issue about either permissions or trusts. If you're working on a SharePoint site that you use every single day that's maintained by your IT department, the odds of you having an issue with this more than once are pretty slim because they will come fix you, they probably did it upfront.
But if you're working on site that you set up just to be able to work with me in this course the odds are a little greater that you might have an issue. So let's talk about how we can fix that, we're going to go now to Internet Explorer. And here in IE, because that's the browser we should be using to run this ActiveX control, we're going to go to our Internet options, we're going to go to Security, and we're going to make sure that our SharePoint site is listed in the Trusted sites.
So click Trusted sites, click Sites and then this list of sites should include your SharePoint site. There's mine right here. If your site isn't here, then put its URL in, you can go back to another tab the browser, wherever your SharePoint site is and you can paste that in and click Add and close this. The other thing you may need to do is to turn off Require server verification for all sites in this zone. You can try leaving it on at first, which would mean it's, you know, https sites are the only types that are allowed, but if you experience the same problem, come on back to this same dialog and try turning on Require server verification.
I'm not sure why that works, but sometimes it does. After you have added your current SharePoint site, it doesn't need to be each site, the highest level site will work just fine to your Trusted sites list. Simply click OK, go back and try the same thing again. One more possibility, after you've done this, you can also close Internet Explorer, start Internet Explorer again, go back into SharePoint site and continue from that point forward. If it's not trust that's an issue it's usually permissions, so talk to your SharePoint administrator.
And there is one more workaround, which is, from Microsoft Excel itself you can export a table to SharePoint and you can try that if you would prefer but often if importing doesn't work, exporting won't work for exactly the same reasons. Let's go back to SharePoint now. I'm asked what type of range I want and I'm going to actually use a table, the Western Region Inventory Table1.
Click Import, west_inventory and all of my columns of information. To clean up this name, that again, appears in the URL, click gear, choose List settings, List name, description and navigation and let's now change this to West Region Inventory.
Do I want it on the Quick Launch? Yeah, let's put it there and save this. And return now to my list, West Region Inventory. All cleaned up. As I noted earlier, this is a way that I like to create custom lists in SharePoint. Rather than needing to specify information about what data types I'm using and other information, in SharePoint I can do this, and Excel, which I'm very comfortable with, but more importantly, my users are comfortable with using Excel, so if I say, create the list that you'd like to see in SharePoint, but create it in Excel and pitch in a couple of records for me, this works really well for people who aren't that familiar with SharePoint.
Remember that when I go to my List settings not only do I have the ability to modify my List name, description and navigation, but I can double check and ensure that all of the information that I imported, all of my columns reflected the correct data types that we have, for example, Number for quantity and Number for Watts, Currency and so on. Where this information comes from is from the records that are in the data that I import, so even if I don't have any records, even if I have simply modeled by creating column headings, I want to include two or three rows of data, because that's how SharePoint determines what SharePoint data types should be used to store that information in your list.
Now that we have the list that we'd like to create a PowerApp for we can move on to PowerApps. See you in the next movie.
- Signing up for PowerApps and Microsoft Flow
- Importing a custom list from Excel
- Creating and managing a PowerApp from SharePoint
- Formatting text, modifying settings, and branding your app
- Updating app forms and the data source for an app
- Applying conditional formatting to a control
- Creating a flow for a SharePoint list
- Adding data using a mobile app
- Viewing results in Flow