This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
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:
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:
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.
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 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.
I believe your requirement is to have an Event triggered when your data in CloudSQL is changed. Is that correct?
I donโt think Apps Script can natively register as an event listener for non-Google WorkSpace apps, as you are correctly stating.
You will have to use a Polling strategy to simulate an event based โauto-notifyโ pattern, unless there is some way to configure a trigger on the DB Table itself (in CloudSQL) to call an APIโฆIโm not a DB expert, so not sure if thatโs possible.
A Polling strategy means that you have a script that is scheduled to run every x amount of time (once per hour, once per 5 minutes, etcโฆ). It then checks the DB for any changes using an SQL query. If there is a change, it takes the appropriate actions for that change.
We have some Eventing features on our roadmap as part of our Automation+Connectors feature sets. The CloudSQL eventing is planned for 2021 in the second half.
My datasource in my Appsheet application is GCloud SQL but I receive data via a Google form that I would like to have written to the SQL database.
I know I can use GAS JDBC connection to establish the connection from my GForm directly to my SQL database but I am wondering if rather than doing that I would be able to use Appsheet API in my GAS script to write data to the Appsheet application rather than the database directly?
I built my app (appsheet) with google forms. And now, I need trigger a google script when the data is recorded in the sheet and this script be triggered by INSERT_ROW (change type).
I verified here that is not possible with onEdit or onFormSubmit. I am trying to use onchange, but I could not get the INSERT_ROW on changeType.
function setTriggerOnChangeTest(){
var sheet = SpreadsheetApp.getActive();
ScriptApp.newTrigger('test2')
.forSpreadsheet(sheet)
.onChange()
.create();
}
function test2(e){
console.log(e.changeType);
}
I need help with connecting google spreadsheet script to appsheet, tried many things but since i choose appsheet because i cant actually code (and yes i steal this code below from stackoverflow :D)
function onChange(event) {
var ss = SpreadsheetApp.openById(โ10_d4Drmj27cIOdTrnWfWXiZRd9bJRdmClnj-AbXHtQcโ);
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("KOLOM ONLINE");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
}
}
I just need to move the entire row if the column 14 (column N) from โKOLOM AVAILABLE AXISโ value is โONLINEโ, and move them into the new sheet called โKOLOM ONLINEโ
Thank you for your time on the thread and I am recently trying to integrate GAS with AppSheetApp. Since I am totally new on GAS but with a bit VBA coding experience, I am facing different problem on my usage.
Here is my case, I am using an AppSheet App on recoding appointment, I would like to send an email to remind in advance for example; 7days, 1 days earlier and on the same day remind.
I have tried to copy and adjust a bit your code on my case, but I am not able to select event source from spreadsheet. Is there anything I missed? below is captured image FYR.
The first option is time driven and the second option is from calendar.
Thank you for your information. I think my GAS is standalone script. However, when I tried to create a new script from the AppSheet App spreadsheet, there is no Script Editor option but for normal spreadsheet (without AppSheet App), the option is available. Attached image is FYR.
I for the life of me cannot get Appsheet to trigger this. I have an action set up in AppSheet to push the required words to the required column in Sheets to get the function to launch but it will not run the function. I can see in the execution logs that the change is recognized and it is finished with completed it just wonโt run the function.
Everything works great when manually editing in the sheet just not from Appsheet. The trigger is set to Onchange below is the code: Is there something Iโm missing with triggering this from Appsheet. Everything works as expected within the sheet itself.
function onChange(e) {
var sSht = e.source;
var sht = sSht.getActiveSheet();
var shtName = sht.getName();
if (shtName === "Sync") {
var activeRng = sht.getActiveRange();
var rngW = activeRng.getWidth();
var activeCol = activeRng.getColumn();
var activeRow = activeRng.getRow();
var myVal = sht.getRange(activeRow, 2).getValue();
if (rngW == 1) { //checking if the changed range is a single cell
if (activeCol == 2 && myVal === "Complete CycleCount")//Check Column 2/B for Word
{
loadCycleCount() //Custom Code
}
}
}
}
Try to put some logging commands into the code to see what is happening. Is the sheet name actually being matched correctly for the if statement? How about the rngW? activeCol? myVal? etcโฆ
Thanks so much for your reply. After running the log this morning I realised my error which was the line
if (rngW == 1) { //checking if the changed range is a single cell
When manually editing the sheet Iโm obviously changing 1 cell but with my Appsheet Action, I was also pushing an (UpdatedAt) date to the Sync sheet as well. Now 2 cells are being changed. The solution was to remove this line or update it to how many cells are being changed by Appsheet.
Iโm new to GAS and trying to learn and staying up until 3 am trying to work it out probably wasnโt the greatest idea. But all is working perfectly now and a big lesson has been learnt haha โAlways compare and run the logsโ
Thanks, @LeventK for this script and explanation! and thank you @Marc_Dillon for your reply
Dear Levent,
I try to use installable change trigger with OnEdit(e) function but itโs ok only if I change directly on my sheet but if I change the cell by my app sheet it doesnโt run. Can you tell me please what i need to do? Thank you.
Dear Marc,
It doesnโt go.
The app sheet changes the cell value in the sheet but the on Change function doesnโt run.
Now it doesnโt run even if I change the cell directly on the sheet.
Thanks for your help.
Denis
Una recente esecuzione del tuo script Share your care non รจ riuscita. Il riepilogo degli errori รจ riportato qui di seguito. Per configurare i trigger per lo script o per modificare le impostazioni relative alle notifiche di errore, fai clic qui.
If your onChange function is returning an error, that means it in fact is running. You just have an error in your code. That particular error is telling you that whatever youโre trying to run getA1Notation() on, is undefined.
Ok. I understand.
The problem is that this error is given only with the trigger installed, not if I change the cell manually (in this case the function run correctly)
Sorry but why do I need the trigger? Is not enough the function onchange(e) for making an unstable trigger?
Many thanks for your time.
Off the top of my head, Iโm not actually sure that you do need it.
I suspect your issue is with determining the correct range, which is why it is erroring out because your range is undefined. Manual edits in the sheet are mostly just single cell ranges, but an edit via Appsheet is going to be an entire row.
I know Iโve posted something before about how to get the range from an Appsheet edit onChange. You should search through other threads about GAS on here.
This seems to be conflicting with the fact that everybody here is talking about adding an installable trigger. I cannot add a trigger that's from spreadsheet, if the project is a standalone one. I would appreciate any clarification on this matter. TIA.
LinkedIn
Twitter
