Posted by Mike Procopio, Senior Staff Software Engineer
This week, we launched the Apps Script connector for AppSheet, which now makes it possible to call Apps Script code functions from a no-code AppSheet app. This greatly extends the abilities of AppSheet apps by letting them access the power that Apps Script provides. For example, an AppSheet app can now use Apps Script to automate workflows with Google Workspace using the Workspace APIs for Drive, Docs, Sheets, and Admin SDK, and more – as well as other Google services like YouTube, Google Analytics, and BigQuery.
AppSheet is Google’s platform for building no-code custom apps and workflows to automate business processes. It lets app creators build and deploy end-to-end apps and automations without writing code.
Editing an App Definition in Google Appsheet
Before we dive into the Apps Script connector feature, let’s take a look at what we mean by no-code. No-code platforms like AppSheet enable citizen developers to quickly build apps automatically through data analysis using web-based user interfaces. These are typically built on top of a data source like Google Sheets, MySQL, Salesforce, or other database. Creating these apps doesn’t require a traditional software engineering background; business technologists and analysts with a wide range of skill sets can build apps that make their work easier and more efficient.
In a no-code platform like AppSheet, an app creator uses a web-based editor UI to define how the app should behave, instead of writing specific code to achieve that. They’ll specify things like what the app should do, what data source it's built on, what the data means, what UI the user sees, what events to listen for and what actions to take when they occur, and so on. The app is then deployed in production as an end-to-end, data-driven app with a UI, all without “writing” any actual code. This is all transparent to the end user, who simply sees a standard web or native app.
In practice, no-code platforms work very well for a large class of apps. Why? Because many apps are functionally very similar – in particular those that implement internal business processes. These apps share many common traits: A data source for storing data, a concept of a data “record,” UIs and forms to collect and edit data, user-specific views of that data, expressions to define transformations of data, actions to take when data is changed, admin controls to manage permissions, and so on.
For example, consider an app for creating and approving an employee travel request. This app shares many common traits with an app for, say, creating and approving expense reports. In both apps, an employee creates a record which gets reviewed and annotated, users are notified when changes are made, and eventually the request is completed and closed out. Similarly, an internal app for tracking employees’ assigned computer equipment has a lot in common with any generic inventory tracking, fulfillment, and ordering system.
A no-code platform’s challenge is always balancing expressiveness with high-level abstraction. Fortunately, business processes tend to conform to a well-understood core set of expectations around data, data lifecycle, and process. This is why no-code platforms perform well in this space. However, there may be times when the requirements of the app go beyond what a no-code platform can provide. The new Apps Script connector greatly improves the expressiveness (capabilities) of a no-code app – ensuring that the app can grow to accommodate changing requirements or business processes as they evolve.
Google Workspace is Google’s cloud-based productivity suite, enabling teams of all sizes to connect, create, and collaborate. You may be familiar with many of these apps, like Gmail, Calendar, Drive, Docs, Sheets, Slides, and others. Each of these apps also comes with a powerful developer API that lets app developers programmatically integrate with it. For example, the Drive API allows its users to upload and download files with code, the Gmail API lets apps integrate with its messaging functions, and the Google Docs API allows script-based editing of Google Docs.
Using Google Workspace integrating Chat, Gmail, and document previews
Apps Script is a low-code platform that makes it quick and easy to build business solutions that integrate, automate, and extend Google Workspace. It does this by providing easy-to-use high-level “wrappers” around APIs, enabling custom integrations with Google Workspace and external services. Apps Script also integrates with Google services such as YouTube, Google Analytics, and BigQuery. It is a powerful platform, enabling script authors to implement complex business logic and macros and call external services, for example a third-party vendor’s data exchange system or a machine learning classification service.
Editing a script in the Google Apps Script code editor
The Apps Script connector for AppSheet, launched this week, ties everything together: AppSheet, Apps Script, Google Workspace, and Google Workspace’s many developer APIs. This integration lets no-code app developers using AppSheet greatly extend the capabilities of their no-code apps by allowing them to call and pass data to Apps Script functions. One way to think about this integration is that it bridges no-code (AppSheet) with low-code (Apps Script). Importantly, the person who writes the Apps Script script doesn’t have to be the same as the no-code AppSheet app creator that calls the script (and often won’t be).
From the Apps Script developer perspective, this opens the door to some important capabilities. One of AppSheet’s biggest benefits is that it allows you to easily deploy an actual end-to-end app with a UI (frontend). With the new ability to call your existing Apps Script code, creating or replacing any existing front-end with a no-code AppSheet app becomes a potentially game-changing option.
In AppSheet, configuring this is straightforward and requires no code other than the script itself. The core idea is that the app creator chooses the Apps Script script to call by configuring an AppSheet “Task,” which is an action taken when an AppSheet Automation is triggered.
Such an action typically occurs in the context of a specific data row in AppSheet’s data source; for example, an automation could be triggered when a new data record is created by one of the app’s end users. Finally, the app creator can specify values to pass in to the parameters of the Apps Script function based on values in the active data record.
Editing an Automation Task in Google AppSheet
Here’s how to set this up in five easy steps:
Configuring an AppSheet Automation Task to call an Apps Script function
Choosing the Apps Script Project to call from Google Drive
Displaying the parameters to pass to the logTravelRequestUpdate() Apps Script function
Using the AppSheet Expression Assistant to choose which values to pass to the Apps Script function parameters
Finally, save the Task in the AppSheet Editor using the Save button.
And that’s it! When the automation is triggered (for example, when a user adds a new data row), the AppSheet automation engine will call the specified Apps Script function, passing in the values from the evaluated expressions as function arguments. Details about the function call will be available in the AppSheet Automation Monitor.
Here’s a simple example you can try on your own based on a Travel Request Tool AppSheet app shown in the above screenshots. (More examples are also given in the Sample Scripts section below.) The code below is an Apps Script function that adds details about an employee travel request to a Google Docs document simulating an “audit log”. It uses the Google Docs API via the DocumentApp wrapper library available in Apps Script.
TravelRequestAuditLogger.gs
function logTravelRequestUpdate(RequestDate, Requestor, Origin, ...) {
var TRAVEL_REQUEST_AUDIT_LOG_DOCID = 'your_docid_here'
var doc = DocumentApp.openById(TRAVEL_REQUEST_AUDIT_LOG_DOCID)
var body = doc.getBody();
var dataParagraph = body.insertParagraph(0, '');
var headerText = dataParagraph.appendText('*** NEW AUDIT LOG ENTRY ***\n');
headerText.setBold(true);
// Add in the audit log entry metadata.
dataParagraph.appendText('New Travel Request Tool audit log entry...\n');
dataParagraph.appendText('RequestDate: ' + RequestDate + '\n');
dataParagraph.appendText('Requestor: ' + Requestor + '\n');
dataParagraph.appendText('Origin: ' + Origin + '\n');
// ... additional values here to append to Google Doc
}
When this function is called, for example from an AppSheet automation when a new travel request is created, text will be added to the Google Docs document based on values passed in from AppSheet. The document will look similar to below when it is updated:
Example Google Docs document output, created from using the Google Docs API in Apps Script with values passed in from AppSheet
We’ve created a list of resources to help you quickly get started and get community support.
- Help article, Call Apps Script from an automation
- Overview video
- AppSheet Community
- Apps Script sample solutions
Here are three sample scripts we’ve created to help you get started.
Sample Google Workspace Project - Create a Doc, Sheet, Slide or send an Email.
Create a Calendar Event - Create a customizable Calendar event.
Send an interactive Chat message - Send a message in Google Chat that opens a link or displays an image.
We’re very excited about this announcement and we can’t wait to see what you build for Google Workspace! To stay in the loop on announcements about AppSheet and the Google Workspace Platform, please subscribe to our developer newsletter.