There are lots of use cases that would be facilitated by an ability to reference how recently a user was last online. Examples include:
- Identifying who may have outdated offline data
- Limiting a customer's active users to the number they've licensed, but without limiting them to named seats (e.g., provide a license for concurrent monthly users) and without having to tediously maintain an app allowlist (although without an allowlist, domain authentication is necessary, such as via AWS Cognito)
There has been some discussion of whether there's a straightforward way to do this, ideas for hacky proxies, and suggestions for feature implementation. As it turns out, there's been a way to access this data all along--although there are some evident limitations, as well as possibly some lurking ones.
Overview of steps
- Generate an API key for your AppSheet account (not for an individual app)
- Use the API to Monitor team billing information
- Get the resulting CSV-formatted data into a table you can reference from an app
Example
I'm using a Google Apps Script to get the user activity via the AppSheet account-level API and then write it to an app using the AppSheet app-level API. I defined a time-driven trigger for my script that runs on the frequency I need (theoretically, the trigger could instead be a Call a script task in an AppSheet automation). If you're comfortable with API requests and responses, it's mostly straightforward.
One part I found complex is transforming the API response into the format I need for adding the data to an app. One layer of complication is simply that the response is in CSV format (as opposed to, say, JSON). Also, there are some oddities (perhaps bugs?) in the CSV--including an unknown first character and a comma at the end of each line. Here's the CSV header line showing these issues, as well as the header names for your reference.
๏ฟฝ"User Id","User Email","App Count","Deployed App Count","App Names","Last Access",
Here's the portion of my script with the various contortions to transform from CSV to an array of JavaScript objects. These steps may be clunky and I won't be surprised to learn there's a smoother approach, but it's working reliably.
// FUNCTION EXCERPT
let responseCSV = response.getContentText();
responseCSV = responseCSV
.replace(/\p{Cf}/gu, '') // Remove initial unknown character that appears at beginning (from https://stackoverflow.com/questions/11598786/how-to-replace-non-printable-unicode-characters-javascript)
.replace(/,$/gm, '') // Remove trailing comma at end of each line
let responseData = Utilities.parseCsv(responseCSV); // Convert csv to array
responseData = arrayToJSONObject(responseData); // Convert two-dimensional array to array of objects
// SEPAPATE FUNCTION
function arrayToJSONObject (arr){
// From https://morioh.com/p/9acdbd845f36
//header
var keys = arr[0];
//vacate keys from main array
var newArr = arr.slice(1, arr.length);
var formatted = [],
data = newArr,
cols = keys,
l = cols.length;
for (var i=0; i<data.length; i++) {
var d = data[i],
o = {};
for (var j=0; j<l; j++)
o[cols[j]] = d[j];
formatted.push(o);
}
return formatted;
}
Summary of limitations
- Using the account-level AppSheet API seems to require an Enterprise subscription, although I confirmed that it's possible to at least generate an API key in a non-Enterprise account--so, seemingly available to test drive in any account.
- The last access timestamp is not at the app level, but rather at the account level--if a user has accessed multiple apps, it's unknown which app was accessed when.
- The data is only for the last 30 days--if you need older data, you need to save snapshots and reference those.
- It's unknown (to me) how current the data is--potentially, there's enough lag to preclude relying on this technique for some use cases.
Finally, of course, keep in mind that this "last access" data presumably does not directly reflect offline app usage (which is a great feature of AppSheet apps) but rather only represents the points in time when a user's data syncs to or from the AppSheet server.
LinkedIn
Twitter
