Re: How to Integrate Google Apps Script & Trigger ... - Page 2

LeventK · 06-24-2019 06:37 AM

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:

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.
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

The schedule creation is also performed by onChange(e) trigger within the sheet container when you ADD a contract record. Am I correct?
If so, checking the value of the 1st column if it has a value or not, identifies the addition/editing or deletion of that particular row.

function myGAS(e) {
	var sSht = e.source;
	var sht = sSht.getActiveSheet();
	var shtName = sht.getName();
	
	if (shtName === "MyTestSheet") {
		var activeRng = sht.getActiveRange();
		var activeRow = activeRng.getRow();
		
		//Considering your Contract Table's Key column is Column A, ColNr is set to 1. If different arrange accordingly.
		if (sht.getRange(activeRow, 1).getValue !== "" || sht.getRange(activeRow, 1).getValue !== null || sht.getRange(activeRow, 1).getValue.toString().length > 0 {
			Your Calendar GAS code goes here;
		}
	}
}

ANJ_dev

You right with your assertion.
So you suggest me to check for the first column to see if it has or not a value, to infer if it as a creation vs a deletion?
I did not though about it… I will try and let you know…

Thank you

i still wonder why GAS’s event source does not clearly distinguish between INSERT_ROW and DELETE_ROW

LeventK

Provided you access the sheet via Sheet API then you can control that. With onChange(e), every interaction with the gSheet itself (on a workbook basis, not single sheet level) is considered as an EDIT EVENT Object.

ANJ_dev

The worse is, sometimes I remove a row ChangeType is set correctly to ROW_REMOVE and sometime to EDIT, same when I insert a row. So the ChangeType seems not to be reliable.

To handle that, I added a modified a lil bit yor code like this

if (e.changeType === "INSERT_ROW" || 
    (e.changeType === "EDIT" && 
      (sht.getRange(activeRow, 1).getValue !== "" || sht.getRange(activeRow, 1).getValue !== null || sht.getRange(activeRow, 1).getValue.toString().length > 0)
     )
    ) { 
      if(shtName === CONTRACT_SHEET){
          //Contract creation handler
      } if(e.changeType === "REMOVE_ROW" ||
   (e.changeType === "EDIT" &&
    (sht.getRange(activeRow, 1).getValue == "" || sht.getRange(activeRow, 1).getValue == null || sht.getRange(activeRow, 1).getValue.toString().length == 0)
   )
   ) {
     if(shtName === CONTRACT_SHEET){  
//Contract deletion handler
 }

this distinguish well now between row insertion and row deletion. The problem is now that by deletion, I dont have any context. The Active range’s values is empty or undefined.
Is there a way to have the values of the deleted row?

LeventK

Provided you are not explicitly using Sheet API, with the onChange(e), you can’t control the context unfortunately. But your code is neat, congrats for that.

ANJ_dev

I will find another way to reach my goal, with as low coding as possible.
Thanks for for your help

LeventK

You’re welcome, my pleasure.

ANJ_dev

Good day all,
I solved the problem that I myself posted here and would like to share, if you ever meet the same issues as me.
In fact, I was faced more with a design problem than a technical problem. Let me explain myself : I wanted to deal in real time, on the server side, with events that occur on the client side. I don’t think this is a good idea in the context where Appsheet’s client-server synchronization is not instantaneous and especially since GAS does not provide reliables informations about the events that occur on the spreadsheet.
So I changed my approach to use Time-based triggers instead of Spreadsheet based triggers. In short, I let the events accumulate in the spreadsheet and program a trigger to process them at a reasonable frequency. A workload in fact. Old like computers, but it works for me.

I think this approach is easy to setup if you don’t have to deal with the concurrency. My application is single user, so the approach was easier to set up. In a multi-user context, I am still convinced that this approach remains valid, but it requires more analysis for a good implementation especially when the users can overwrite shared ressources.

Thank you LeventK for your support and also for your article which opened the way for me on all the possibilities offered by the association of Appsheet and GAS

MultiTech

Funny you would say something like this, I was just discussing with someone else about how building AppSheet apps feels like building web pages in the 1990s.

Ovais_Shah

Hey there,
I am using an onChange instable Trigger with the below code. It isn’t even triggering… I even tried with only one-sheet. Please help

function myFirstGAS(e) {

var sSht = e.source;

var sht = sSht.getActiveSheet();

var shtName = sht.getName();

if (shtName === “Form responses 1”) {

GmailApp.sendEmail("ovsjid@gmail.com","sub","bosy")

}

LeventK

@Ovais.Shah
Your script code does nothing unfortunately.

What’s this code? What do you want do accomplish? Your code sends an email to ovsjid@gmail.com with the subject line of sub and a email body of text bosy, that’s all.

Ovais_Shah

Hey there, as the code didn’t work with e.source, hence I was trying to check if this will work at least.

Actually, I am using generating a document and emailing that to the person whose Email-id is mentioned there… I am using Appsheet to update a new row to my google sheet… Now every time a row is added, a sheet invoice template gets updated and is converted into pdf and emailed to the client whose email id is mentioned there…I want the email to trigger whenever the row updates through AppSheet but as onFormSubmit didn’t work so I used onChange… onChnage is working perfectly fine but sends multiple emails instead of just one… Sometimes even sends more than 20… Please help me so that only 1 email is sent whenever a new row is added

LeventK

I can’t advise anything without knowing your app build, how the gSheet is structures, your complete script code etc. There might be several reasons for that behaviour and it’s not possible to get to the point one by one.

Ovais_Shah

Please tell me how do i contact you??

Ovais_Shah

Please tell me, why do I get this error*** when I run the code#####…While u have mentioned that we will get the result as an object i.e. the whole sheet.

[21-01-04 23:10:46:361 IST] TypeError: Cannot read property ‘source’ of undefined
at myFirstGAS(Code:2:15)

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

This is what my execution says…

Jan 4, 2021, 11:10:17 PM Error TypeError: Cannot read property ‘source’ of undefined
at myFirstGAS(Code:2:15)

LeventK

Trying to log e.source (assigned to sSht variable) gives you nothing, because it’s object space.

Ovais_Shah

Are u saying, The trigger attached to this code will get executed if there is a change in row 5 of MyTestSheet???

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

As the code itself clearly implies, yes.

Ovais_Shah

I am trying to send an email, if row 1 in sheet 2 changes with the following code… Y is is not sending the email???
Please help…

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)

LeventK

I believe the error code is quite clear. Are you the sheet and the account owner? Or the sheet is owned with some other and just shared with you?

Ovais_Shah

I am the owner, creator, everything of the sheet and the account

Ovais_Shah

Thank you so much for your advice…Definitely I will take care of that… Please tell me what is the problem here??

LeventK

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.

Ovais_Shah

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)

LeventK

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

Ovais_Shah

LeventK:

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;
		}
	}
}

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

