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
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.
And that’s it, we’ve created our first websheet, though it’s very bare:
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.
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.
Call the grid ‘Sales Data’ and paste in the data.
Click ‘Upload’ and you should see your new 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.
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
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’.
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’
Now you can see that our grid is filtered by 2013 and you can see your report title in the filter list.
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.
Let’s add our data grid to the page so that users can view the 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.
Create the page and navigate to it. You should see the data grid embedded into the page.
Even better, they can filter the data and edit it as needed.
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’
Choose ‘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.
Select ‘Column’ as the 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’)
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)
And there you have it, navigate to our home page and it should look like this
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.