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.

Google AppSheet

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.

Overview of No-Code

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.

Why No-Code Works Well

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, Workspace APIs, and Apps Script

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.

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.

Introducing the Apps Script connector for AppSheet

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.

Using the Apps Script connector for AppSheet

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.

Here’s how to set this up in five easy steps:

1. In AppSheet, select the Automation screen, create a new Bot with an associated Event to listen for, and finally and choose the Call a script option for the Task:
   
   

   Configuring an AppSheet Automation Task to call an Apps Script function

2. Then, under “Apps Script Project,” choose the script to call. This code will be created using the Apps Script editor, and will be in App Creator’s Google Drive (it can be shared with them by someone else):
   
   

   Choosing the Apps Script Project to call from Google Drive

3. Once selected, a list of the available functions in the Apps Script project will be shown in the Function name dropdown. Here, choose the function to call from AppSheet.
4. After the function to call is selected, a list of its parameters — additional data passed in when calling the function – is then shown.
   
   

   Displaying the parameters to pass to the logTravelRequestUpdate() Apps Script function

5. For each function parameter, use the AppSheet Expression Assistant to specify what value to pass for the parameter when the automation is called. An expression can be a simple column in the AppSheet app’s data source, or a more complex function or computation involving multiple columns.



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.

Example Code

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:

Getting Started

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

Sample Scripts

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.

Posted by Peter Jacobsen, Technical Writer

OAuth 2.0 is an open standard authorization framework for token-based authorization on the internet. An OAuth 2.0 access token is a string that the OAuth 2.0 client uses to make requests to the resource server, and hides the user's identity or other information from the OAuth 2.0 client. Only use access tokens to make requests to the resource server.

Offline refresh tokens

Access tokens periodically expire and become invalid credentials for a related API request. If you requested offline access to the scopes associated with the token, you can refresh an access token without prompting the user for permission, even when the user isn't present.

As a best practice, set the expiration time for refresh tokens for a little longer than the access tokens. For example, if you set the expiration to 30 minutes for an access token, set the refresh token's expiration to 24 hours or longer.

For more information, see Refreshing an access token (offline access).

Online access

Some apps may request that the user reauthenticate after a shorter period of time, which relies on the access token alone rather than a refresh token. These apps have online access as opposed to those that have a refresh token and are considered to have offline access.

For more information, see Refreshing an access token (offline access) and Refresh tokens.

JSON Web Token (JWT) and token expiration

To authenticate to Cloud IoT, each device must prepare a JWT. JWTs are used for short-lived authentication between devices and the MQTT or HTTP bridges.

JWTs are composed of three sections: a header, a payload that contains a claim set, and a signature. The header and payload are JSON objects that are serialized to UTF-8 bytes and then encoded with Base64 URL encoding.

The JWT's header, payload, and signature are concatenated with periods. As a result, a JWT typically takes this form:

{Base64url encoded header}.{Base64url encoded payload}.{Base64url encoded signature}

For more information, see Using JSON Web Tokens (JWTs) and Managing JWT token expiration.

Common token expiration paradigms

There are different policies and strategies that you can use to manage token expiration. You can:

• Monitor your HTTP responses, look for 401 HTTP responses, and respond accordingly.
• Check the token's expiration date proactively to determine the validity of the token before you make an HTTP request to the resource server.
• Combine the previous two strategies to handle expirations where a valid token can expire during the request that causes a 401 HTTP Response.