LeventK

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.

Ovais_Shah

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

shaaland

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

Ovais_Shah

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

LeventK

@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.

Ovais_Shah

Here is my code…

function emailSpreadsheetAsPDF() {
DocumentApp.getActiveDocument();
DriveApp.getFiles();
const ss = SpreadsheetApp.openByUrl(“https://docs.google.com/spreadsheets/d/1NrQsfXr7nzbNRm-FKqU79CBlIeGIKrZKkaUKmVftcBQ/edit”);
const value = ss.getSheetByName(“Invoice Template”).getRange(“B9”).getValue();
const email = value.toString();
const cc_email = ‘###############@gmail.com’;
const subject = ‘Your Invoice From @@@@@@@@@@@@@’;
const body = “Hello
Attached herewith is the Invoice. Thank you for doing business with us.

.” ;
const url = 'https://docs.google.com/spreadsheets/d/1NrQsfXr7nzbNRm-FKqU79CBlIeGIKrZKkaUKmVftcBQ/export?’;
const exportOptions =
‘exportFormat=pdf&format=pdf’ +
‘&size=letter’ +
‘&scale=2’ +
‘&top_margin=0.25’ +
‘&bottom_margin=0.10’ +
‘&left_margin=0.25’ +
‘&right_margin=0.10’ +
‘&portrait=true’ +
‘&fitw=false’ +
‘&sheetnames=false&printtitle=false’ +
‘&pagenumbers=false&gridlines=false’ +
‘&fzr=false’ +
‘&gid=430555412’;
var params = {method:“GET”,headers:{“authorization”:“Bearer “+ ScriptApp.getOAuthToken()}};
var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
cc: cc_email,
attachments: [{
fileName: ss.getSheetByName(“Invoice Template”).getRange(“e7”).getValue().toString() + " for " + ss.getSheetByName(“Invoice Template”).getRange(“a7”).getValue().toString() +”.pdf”,
content: response.getBytes(),
mimeType: “application/pdf”
}]
});
const nameFile = ss.getSheetByName(“Invoice Template”).getRange(“e7”).getValue().toString() + " for " + ss.getSheetByName(“Invoice Template”).getRange(“a7”).getValue().toString() +".pdf"
DriveApp.getFolderById(“1MupPI9xtBJKvuVDQ2t380VpaQ4pWyTrq”).createFile(response.setName(nameFile));
}

LeventK

@Ovais.Shah
Sorry but your code is a complete mess.

Why do you need this? You even don’t process them inside the code

const ss = SpreadsheetApp.openById(“1NrQsfXr7nzbNRm-FKqU79CBlIeGIKrZKkaUKmVftcBQ”)

no need for const value declaration
const email = ss.getSheetByName(“Invoice Template”).getRange(“B9”).getValue().toString();

Where have you found this PDF code? it’s a complete mess…my Gosh

Ovais_Shah

I know… when i started using it, i was a rookie… I will modify the code but need to now how will it trigger onChange when there a new row is added via Appsheet

LeventK

By-desing and as the trigger implies as well, onChange is triggered when a new row is added by AppSheet. Provided it’s not working as expected, the problem lies on the code itself, neither the trigger nor the AppSheet. As far as I can see, your script code is a complete mess and your variable declarations are either redundant or not correct at all. I may advise reading some Google Developers page.

Ovais_Shah

I need the email to execute whenever a new row is added in spreadsheet via appsheet…onChnage trigger works but sends more than 1 email… I need it to send just one… Please help

Ali_Sabir

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)

Bellave_Jayaram

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.

Ali_Sabir

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.

Bellave_Jayaram

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.

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