Already have data in a spreadsheet that you want to be a SharePoint app? You’re halfway there! All you need to do is import it into SharePoint as an app.
Before you start, make sure you do the following:
-
Clean the spreadsheet. Make sure your spreadsheet looks like a table, with no blank columns or rows.
-
Make sure your spreadsheet has headers. All columns in the SharePoint app need to have a column title.
-
Make sure your data is consistent. For example, if a cell has a comment in it, but it should contain a date, remove the comment text.
-
Make sure your column heading in the first row is representative of the data. SharePoint reads the headings in the first row and makes assumptions about the information in those columns.
SharePoint looks for what it believes is the first text column and uses it as the pseudo-title field (the primary field in the list that has the Edit menu attached to it). Therefore, try to place a text field with unique data in the first column position.
Unfortunately, if your unique field is a number, such as a serial number, this can cause issues. To work around the problem, create the app and copy and paste the data from the spreadsheet. (A good example of a first column is a unique name of a person or an item, such as Event Name.)
To import your spreadsheet into a custom app:
-
Click the Settings gear icon and choose Add an App.
You can also click the Site Contents link on the Quick Launch toolbar and then click the Add an App icon.
-
Select the Import Spreadsheet App from the list of apps you can add.
-
In the Name text box, enter a name for your app.
Keep names short and eliminate spaces.
-
(Optional) In the Description text box, enter a description of the app.
-
Click the Browse button, browse to your spreadsheet, select it, and click the Import button.
A dialog box appears that asks how your range is designated. The default is Table Range, but other options are Named Range or Range of Cells. If you haven’t named the data as a range in Excel or set it as a table, select the Range of Cells option.
-
If you chose the Range of Cells option, click the Select Range field in the dialog box, and then click your spreadsheet and highlight the desired cells.
-
Click the Import button in the dialog box.
After you import your spreadsheet, verify the column types SharePoint chose for you. Generally, SharePoint assumes text, number, and dates. You may want to change some text fields to Choice, Yes/No, and so forth.
Don’t have the patience to create all your custom columns one by one — but you don’t have a current app in another format either? Create a spreadsheet with your column headers and at least one row of data, and then import this spreadsheet and modify the column properties as necessary.