AppSheet - Rota App

I first found out about AppSheet a number of years ago when it was still a fairly new project. I was running my own skate school and needed something that could organise data while on the move; specifically bookings I had made with clients. I had looked at a lot of options which didn't quite make the cut of what I needed. But then I stumbled across AppSheet, and it did just about everything I needed it to. Well, almost everything. But as it uses Google Sheets as pseudo-databases, I was able to use Apps Script to fill the last few gaps, and have my own custom software .. for free!

I really like low cost and no cost solutions. the free tier on AppSheet can be a little limiting, that is unless you know you're way around Google's Apps Script feature. When it was still new, AppSheet relied on Google Sheets and Microsoft Spreadsheets as backend. Nowadays they have a myriad of options that even include cloud SQL databases. They've even released their own AppSheet databases. However, on the free tier, you're limited to 1000 rows in a database, which can fill up quicker than you might expect depending on your use case. There are some handy workflow options, but scheduled workflows are not on the free tier either, so flushing old database entries is not really an option. However, this is remedied by going with the tried and tested, straws and sellotape method of using Google Sheets, where we can write Apps Script's to perform all kinds of workflows. The synergy between AppSheet and Google was so strong, that Google took the project into it's ecosystem (here's hoping they don't send another great project to the Google Graveyard)

I discovered AppSheet at a time where coding was something I occasionally dabbled in and I certainly would not have been able to put together a web app at the time. Now I'm deeper into the rabbit hole, and could confidently put together a simple web app. Yet, I still find myself going back to AppSheet to get something done quickly and for free. I don't have to worry about writing an entire codebase or wrangling with CSS. There are caveats, though. Although AppSheet brands itself as a no-code platform, it does rely heavily on something far more sinister and ugly: formula. That is, spreadsheet style formulae are used to get an app to do just about anything interesting at all. One project that I have built which gets used in the real world is a rota app, to handle shifts for employees where I work. It has just enough going on in it that it's a good example to use to start getting familiar with using AppSheet without being overwhelming. The following is a guide to assembling a simple rota app using AppSheet with some Google Apps Script integration.

Building the Backend

Ha, good one. Our backend is a Google Sheet. Column headers will be used as "fields" in app. Any additional rows are considered entries. You can add in directly through Google Sheets, but I'd advise using your AppSheet app to avoid any formatting inconsistencies.

We can use sheets, or pages, within our spreadsheet as separate data sources. We can also use multiple types of data sources within apps, including multiple spreadsheets, but this app isn't likely to get so large so quickly that having everything tidily in one spreadsheet is likely to become a problem at any point.

Our main sheet will be a shifts table, with additional tables for staff and locations. Our team at work do occasionally have to work away from the main site, so it's a niche feature that we use, but I am including it here just to demonstrate some neat stuff you can do between AppSheet and Google Suite. The staff and location tables will be structured thus:

As previously mentioned, it's best to wait to enter the individual records through the AppSheet app. AppSheet is pretty feature rich, it's not difficult at all to include hot buttons in the app to make phone calls and emails. When you set an address data type it will do what you would hope it would do and look for real addresses and list suggestions as you type. You can also enter something that is not an address, like home. We find this particularly useful for when someone works from home. These shifts all eventually end up in a Google Calendar which all employees have access to, and when it opens the location to home on a users device in Google Maps, the omniscient Google normally has a good idea of where home is per user.

Next, we need somewhere for the work shifts to be stored. So we can make the shifts table. To allow the app to differentiate between shifts we will include a Shift ID column that will be automatically populated by AppSheet itself, and to track it's linked calendar event in Google Calendar we will include a Cal ID column, which an Apps Script will pass into AppSheet at the end of specific workflows.

Building the Frontend

No code, anyone can do it! But, if you want your app to have any kind of useful or advanced functionality it will be less simple. The design UI is good, although, out of the box AppSheet is already doing quite a lot, so even building your first simple app can be a bit of a learning curve.

The main aspect of the UI are in the menu bar on the left of the screen, for now we only need to worry about data and views. We will also use automation. These are where we make our app work.

Data

