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?
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:
Awesome article Carlos! Great job.