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

As I have said, your code is a total mess. The code is the problem itself already. This code needs to be re-written from scratch with paying attention to the language and namespaces.

I am asking about this one…

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

if (shtName === “Sheet2…”) {
var activeRng = sht.getActiveRange();
var activeRow = activeRng.getRow();

if (activeRow === 1) {
	GmailApp.sendEmail("o*****@gmail.com", "Test Sucess", "Body");
}

}
}

Executions show this:

Jan 4, 2021, 11:27:13 PM Error Exception: You do not have permission to call SpreadsheetApp.getActive. Required permissions: (https://www.googleapis.com/auth/spreadsheets.currentonly || https://www.googleapis.com/auth/spreadsheets)
at myFirstGAS(Code:2:14)

Error is indicating the problem. What do you want me to say more?

It is my sheet and my account then why am i getting this error and how do i solve it??

You’re just copycatting my code that’s all. You’re not into it provided it will serve your need or not. You have missing scope auths as the error indicates. In general, those scopes are automatically asked for auth when you save your code, but it’s possible that you might have missed it OR as your account might not be a verifed developer, so the auth code might have warned you about being unsafe and you have dismissed it rather than continueing anyway.

I tried executing this code on a lot of sheets and also through different Gsuite accounts… I get Same Error… Don’t know how to fix it now…

If I need some auths, please tell me how to get them

Very helpful thread - is there a way to trigger Apps Script when not using Google Sheet but using Google Cloud SQL (MySQL). We are migrating from AppMaker to Appsheet with 60K + records which are slow on Sheet but fast on SQL. I need to call 3rd party REST APIs on data change events. Since AppSheet Webhooks/HPPT POSTs do not support OAuth2, I can probably use App Script and publish it as Web Project. I can’t figure how to trigger it via data change events when using DB (as opposed to Google Sheet)

The data source makes no difference in how data change events in AppSheet trigger webhooks.

You will need to use ODBC or other connection to your cloud SQL from your script.

If you can provide more details about your use case, we can help further.

1 Like

I didn’t explain correctly. I am talking about triggering Google App Script when data source is not Google Sheet. Google App Script is written within Google Sheet (or Docs) and it triggers on an event such as onChange:

function onChange(e) {

}

If you are not using Google Sheet, how do you trigger Google App Script? One way I can think is to HTTP POST to Google Web App project.

You can have unbound (i.e., not attached to a sheet or docs) google app scripts. These can be published as web apps with a doPost() which you can trigger from webhook workflows in your app.

FYI, @LeventK is offering GAS webinars which you can sign up for to learn more.

1 Like

@Bellave_Jayaram where can one subscribe to the @LeventK GAS Webinars

@Henry_Scott

3 Likes

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

Hi @Ovais.Shah ,

You may want to review this documentation for Google developers regarding Authorizations.

It also points to this support help page, which has a handy link to a Console page for checking your authorizations and consents. You may see the issue there or be able to set up new Auth’s as needed.

Thanks,
Scott

1 Like

Hello @Scott_Haaland
Please tell me how do I trigger an email using appscript when a new data row is added in google sheets via AppSheet???
Please Help

@Ovais.Shah
As we have already spoken multiple times on this issue; onChange(e) is the correct trigger for what you want. However your code is not correct and you have missing auth scopes, that’s why it’s not working. There are 2 options to correct this:

Option#1

  • Delete the whole code, save the Script Editor, close the file, close the spreadsheet.
  • Re-open the spreadsheet, open the Script Editor, paste the code and save the editor.
  • Whenever/Ifever it prompts your for security, on the screen choose to continue unsafe explicitly, auth the content scope

Option#2

  • From View menu, choose Show manifest file

  • To add missing oAuth scopes please follow the directives on this page

And if the goal is only to send an email when a new record is added to AppSheet, you don’t need a script, you need to set an Email Workflow which much more painless and easy.

1 Like

@LeventK
Thank you so much for all the help…
With all your support I figured out a way to make it work…
Thanks, again

2 Likes

Good to hear that @Ovais.Shah
Do you mind sharing your solution with us as well?
Thanks.

@LeventK

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.