In the data view, we can add our data sources using the + button. Find the spreadsheet you created in Google Drive, select it, and select the relevant page for each data source. Depending on what you name each column, AppSheet will hazard a guess at what you want each data type to be, but in case it gets it wrong, these are the data types for the Locations and Staff tables:

For those, you will also want to set the key and labels. For Locations the Name column will be both key and label. For Staff, the employee number will be the key, and the name will be the label. The key is how the app deciphers which record or row is being referenced, while the label is used as the human readable identifier, so that we aren't expected to figure out what staff is being referenced by a particular key.

The Shifts table is a little more complex, so I will include a few more of the option fields for this one:

The Ref datatype is a reference to another table or data source. To set what each one references use the Edit button on the left, which is a pen emoji, and then set the source table to the relevant one for each of those. Also make sure that Cal ID is set to NOT required, as we want to leave that blank for an Apps Script to populate later on.

We have now had our first tastes of AppSheet's formula in the Formula and Initial Value fields. Formula's allow AppSheet to do some more interesting things computationally than say, a regular table's app. Here, we use the UNIQUEID() function to create a Unique ID for the Shift ID. By default, the function generates strings of 8 random characters, with a low probability of creating duplicates. However, over time and large quantities of data (several thousand entries over the course of years), I have encountered multiple instances where AppSheet generates a code that is already in use. This isn't a huge deal, but you do have to abandon that particular record and start it over again, allowing the app to have a second crack at generating a new code. This is manageable, as long as you have the data available to you to enter it a second time, and if it's not a large amount of data. I take this opportunity to apologise for the times my colleagues have had to start over while logging orders into the skate shop POS). In my defence, the AppSheet documentation claims that:

The chance of a duplicate ID being generated is virtually zero.

Since making that app though, AppSheet have added arguments to the UNIQUEID function: "UUID" and "PackedUUID". These both create longer and more complex strings, vastly decreasing the chance of repetition. One last thing to touch on here, with respect to using computed values as a key, is that AppSheet can be a little quirky here. Ideally, we would set this as the Initial Value, but AppSheet wouldn't let us do that and also select it as a key. If we set it as App Formula instead, and tick this field to act as the key, AppSheet still doesn't like this, but it will automatically generate a new virtual field, called _ComputedKey, and set it's value to [Shift ID] in it's App Formula - [Shift ID] represents the value in that column, or field, for this particular row, or record.

We get to see a few more examples of what can be achieved with some simple formula expressions in the Initial Value fields. For date, we can use a template expression to define the format of the date that the user must input the date in. I'm in the UK, so please don't be alarmed that I have defined "{DD/MM/YYYY}". For location, I have defined "{Work}", which is one of our location's names. For reference data types, AppSheet will generate a dropdown menu in the form, it uses key's to distinguish referenced records, but it will use the label we have defined within the dropdown, as long as our initial value exists as a label for a record in the linked table, AppSheet will make the association. For Start time, instead of defining a format with "{HH:MM}", I have set an initial value of "00:00", this is helpful for the user, as when we define a format as "{DD/MM/YYYY}" or "{HH:MM}" it will display those in app as --/--/---- and --/-- respectively, by setting the time to "00:00" a user only has to set hours and can skip over the minutes if a shift starts on the hour as they often do. Finally, The Finish time is set to [Start]+"006:00:00", which assesses what the time defined in the Start field is, and then adds 6 hours to it as a sensible default.

Views

The Views panel is where we make the views that the user will interact with in the app. There's a few to look at, but the ones we will be using in this app are Deck, Table, Calendar and Dashboard. AppSheet will automatically generate Details and Form views for each data source, but we can create our own when we want a little more control over what's on screen at a given time.

Starting with the simplest table, Locations, we can select Deck for view type and choose where on the menu bar we want it to show up. Options here are first, next, middle, later, last, menu and ref. Menu and ref are the only ones that aren't on the main menu bar, selecting menu will move the view button to the side menu, and ref will hide it altogether, and it will be used some other way, such as part of a dashboard view or as a form. For the locations view, set the primary header to Name and the secondary header to Address. We can also select Manual for Actions, and remove any unwanted fluff like the map view button, and just keep the edit and delete buttons showing.

