Deploying Identity-aware Management using Apps Script and AppSheet


AppSheet is a no-code tool to build mobile and web apps without any coding experience.

  • The AppSheet platform helps to build an app for iOS, Android, and the web without ever having to write a line of code
  • AppSheet integrates with different data sources such as  Google Sheets, Excel, Cloud SQL, and Salesforce.  App user activity syncs to the connected data sources
  • AppSheet leverages the data to display it through maps, charts, calendars, and dashboards
  • Automated workflows can be added into apps for doing things such as sending notifications, generating emails, and modifying data across any connected sources
  • AppSheet helps customize user experiences such as colors, icons, images, fonts, and other actions. A trigger can also be added to schedule a job for a later time
  • AppSheet requires licensing for users to develop and deploy applications

Apps Script

Google Apps Script is a platform used for automation and extending the functionality of Google Apps.

  • App Script helps in building a lightweight cloud application that integrates with Google’s workspace
  • Code can be written in Javascript and have built-in libraries for Gmail, Drive, and many more
  • All Google workspace products come with an in-built editor to write custom code and edit the scripts that run on the google server
  • There is no need for installing any IDE’s or software for testing the code. The security and authentication are handled by the platform itself
  • Apps Script runs server-side code, but the HTML, CSS, Javascript can also be supported as an interface

Identity-aware management using AppSheet and Apps Script

Identity awareness is the key to a successful knowledge management system. It not only helps to give the authenticated users access to the resources but also helps in logging the user activity within the system. Apps Script can be used to track the level of authorization or role of the user in the system. Using this, a unique role-based option (to perform specific actions related to the document) can be provided to the users.

In the current use case, we are considering a serial approval system for approving changes in the document. This system would be aware of the user and would pull their role in the system. Post document approval, the status’ will be stored continuously in the spreadsheet. Apps Scripts seamless integration within Google Workspace helps connect Google Docs and Google Sheets in the same codebase. Thus, allowing a combination of status and user role from Sheet with user identity from Doc. This is used to provide very specific options to the user. Again based on the actions selected and current status, specific and complex operations can be performed on the Doc. This integration not only allows operations on Doc but on any other service within the workspace. Some examples are sending emails on specific events or user actions (uses GMail), scheduling Calendar events (uses Calendar), creating new documents (uses Google Doc), changing the GDrive access of a user. Here is the demonstration of our use case:

SpringML Knowledge Management Solution

AppScript & AppSheet: Implementation

AppSheet provides various views for developing the UI. Data from Google Spreadsheets can be presented on a dashboard with the capability to customize it using various filters. It also enables the user to edit a specific row/entry or add data to the given sheet. Additionally, it also allows building dynamic forms that can fetch data from different sheets. A custom menu item within a document is used to log the status of a document. These menu items can be created using Apps Script, which can be used to link a document to an existing Spreadsheet where the status of the document’s approvals along with other details are stored. Apps Script provides custom APIs for creating pop-up menus and buttons that help in enabling the user to either Approve or Reject a document’s changes and upon clicking the button, the approval status is stored in the sheet and can also be used to notify other users through emails.

Adding Menu using Apps Script

To add menu options and custom functionalities Go to Tools > Script editor.

Adding Menu using Apps Script

This will open the script editor wherein you can write the javascript code to add the customization as per your use case. Here is the code to add the custom menu on the top bar as given below

script editor

Once we have the code saved in the Apps Script project, on opening the document based on the user and status of the document, the menu option (SpringML) to perform the action will be displayed on top.

script editor heading

Approval Process using Apps Script:

Approval Process using Apps Script

Dynamic form using AppSheet

This form is an extension of Google Forms with all the new features added on top, such as referencing the other document in a dropdown and many more capabilities

agenda submission form


All the status’ and other approval steps can be visualized from the dashboard at a single place using AppSheet views

agenda submission form

We have just explained one use case of Appsheet and Apps Script. The possibilities one can achieve using these two cutting-edge technologies are endless.

Thought Leadership