How to Integrate Google Apps Script & Trigger with an AppSheet App

[LeventK]

---

1 - INTRODUCTION

---

Dear valuable members of the community,

This post is my personal long-time promise to @praveen that I’ll write about nearly a year ago but couldn’t be able to keep my promise and spare some time because of our work load as an AppSheet Partner and Developer. Now I have found some time, I decided to write it down. Hope you can find a benefit of this post for your apps and/or projects.

Before starting and diving any deeper, I should admit that this requires at least a beginner level of Google Apps Script or JavaScript knowledge, because as the possibilities might converge to endless and it’s not possible to cover out every app/development scenario here; it’s a bit more than just copy&paste. Therefore, besides this post you need to read some documentation, make your hands dirty and run thru some trial&errors. Hence, I will try to explain the basics and how to get it working with your AppSheet App.

The best resources - amongst many others - can be listed as:

• Google Developers | Apps Script
• Bruce McPherson | Going GAS
• Book of GAS | Bruce McPherson

This will be a bit multiple post items linked to each other under this thread, so thank you for your patience.

---

2 - THE DEVELOPMENT PLATFORM

---

Any Google Apps Script project, can be developed in 2 platforms or editors:

• In a Google Document container (gDoc, gSheet etc.)
• In a Standalone Script File (needs Google Apps Script add-on attached to your gDrive)

To access the Script Editor from any Google Document, you can simply choose Tools > Script Editor. To attach Google Apps Script add-on to your gDrive, please refer to my older post here:

