LinkedIn
Twitter
Give your AppSheet apps the superpower of connecting to 70+ enterprise applications!
AppSheet is a no-code development platform that allows you to quickly and easily build mobile and web apps that connect to your data. AppSheet is built on top of Google Sheets, so you can use all of the data and formulas that you're already familiar with. AppSheet also has a wide variety of pre-built templates and components that you can use to create your apps, so you don't have to start from scratch. AppSheet is a great way to empower your team to build the apps they need to improve their productivity and efficiency.
AppSheet can integrate with multiple data sources when you build its tables for your app's logic. It can also communicate with external systems, reacting to changes in its data using AppSheet Automation by issuing webhook or AppsScript calls. But it is not the job of AppSheet to model the data to the exact format the external system expects when issuing the webhook call. Also, it is not its job to be fully concerned with authentication and connectivity details to the external system (which might even be a private system running in your Datacenter!)
What if you want to synchronize events / data changes that happen in AppSheet with external systems, such as Salesforce, SAP or publish events to queues such as Pub/Sub or Kafka, or even open an issue in Jira Cloud or Zendesk? What if you can visually map the data you received from AppSheet to the format the target system expects - without code? Furthermore, what if AppSheet could receive updates about changes in such external systems and update its apps with such information? For all of these questions, the answer is Google Cloud’s Application Integration!
Wiring AppSheet Automation (with AppsScript!)
The first step is creating a new automation (Bot) in AppSheet. A Bot is basically an automation that links an Event (we'll use Data Change in this example) that triggers a Process - which is a series of Steps.
For my specific example, I'll trigger this automation when new rows are added to a table named "costs":
Now, the AppSheet automation step. Since the Application Integration API Trigger is a Google Cloud authenticated API - that is, you need the proper credentials (Google OAuth2 token with the right scopes / permissions) to invoke it (of course, for security reasons here!). Here is where the most complex portion of this tutorial comes up - we'll leverage the AppsScript Step to invoke the Application Integration API Trigger. Basically, I am saying here "hey AppSheet, when there's a new row added to the costs table, please invoke the function named invokeAppInt in the AppsScript project named App-Int." Notice that the user that the AppsScript script will be executed as must have Application Integration invoke permissions in Google Cloud IAM. More on that later -
On the AppsScript project, under Project Settings, make sure that you link it to the GCP Project where you have Application Integration running.
In the files section of AppsScript, make sure you have the following snippet of code in appsscript.json - the important portion here are the oauthScopes portion, which will enable the script to have the permissions required to invoke Application Integration. The AppsScript will be run with the credentials of the AppSheet app creator. For more details on invoking another authenticated API from AppsScript, please check this https://github.com/googleworkspace/apps-script-oauth2 .
{
"timeZone": "America/Sao_Paulo",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/cloud-platform",
"https://www.googleapis.com/auth/script.external_request"
],
"executionApi": {
"access": "MYSELF"
}
}
Finally, the actual code for invoking Application Integration (which is no more than executing an authenticated POST to a Google Cloud) is:
function invokeAppInt(req, appsheetId) {
var APP_INT_PROJECT = 'YOUR_PROJECT_NAME'; // GCP project ID
var REGION = 'YOUR_INTEGRATION_REGION'; // such as us-central1
var INTEGRATION_NAME= 'YOUR_INTEGRATION_NAME';
var INTEGRATION_API_TRIGGER_NAME ='YOUR_TRIGGER_NAME';
var url = encodeURI('https://integrations.googleapis.com/v1/projects/' + APP_INT_PROJECT + '/locations/'+ REGION + '/integrations/'+ INTEGRATION_NAME + ':execute');
var token = ScriptApp.getOAuthToken();
var options = {
method : 'post',
contentType: 'application/json',
payload : JSON.stringify({trigger_id:'api_trigger/'+ INTEGRATION_API_TRIGGER_NAME,
inputParameters: {"requester": {"stringValue": req}, "appsheetId": {"stringValue": appsheetId}}
}),
muteHttpExceptions: true,
headers: {
Authorization: 'Bearer ' + token
}
};
var response = UrlFetchApp.fetch(url,options);
Logger.log('RESULT:' + response.getContentText());
return JSON.parse(response.getContentText()).outputParameters.ticketId;
}
What happened in this snippet?
- First of all - notice that we chose our function to have two input parameters - "req" and "appsheetId". Those were arbitrary column data that we chose to send to Application Integration from AppSheet:
- To see these variables in Application Integration, here is a screenshot:
- Then, we build the URI for invoking the right integration / API trigger in Application Integration.
- For getting the right name of your API Trigger in Application Integration, select your API Trigger and in the right-hand panel, get its name:
- Next, the ScriptApp.getOAuthToken() portion natively gets a Bearer token, with the right scope/permission for invoking the API.
- Then, we build the actual request. In our example, we are using the two variables we received from AppSheet as input variables to Application Integration. Notice that Application Integration expects the inputs to come within a specific payload, which we are building also in our call. You'll need to customize your payload according to the input variables your Integration expects.
- The UrlFetchApp.fetch method actually issues the call
- We are also getting the return of this method (which the outputs from Application Integration, as we invoked Application Integration synchronously) - more specifically, ticketId in my example.
And that's it! Notice that AppSheet can access this return variable as well as [Step].[Output] (in my example, [Invoke Integration].[Output])
I actually added one extra AppSheet automation step to the flow - I took this return variable and updated a row with new information (that came all the way from Application Integration):
Summary
We did a lot! Let's summarize:
- We use AppSheet Automation as a means to detect changes to data in AppSheet and then we invoke an AppsScript script
- The main reasons for us to use AppsScript (and not a generic Webhook) are basically for manipulating the input to what Application Integration expects and also because we need to have Google Authentication (OAuth2, IAM permissions) in our call
- We can send and retrieve variables across both AppSheet to Application Integration
- We just need to make sure the user that AppSheet executes as HAS the right IAM permissions to invoke Application Integration