Next, for the Staff table, set the view type to Table. If we set the Column Order to manual, we can choose only the necessary columns to include in the main view, which I have set to Name and Employee Number only.

For the Shifts view, we can do something a little more interesting. Instead of a single view, we will make two views and include them both in a dashboard view. The first view will be another deck view, with the Position set to Ref so we can place it in the Dashboard view. We can sort by Date first, and then Start, both descending, so oldest shifts are placed further down the list. We can also use Group By, to separate shifts further under a specified header. I use a virtual column called week start, in descending order. To create the virtual column, simply go back into the data panel, choose the Shifts data, then click the + button in the top right corner to add a new virtual column. We can also set up an End of Week virtual column that will be useful later on.

# Virtual Column: Start of Week 
DATE(
    EOWEEK(
        DATE([Date] - 1)
    ) - 5
)

# Virtual Column: End of Week
Date(
    EOWEEK(
        DATE([Date] -1)
    ) + 1
)

Spreadsheet style formula with nested functions is hard to read whatever way you right it, but I think multiline notation is the only way to not go cross-eyed when writing some of the more complex formula's in AppSheet. We are limited to how much data we can display at a time in the deck view, but we can use virtual columns here too. We can fit both the name and location in the secondary header by concatenating both columns into one virtual column.

CONCATENATE([Staff].[Name], "/", [Location].[Name])

We reference the table reference here and the column we want to use, as using the relevant column from this table would just pull in the key rather than the label. We can use multiple virtual columns on concatenate functions to display the data, start and finish in the primary header.

# Virtual Column: Shift
CONCATENATE(
    TEXT([Start],"HH:MM")," - ",TEXT([Finish],"HH:MM")
)

# Virtual Column: Shift Date Time
CONCATENATE(
    TEXT([Date], "DD/MM/YYYY"), ", ", [Shift]
)

The "Shift Date Time" column will be what is used for the primary header. We can use the TEXT function to declare the formats to be used when displaying dates and times. In my experience, trying to get AppSheet to do all that in a single virtual column and one formula causes issues and can break the app, but breaking it up into two steps across two virtual columns hasn't caused me any issues in all the time I've been using AppSheet.

In the summary column on the dashboard, I like to include a weekly hour count for each member of staff. It's a useful quick reference to check how many hours each member of staff is rota'd in for in a given week that can be checked at a glance while working in the app. In order to work that out, we will first want a virtual column to work out each shift duration, then a second to add them together. The Start of Week and End of Week virtual columns are both helpful here in the calculations.

# Virtual Column: Shift Duration
TOTALMINUTES(
    TIME([Finish])-TIME([Start])
) / 60

# Virtual Column: Hours for Week
SUM(
   SELECT(
      Shifts[Shift Duration], 
      AND(
        ([Staff] = [_THISROW].[Staff]),
        ([Start of Week] = [_THISROW].[Start of Week]),
        ([End of Week] = [_THISROW].[End of Week])
      )
   )
)

Virtual columns are useful, but as we add more in, the form views and details views that are automatically generated by AppSheet for each data source begin to get cluttered with irrelevant information to adding and updating records. Luckily, we can customise these. At the bottom of the Views menu pane, under System Generated, simply click on the form view for Shifts, and change Column Order form Automatic to Manual. You can then remove all virtual columns since they are automatically generated anyway. This should just leave us with the original columns from the spreadsheet:

• Shift ID
• Date
• Start
• Finish
• Staff
• Location

The same process can be repeated for forms for other data sets, and also for details views, however, you might choose to display some of the virtual columns in the details views if they are useful to the app user.

Next, we can build a Calendar view, and set the data source again to Shifts. In case AppSheet doesn't get the info correct in the view options settings, we want the following set up:

There is also an option for category, this will categorise different records under their own colour codes on the calendar view. This is helpful to see who is working on a given day at a glance, but to use it, we will need to add a colour code field to the staff table.

Back in Google Drive, add a Colour Code column to the Spreadsheet, then in Data view for the staff table press the Regenerate Schema button in the top right corner. Make sure the data type is set to color, and then we can add a colour to each staff member in the live preview mode.