[Executing a DeepLink action from an Email Silently](https://community.appsheet.com/t/executing-a-deeplink-action-from-an-email-silently/10992/2) Tips & Tricks ?

[STRUCTURE AND THE APP BUILD] I will try to explain briefly but as detailed as possible on how to do/provide such a functionality in your app. Please keep in mind that, as mine was just a public sample, I have provided/triggered the functionality with an action button on the Detail View of the record. So as far as the “structure” is preserved, you can add the same functionality in any part of your app build. I will go step by step and part by part for you ease of following and understanding …

---

3 - GOOGLE APPS SCRIPT TRIGGERS

---

There are 2 kinds of triggering (or you can simply call it as execution) with Google Apps Script:

• Simple Triggers: Triggers let Apps Script run a function automatically when a certain event, like opening a document, occurs. Simple triggers are a set of reserved functions built into Apps Script, like the function onOpen(e), which executes when a user opens a Google Docs, Sheets, Slides, or Forms file.
• Installable Triggers: Like simple triggers, installable triggers let Apps Script run a function automatically when a certain event, such as opening a document, occurs. Installable triggers, however, offer more flexibility than simple triggers: they can call services that require authorization, they offer several additional types of events including time-driven (clock) triggers, and they can be controlled programmatically.

For both simple and installable triggers, Apps Script passes the triggered function an event object that contains information about the context in which the event occurred. Installable triggers offer more capabilities than simple triggers but must be activated before use.

To use a simple trigger, simply create a function that uses one of these reserved function names:

• onOpen(e) runs when a user opens a spreadsheet, document, presentation, or form that the user has permission to edit.
• onEdit(e) runs when a user changes a value in a spreadsheet.
• onInstall(e) runs when a user installs an add-on.
• doGet(e) runs when a user visits a web app or a program sends an HTTP GET request to a web app.
• doPost(e) runs when a program sends an HTTP POST request to a web app.

The e parameter in the function names above is an event object that is passed to the function. The object contains information about the context that caused the trigger to fire, but using it is optional.

There are several installable triggers for G Suite applications:

• An installable open trigger runs when a user opens a spreadsheet, document, or form that they have permission to edit.
• An installable edit trigger runs when a user modifies a value in a spreadsheet.
• An installable change trigger runs when a user modifies the structure of a spreadsheet itself—for example, by adding a new sheet or removing a column.
• An installable form submit trigger runs when a user responds to a form. There are two versions of the form-submit trigger, one for Google Forms itself and one for Sheets if the form submits to a spreadsheet.
• An installable calendar event trigger runs when a user’s calendar events are updated—created, edited, or deleted.

You can use installable triggers in standalone and bound scripts.

In order to execute a Google Apps Script, with any data change in the back-end which is manipulated by an AppSheet app, you need to use an installable change trigger which uses a Google Document as a container.

My partner @Bellave_Jayaram is also a coding expert. Jay, no need to say but your contributions are always welcome bud.

[LeventK]

---

4 - GOOGLE APPS SCRIPT & CODING

---

Let’s get our hands dirty now…
When you launch your Script Editor, the editor by-default opens with a Code.gs namespace (note that this name will change as per your preferred or set language) where we can write our script code. You can give a desired name to your script function(s) but prevent using special or accented characters (umlauts like in German, Finnish, Turkish etc).

Function or code syntax is pretty straight-forward just like the AppFormulas

function YourFunctionName(function parameters here if any, each seperated by comma) {
	Your script code goes here;
}

As we will be working with an installable trigger, our function will take only a single parameter, which is the event object denoted by e. So we can wrap-up and re-write the same code above:

function YourFunctionName(e) {
	Your script code goes here;
}

So let’s take a look at the event objects we have with the Google Spreadsheet:

Event Object	Description
authMode	A value from the ScriptApp.AuthMode enum.
	LIMITED
changeType	The type of change (EDIT , INSERT_ROW , INSERT_COLUMN , REMOVE_ROW , REMOVE_COLUMN , INSERT_GRID , REMOVE_GRID , FORMAT , or OTHER).
	INSERT_ROW
source	A Spreadsheet object, representing the Google Sheets file to which the script is bound.
	Spreadsheet
triggerUid	ID of trigger that produced this event.
	4034124084959907503
user	A User object, representing the active user, if available (depending on a complex set of security restrictions).
	able3@able3ventures.com

As we are mainly working with Google Spreadsheet, the Event Object that we will be referring to is therefore the source object. Depending on what you want to achieve as a result, you can surely use other event objects too. With using event objects, we can then refer to key parameters of event objects to read/verify spreadsheet objects, parameters and values as well.

To achieve this functionality, we first need to assign our event object to a variable, so that we can make a call to their key parameters to access their values. So let’s construct a script fuction named myFirstGAS, create a variable named sSht (you can give any name you like, but the best practice is always mimic the functionality so I have chosen a name of sSht which denotes to <Spreadsheet>) and assign our event object to this variable.

function myFirstGAS(e) {
	var sSht = e.source;
}

if you run this code, you get an response of [object]. As it returns the whole spreadsheet itself, we need to learn in which sheet the data is changed. So we need to refer to getActiveSheet() key parameter of the event object. Let’s do that:

function myFirstGAS(e) {
	var sSht = e.source;
	var sht = sSht.getActiveSheet();
}

As you might have also noticed, I have assigned this to a variable named as sht. Now we know the active sheet but we still don’t know if it’s the sheet that we are looking for. Assume that we are looking for the change in sheet named MyTestSheet. So we need to learn the assigned name of the active sheet. To accomplish this, we need to refer to the getName() key parameter of the event object.

function myFirstGAS(e) {
	var sSht = e.source;
	var sht = sSht.getActiveSheet();
	var shtName = sht.getName();
}

OK. We now know the name of the active sheet, and we want our script to be executed IF the sheet name is MyTestSheet. As you are already familiar from the AppFormulas also, we need to construct an IF statement inside our code to achieve that:

function myFirstGAS(e) {
	var sSht = e.source;
	var sht = sSht.getActiveSheet();
	var shtName = sht.getName();
	
	if (shtName === "MyTestSheet") {
		Your code goes here
	}
}

Whatelse do we need to check? Is that enough? Don’t we need/want to check what is the range and/or row that had changed? Yes we definitely do. We achieve this with a couple of parameters. These are mainly:

• getActiveRange() - returns the range object of the changed range
• getRow() - returns the row number of the changed range
• getColumn() - returns the column index of the changed range (provided the a complete range is changed instead of a single cell, this parameter returns only the starting column of the range)
• getWidth() - returns the width of the changed range (i.e. if the changed range is A2:C2 then this parameter will return the value of 3)
• getHeight() - returns the height of the changed range (i.e. if the changed range is A2:C3 then this parameter will return the value of 2)
• getA1Notation() - returns the changed range’s A1 notation (i.e. A1:B2)
  You can take a look at the entire list of parameters from here > Google Developers | Range Parameters

With the information set; let’s check if the changed sheet is MyTestSheet and the changed row is Row(5):

function myFirstGAS(e) {
	var sSht = e.source;
	var sht = sSht.getActiveSheet();
	var shtName = sht.getName();
	
	if (shtName === "MyTestSheet") {
		var activeRng = sht.getActiveRange();
		var activeRow = activeRng.getRow();
		
		if (activeRow === 5) {
			Your code goes here;
		}
	}
}

[LeventK]

---

4 - GOOGLE APPS SCRIPT & CODING (continued)

---

OK. We now know which row is changed but we still don’t know if the changed data/cell is the one which we are monitoring so we need to expedit it. To accomplish this, we first check if a single cell or a range of cells had been changed and according to this condition we will check if our monitored cell has been covered or not.

Let’s assume that we are monitoring a data change in Column D and execute our script if the value of this cell has been changed to a value of i.e. “AppSheet”. As we always be monitoring the change in the active range, actually we don’t need to check the row number. So our code will a bit change here:

function myFirstGAS(e) {
	var sSht = e.source;
	var sht = sSht.getActiveSheet();
	var shtName = sht.getName();
	
	if (shtName === "MyTestSheet") {
		var activeRng = sht.getActiveRange();
		var rngW = activeRng.getWidth();
		var activeCol = activeRng.getColumn();
		var activeRow = activeRng.getRow();
		var myVal = sht.getRange(activeRow, 4).getValue();
		
		if (rngW == 1) { //checking if the changed range is a single cell
			if (activeCol == 4 && myVal === "Appsheet") { //We are checking for Column D where index of Column A is 1
				Your code goes here;
			}
		} else { // or multiple cells (a range)
			var rngStartIndex = activeCol; //we defined the range's start index
			var rngEndIndex = activeCol + rngW - 1; //we define the range's end index
			
			if (rngStartIndex <= 4 && rngEndIndex >= 4 && myVal === "Appsheet") { //checking if Column D (index=4) falls into the start and end
				Your code goes here;
			}
		}
	}
}

In Your code goes here lines, you can execute whatever you want. You can make calculations and change the value of other cell(s) in the same sheet or other sheet(s), you can make REST API calls, you can create PDFs, Excel or CSV files etc. As I have mentioned in the beginning, the possibilities are endless. For sure, the sample code snippets I have given are very very basic. You can make other checks or verification of data, you can loop thru data ranges etc.etc. This totally relies on your needs and your development approach. On the last step, I will explain how you will trigger the script you have created.

[LeventK]

---

5 - GOOGLE APPS SCRIPT TRIGGERING

---

On the last step, we will set the installable trigger, so that the code will be automatically executed when the data in the spreadsheet changes. The code will be executed if the conditional criteria inside the code is met.

To set the trigger, goto Edit > Current project’s triggers menu item on the script editor

A new browser window will be opened, showing all the triggers set for that spreadsheet container so far. If you haven’t set any, the window will be empty and no triggers will be listed

Now click on Add Trigger button and your trigger set-up form will be poped-up. You need to set it like this:

• From Choose which function to run dropdown, select your function name

• From Which runs at deployment leave it as Head

• From Select event source dropdown, select From spreadsheet

• From Select event type dropdown, select On change

• From Failure notification settings select Notify me immdiately

• Click Save and your trigger will be set

  

Now, each time you add/edit data in your AppSheet app, the set function will be triggered and your script code will be executed if the conditional criteria is met.

[Mike_A]

@LeventK - Thanks for taking the time to write this mini session and share with the crowd. As you can see from my profle, I am an “Appsheet and google apps script (GAS) enthusiast”, and see this combination as an extremely powerful solution. In case those reading did not make the connection, by using a little google sheet scripting, you can actually set things up so when your appsheet app changes specific data in the google sheet(s), you can then have google scripts take over and provide all sorts of actions.

An hypothetical example might be, when a user completes a job, you need the following to happen:> > 1. A summary spreadsheet of those jobs is created (not a pdf).> 1. An email is sent to each person on that list.> 1. plus an email is sent to two managers depending upon which job was completed, with a survey requesting feedback about the work.> 1. those records are archived in a specific google sub-folder (both as PDF and single google sheets).> 1. and then the completed jobs are deleted from the main data.

Some of this can be done by appsheet directly, but others actions cannot currently, and may be specific to the back end environment (like creating an archive folder on google drive).

The partnering of the two solutions is very very powerful and has so much potential!

Using Appsheet to handle the great mobile app experience and automation at the single record level, and google sheets/scripts to handle the back office work. I know this gets a lot more complicated than a “no-code” platform is designed to handle (and maybe should not be expected to) – which is why partnering Appsheet with a scripting environment can be a real one-two punch!

Might be more than the typical Appsheet audience is looking for, but if you hit a capability “wall”, you can always consider leveraging things like Google Scripts. Maybe the answer is not to turn Appsheet into another “low-code” platform, but maybe continue the work to improve how Appsheet leverages other environments (more than just Zap’s). Just some thoughts…

[LeventK]

Thanks @Mike, your thoughts are very valueable really. I’m aware that you’re a GAS enthusiast too, so I appreciate you liked it. I have no idea if I could be able to express the idea in a way that everybody can understand, test and use but I have tried my best as this was my long-time personal promise to @praveen

As I have mentioned in the beginning of the post, in fact the concept of GAS is not so hard to grasp provided you have the time, faith and love pain for sure I haven’t explained or dive deep for logging and debugging of the codes, but that’s totally another concept. In earlier times of AppSheet, we had used onChange(e) trigger a lot really but nowadays AppSheet can do nearly everything that you need without the need for coding. However, the power of scripting - especially combined with external resources i.e. RESTful API - makes it like Transformers Dealing with coding nearly 3 decades now, so I’m somehow addicted to it and I love coding myself.

The possibilities of using GAS with AppSheet is endless I believe. You can even connect an AppSheet app with the Kubernetes engine I’m nowadays exploring GAS + Raspberry Pi + AppSheet + IoT integration. I have bought a Raspberry Pi W Zero with some additional stuff (i.e. night cam module etc.) and managed to get a notification to my sample AppSheet App upon a camera detection for example. So if you are really interested in the current technology, the possibilities are really endless. For example I’m very curious about integrating Machine Learning and AI with AppSheet.

Anyway, I can talk till morning Thanks for your input on this Mike.

[Mike_A]

@LeventK - looks like I struck a passionate cord! Really appreciate all your (and the other Appsheet team members) efforts to share and help us re-imagine how to get “stuff” done. The wonderful thing is there are endless possibilities if you have the desire to learn, the imagination, and the endurance. The hard part is, how to segment the Appsheet audience so people get what they need, at the level they are, and can learn more when ready. @Peter wondering out loud - is the community big enough to have another section to discuss the deeper topics (more than no-code)? Maybe a an area for “pushing the envelope”?

[anonymous]

Hi @LeventK
very good post! I am now build a GAS to export data in a specific format using the script to fill a txt file! Amazing!
I have, instead, a little issue: it seems that the trigger do not fires if che data change isa made by tha app and not directly on the sheet. Do you had the same problem during your tests?

[LeventK]

@Andrea_Saccavini
Provided you have set the onChange(e) trigger correctly, it should work when the AppSheet app makes any changes in the sheet i.e. adding, deleting or editing rows. If you have set it with onEdit(e) it won’t work.

[anonymous]

Hi,

Is it possible to run an onEdit() trigger on a public Google Sheets file that does’t belong to me and i don’t have edit permission on it?

When i’m in it i don’t see a Script option under Tools.

Is there a workaround?

[LeventK]

Provided you don’t own the sheet or you don’t have edit access you cannot access the script editor. Provided you have the edit access, you cannot set a trigger in a gSheet container that you don’t own because of the permissions scope is not on your account.

[anonymous]

Oh you are right! Damn the translator! In italian it was not clear which option to select!
Thanks a lot!

[anonymous]

Could I use an installable trigger (onOpen) and compare the user email to a master email list in a drive to protect a Google Sheet from being copied without authorization? I’m think select all cell in the active workbook nd clearing those if the email is not found.

Thanks for the help

[Ami]

I have trird last month to get a google script with a google map API to calculate a distance between two addresses. Though the script worked, the API had no effect and once i hit the daily quota the script stopped working. Any help you can provide may be very helpfull. Also, im looking as well into IOT integration with appsheet and hope you can share your thoughts as well.
Thanks

[Bellave_Jayaram]

It is not clear what problem you are trying to solve. Of course you can clear contents if the user is not allowed to view or copy the data but why and how would that user have obtained the link to the sheet in the first place?

[anonymous]

I’m looking for a way to mix what you’re explained and the G App API.

What I need is to raise an event when a copy of the spreadsheet is done. This event has to be trackeable from google APi so my local program runs every time a copy is made.

My users are ‘Making a copy’ using a script incrusted in the “main” Spreadsheet. So I can create an event every time they click on that Add-on function.

So, the question is: ¿can I create an event when script is run? and ¿is this Event detected from the API?

[LeventK]

@Alex_Anadon
Can you elaborate what do you mean with “…raise an event…” and what do you mean with “…tracking that event…”?

[anonymous]

Hello Levent,

Well I need to detect from AWS Lambda when a copy of a google spreadsheet.
Once this is detected , create a trigger for the new spreadsheet that runs script OnOpen.

So, raise&track event I mean that my Lambda function being able to detect that a copy is being made.

Our problem is that we have a spreadsheet with an OnOpen trigger and we use it as a template. Our “users”, (people from ou company to create internal reports) create a copy of that template and the problem is that the OnOpen Trigger is not being inherited in this copy and the menú that should be showed in the copy is not there.

[anonymous]

Thank you, this helped a lot.

[anonymous]

Google Apps Script is indeed the most powerful add-on there is for Appsheet. As far as I understand, it also creates a potential risk of data loss.

In some cases, you could use the scripts to manipulate the data in way which isn’t possible to achieve at the application level. The basic working principle behind most of the data manipulation scripts involves reading the data from the sheet (.getDataRange), converting it to a virtual array (.getValues), modifying this array, and then returning it to the sheet (.setValues). If a user were to alter the data on the sheet during script execution via the app, this data would be inevitably lost, as the script would only be able to return the data it has originally converted to an array at the start of an execution.