Technical

Excel to Modern App in Apex Addendum: Websheets

In the previous posts (Part 1, Part 2 and Part 3) we took an Excel Spreadsheet and create a modern web-based application that our business users could do. Whilst it created a very powerful application, it did require us to know a little bit of SQL and was complex in parts. An alternative to a full-fledged application is to use the Websheets feature in Apex to create an editable web page that users can use to edit and manage their data. This can be a more appropriate option for converting spreadsheets as it is easier to use and doesn’t require any knowledge of SQL. However, because of this simplicity, it’s not as powerful a tool and may not be an option for more complex data interactions.

In this post we will take the spreadsheet we used in the previous posts and create a Websheet so we can compare the two methods. After that you’ll hopefully be able to choose the right option in future.

To start, go create a new application but this time select ‘Websheet’ as the option

1. Create WebSheet

Let’s call it ‘SalesSheet’. Untick the ‘Include Getting Started Guide’. This will just add a bunch of pages explaining how to use the Websheet features. You may want to turn this on in your own example to get an idea of how everything works, but for this example let’s turn it off. You’ll also notice a ‘Allow SQL and PL/SQL’ option. This would allow our users to embed SQL statements into the text areas of the websheet. This can be useful to include dynamic elements on the page (eg: a sentence like ‘The total sales for last quarter were: [SQL statement to work that out]’). For this example, let’s prevent our users from doing that.

2. App Info

And that’s it, we’ve created our first websheet, though it’s very bare:

3. Home Page

There are 3 types of elements to a websheet:

  • Pages: Just like a webpage your websheet can have many pages. These may include text, charts, grids etc.
  • Sections: Pages are made up of one or more sections and each section will contain either text, charts, grids etc. These are the building blocks of your page
  • Data Grids: The data from your spreadsheet(s) go into a Data Grid. Users can interact with the data grid directly, though this is generally reserved for power users or admins. Everyone else should interact with the grid through a section on a page

So our first step will be to upload our spreadsheet data. To do that, click the ‘New Data Grid’ option under the ‘Create’ menu.

4. Create Data Grid

We can either create the spreadsheet from within the Websheet application or copy and paste from an existing spreadsheet. In our scenario we will use the spreadsheet data we used previously.

5. Copy and Paste

Call the grid ‘Sales Data’ and paste in the data.

6. Sales Data

Click ‘Upload’ and you should see your new data grid.

7. Created Data Grid

You can interact with this data grid from here, including adding new rows and editing or deleting existing ones, You can also filter the graph (say to only show results from 2013) and save that as a report. You can then display this report on other pages. This is a very useful feature that allows a power user to manipulate the data in some way and then show to other users without them having to filter the data themselves. All the reports work off the same underlying data grid so when changes are made to the data all of the reports will automatically be updated. To demonstrate this feature, let’s create a report that only shows data from 2013. First, we need to actually manipulate the table before we can save the report. To do that, click the ‘Year’ column and then select ‘2013’ from the list.

7a. Filter Year

The grid will be filtered to only show data from 2013. You can turn the filter on and off as required. Now let’s save the report by clicking the ‘Save Report’ option in the ‘Actions’ menu

7b. Save Report

We now have two options to save the report as – either ‘As Named Report’ or ‘As Default Report Settings’. The first option will make the report private and will only be accessible by the user that created it. This can be useful for users that regularly look at data in a way that only they care about. If you want everyone else to benefit from your report, you need to save it as ‘As Default Report Settings’.

7c. Default Report

Now select ‘Alternative’ as the ‘Default Report Type’. Selecting ‘Primary’ will mean that the grid will use your report by default. You generally don’t want to overwrite the default (which is to show everything) unless you have a good reason why people can’t see all the data. Call it ‘2013 Data’ and click ‘Apply’

7d. Alternative

Now you can see that our grid is filtered by 2013 and you can see your report title in the filter list.

7e. Report Saved

Now we have the data in, but we don’t really want the users having to go into the data grid section to manipulate the data. Let’s create a Sales page to contain the data. On the home page click ‘New Page’ and enter the name as ‘Sales’. Note that the Parent Page will default to ‘Home’. You can create a complex page hierarchy to whatever depth is required in your org.

8. Create Sales Page

Let’s add our data grid to the page so that users can view the data.

9. Create Data

Select the ‘Sales Data’ data grid and call it ‘Sales Data’. Leave the ‘Primary Report (Primary Default)’ as the Report Settings. This will show all the data to the users, as opposed to our report which will only show 2013 data. Make sure to tick the ‘Add Row’ and ‘Edit Row’ options, as this will allow our users to modify the data. Without that, it’s just a read-only grid.

10. Grid Options

Create the page and navigate to it. You should see the data grid embedded into the page.

11. Sales Section

Even better, they can filter the data and edit it as needed.

12. Edit Sales

That’s all pretty nice, but let’s go to the front page and create a ‘table of contents’ and a chart. On the home page, click ‘New Section’ and select ‘Navigation’

13. Create Navigation

Choose ‘Page Navigation’.

14. Page Navigation

Set the title to ‘Navigation’ and the ‘Starting Page’ to ‘Home’. This allows us to define where the navigation starts in our hierarchy.

15. Navigation OptionsHaving created the navigation, lets also add a bar chart to this page. Again click ‘New Section’, but this time choose ‘Chart’

16. Create Chart

Select ‘Column’ as the chart type

17. Chart Type

Select the ‘Sales Data’ as the ‘Data Grid’ and choose the ‘2013 Data (Alternative Default)’ as the report settings. This will use the report we created before to automatically filter our chart to only show 2013 data. Make sure to give it a title (‘Sales Data’)

18. Chart Data

On the next page, choose ‘Quarter’ as the Chart Label (as we want to show the sales for each quarter) and give it a label. Then choose ‘Total’ as the ‘Chart Value’ and give it a label. Then select ‘Sum’ as the ‘Function’ as we want to sum all the results for each quarter together. Finally uncheck the ‘Enable 3D’ box (personal preference, I prefer the non-3D option)

19. Chart Options

And there you have it, navigate to our home page and it should look like this

20. New Home Page

Conclusion

As you can see the Websheets are a lot simpler than the proper applications in Apex. Adding new pages and navigations elements was a lot more streamlined than in Apex and we didn’t have to write any SQL to construct our tables or graphs. However, we are somewhat limited in what we can do in websheets. For instance, there is no way to add multiple series to a chart like we did in our Apex example and we don’t have any control over how the add/edit pages look like on our grid. Another downside is that whilst the data is contained within an Oracle Database (so it’s managed, backed up etc) it’s not in a proper SQL table so it will be tricky to re-use this data elsewhere. Not impossible, just tricky. An apex application using a proper table won’t have this problem

I’d recommend websheets whenever you have a simple spreadsheet that you’d like to modernise and provide a simple web interface to. If the requirements are complex, or you believe that the users will outgrow websheets pretty quickly, take the time to build a proper Apex application.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s