Workflows

Now that we have most of our app's frontend set up, we should write some workflows to get our app to communicate with our Google Calendar. We will do this using Google Apps Script. There are essentially two ways to get Apps Script to interact with our AppSheet app. First, we could write some script that is attached to the spreadsheet itself, and set up a trigger for whenever a change is made to the spreadsheet. This approach is good for dealing with bulks of data, for example, a periodic report based on the data that might be sent out by email once per week or so. However, AppSheet can sometimes leave a table with missing rows when it deletes records, so this method is difficult to use with data that has delete permissions. AppSheet also offers a second, albeit limited, method for applying Apps Script to individual records. Let's write our script and then we can hook it up to the app after.

Start a new Apps Script in Google Drive, advisably in the same, or an adjacent, folder as the Spreadsheet we built earlier. We are going to need some helper functions first though; one to get the Google Calendar we are using for our data so that we don't need to retype that code block for each function, one to format the dates in a way that allows the Google Calendar API to understand them, and the final one to allow the API to make sense of the colour codes being passed in.

const getCalendar = () => {
    return CalendarApp.getOwnedCalendarById('<calendar-id>@group.calendar.google.com');
};

const formatDate = (date, time) => {
    const hours = time.slice(0,2);
    const minutes = time.slice(3,5);
    date.setHours(hours);
    date.setMinutes(minutes);
    return date;
};

const assignColor = color => {
    switch(color) {
        case 'Red' : return 11; break;
        case 'Blue' : return 7; break;
        case 'Green' : return 10; break;
        case 'Yellow' : return 5; break;
        case 'Orange' : return 6; break;
        case 'Purple' : return 3; break;
        default : return 5;
    };
};

Next, we can set our write, edit and delete functions for shifts. newShift and changeShift both return the Event ID from the Google Calendar, this will allow AppSheet to keep track of what shift is what in the Google Calendar. They both take args representing each field within the column, and changeShift takes an id argument too so it can find the shift it needs to change in the Calendar. deleteShift only requires an id and does not return any data.

const deleteShift = (id) => {
    const cal = getCalendar();

    const shift = cal.getEventById(id);

    shift.deleteEvent();
}

const changeShift = (id, date, start, end, location, staff, color) => {
    const cal = getCalendar();

    const eventStart = new Date(date + 'Z');
    const eventEnd = new Date(date + 'Z');

    formatDate(eventStart, start);
    formatDate(eventEnd, end);
  
    const shift = cal.getEventById(id);

    shift.setTitle(staff)
    .setLocation(location)
    .setTime(eventStart, eventEnd)
    .setColor(assignColor(color));

    return shift.getId();
};

const newShift = (date, start, end, location, staff, color) => {
    const cal = getCalendar();
  
    const eventStart = new Date(date + 'Z');
    const eventEnd = new Date(date + 'Z');

    formatDate(eventStart, start);
    formatDate(eventEnd, end);

    const event = cal.createEvent(staff, eventStart, eventEnd, {location:location});
    event.setColor(assignColor(color));

    return event.getId();
};

Nice and simple! Now we need to go back to AppSheet editor view, and click on Automation in the menu bar. Click the + add new bot button, name it something like ShiftAddBot. Choose Configure New Event and click Create Event. On the right of the editor screen, name the event to something along the lines of Shift Add, set App as the event source, Shifts as table that should trigger the event, and highlight Adds as the data change type. In the Run window click Add Step, Name it something like Run Script and set the job as Custom task. On the right again, click Call a script, choose the Apps Script you wrote as the Apps Script Project, and choose newShift in the Function Name field. It will load all the function parameters automatically, and then we can choose which fields in the data will be passed in as the function parameters. These are the parameters for the changeShift function, any of the other functions only use a subset of these each:

Next, add a second step for the ShiftAddBot and name it to the affect of Set CalID. Select Custom Action and in the right settings pane select Set row values. Set column CalID with the value of [Run Script].[Output]. Run Script being the name of the previous step in the workflow, and Output being the return data sent back by the newShift function.

