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:
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:
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:
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:
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.
@Bellave_Jayaram where can one subscribe to the @LeventK GAS Webinars
Hi @Ali_Sabir,
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.
Hope that helps!
R,
Scott
@LeventK
Thank you so much for all the help…
With all your support I figured out a way to make it work…
Thanks, again
Good to hear that @Ovais.Shah
Do you mind sharing your solution with us as well?
Thanks.
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?
Yes, this should be doable using UrlfetchApp.fetch().
Try it and let us know if you get stuck.
Hello, I need help.
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);
}
The output is always ‘EDIT’ or ‘OTHER’.
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 s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == “KOLOM AVAILABLE AXIS” && r.getColumn() == 14 && r.getValue() == “ONLINE”) {
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”
I need help
Hi LeventK
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.
Thank you in advance.
Stanley Leung
I believe that means your script is not attached to a GSheet. Did you create the script directly from the GSheet, or from the GAS page?
I’m not seeing any way to “bind” a standalone script to a sheet, so maybe just create a new script from the sheet, then copy paste.
Hi Marc_Dillon,
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.
Appsheet ss without Script Editor option
Thank you
Stanley Leung
Thank you
Stanley Leung
I would say that is beyond the scope of these forums.
I need that when a person sends a Google Form response, a PUSH notification is generated in AppSheet
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…
Hey Marc.
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
Yes, always log. Nice catch.
huh…well I’ve certainly never done anything like that…
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.
onChange(e) , not onEdit(e).
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
Did you install the trigger?
Yes.
I’ve installed a trigger with the event “At change” but the function Onchange doesn’t run
Is it correct?
Thanks
This is the trigger error
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.
Script utilizzato dal documento Share your Care.
Avvia Funzione Messaggio di errore Attivazione Fine 23/11/21 16.18.05 CET onChange TypeError: Cannot read property ‘getA1Notation’ of undefined change 23/11/21 16.18.05 CET
Cordiali saluti,
Google Apps Script
Hai bisogno di aiuto? Consulta la documentazione di Google Apps Script. Non rispondere a questo messaggio. (c) 2021 Google
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.
is that work now?
when i change data in appsheet trigger is not work
Hi there,
I am really confused by the fact that the official documentation says the following :
The Apps Script project must be a Standalone Project rather than a Container-bound Project.
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.