Repeat this process for a ShiftUpdateBot and a ShiftDeleteBot. The delete shift workflow only requires one step as we aren't handling any return data with that one.

Fixing Some Bugs

The bulk of the app is pretty much finished here. There are some controls we can add to prevent errors and a few things we ought to tidy up a little. The first thing I want to address is a bug, where if you work too quickly, Apps Script doesn't get enough time to send the calendar ID back to App Sheet before changes can be made. I found a pretty simple solution to this, which is to disable the edit and delete buttons entirely until a calendar ID is present within the data.

On the left menu bar, click on Actions, under Shifts select Edit, and under Behaviour, we want to enter a condition for Only if this condition is true. Click on the textbox, and type in ISNOTBLANK([CalID]). Do the same for the Delete button, and then a user will not be able to trigger the bug while using the app.

Next, it would be wise to prevent data entry that could cause errors with the Calendar API script we have made. The glaring opportunity for error here being that Finish should always be greater than Start. So in the Data view for Shifts, click the Edit button for Finish, and under Data Validity set Valid If to [Finish] > [Start].

Filtering Data

One last thing worth looking at before going on your own data organising journey with AppSheet is filtering data. AppSheet has something built in called Slices. A slice will take a chunk of data, and generate a new table based on filtering rules you provide. This is useful if you want to segregate a chunk of data when it meets certain criteria. For example, you might only want to access shifts that are in the future and haven't occurred yet. Slices can be used with Views as well as Ref's, which means you could limit the records that appear in a reference drop down based on a certain criteria. I use this feature in an attendance app, in which we only want to see sessions that are occurring today. However, slices aren't dynamic in the sense that the filter conditions are hard coded.

To look at creating a dynamic filter, we can make an hours tally for each member of staff, that we can then filter by selecting the start and end dates of a period of time to see how many hours each staff member has worked within that period. This is a little hacky to achieve, and not intuitive at all like slices are, so we will need to think outside of the box on this one.

We need to begin by adding two new sheets to our Rota spreadsheet. Pay Period and Pay Report. Pay Report will simply be one column named Staff, with each employee number listed as the values for each row. The easiest way to achieve this is to simply copy and paste that column from the staff sheet into this one. Pay Period will be only two columns: Start and End.

Once these tables are connected as new data sources into AppSheet, set the Staff datatype to Ref in the Pay Report table, and the Start and End datatypes to Date in the Pay Period table. In the Pay Report table, add a virtual column named Hours with a datatype of Decimal, and set the formula to the following:

SUM(
    SELECT(
        Shifts[Shift Duration],
        AND(
            [Staff] = [_THISROW].[Staff],
            [Date] >= ANY(Pay Period[Start]),
            [Date] <= ANY(Pay Period[End])
        )
    )
)

This formula will add together all of the hours a staff member has been rota'd to work, where the date for the shift matches ANY record within the Pay Period table's Start and Finish dates. The reason we can use ANY here is that there will only be one record in this table.

Next we need to make sure there is a view created for the Pay Period table and enter only one record for it with arbitrary dates. Once that is done, we will create a fourth main view for our primary navigation. Name it Pay Period and set it to the Dashboard view type. We will include two views in this dashboard, one named Pay Dates, that will use the Pay Period table as it's data source. It will be a Detail view type, with both of it's columns added to the Quick edit columns. We also want to disable the users ability to save, update or delete records here, however we still want data to be able to be changed by the app, so instead of disabling any of these in the Data settings, we can instead go to the Actions menu, and for Pay Period and Pay Report, set the Add, Edit and Delete buttons Behaviour setting for Only if this condition is true to TRUE = FALSE. These controls will not show now. Instead, when a user sets the Pay Dates and the app syncs, it will tally up each staff member's rota'd hours between the two dates selected. We can also set the Pay Period End column's Valid If formula to [End] >= [Start].

Our rota app is now finished. Changes made to shifts will automatically be uploaded to the Google Calendar identified within the Apps Script. This is only a simple app compared to what could potentially be made, but it covers quite a few things that might help you get started in making some useful tools for yourself at a low cost!