Button on an AppSheet app to trigger a Script for underlying Google Sheet

AppSheet AppSheet Q&A
1

Hi Friends,

Is there a simple way to have an Action button on a Detail View of an AppSheet app that can trigger a script which works perfectly on the underlying Google Sheet?

I’ve created the script, then created a button which is placed on one of the sheets. I’ve then assigned the Script to the button, and clicking on the button from the sheet works as expected. However, I need either the Script or the Button be triggered from an Action Button from the App. How do I do this, simply?

Thanks for your help!

Parag

1 Like
2

There is a post around here somewhere, but the script trigger must be onchange if I remember correctly. Maybe you can adjust your script.

[How to Integrate Google Apps Script & Trigger with an AppSheet App](https://community.appsheet.com/t/how-to-integrate-google-apps-script-trigger-with-an-appsheet-app/11805) Tips & Tricks ?

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…

4 Likes
3

@Parag
You need to publish that script as a WebApp and then use its URL as a webhook endpoint in AppSheet’s Webhook Workflow.

5 Likes
4

Hi Levent,

Thanks for your prompt response!
While it did answer my question to a point, it has raised some more!
Before I get into the details, please know that I’m VERY new to all this-about a month: Google Sheets, AppSheet or any kind of coding!
So, I followed your suggestion and published my Script as WebApp and used its URL as a Webhook in the Workflow of the ApSheet. So far, so good
But it doesn’t work!
Upon testing, I got the following error message:

Failed: Action not performed because 1 errors are present. Error: Failed to parse JSON due to Unexpected character encountered while parsing value: h. Path ‘’, line 0, position 0…

My Script is very simple:
function addRecord() {
var ss=SpreadsheetApp.getActive();
var sh1=ss.getSheetByName(‘Random’);
var sh2=ss.getSheetByName(‘Add’);
var Range=sh1.getRange(“D2:D2”).getValues();
sh2.appendRow(Range[0]);

}

It simply adds a new record, in a new Table’s appended row, from a list of random names from another Table. I’ve assigned this Script to a button on the Google Sheet, and it works perfectly, as expected, if I’m clicking the button from the Sheet. It does not have, nor does it need any other triggers such as onOpen, onChange, etc. It is a manual “click” on the button, and needs to remain a manual click.

Now on the AppSheet app that I’ve created, the app pulls the name (and the associated information such as images, etc.) from the newly appended row in Google Sheet and presents it like a slideshow to the user of the app, in a completely random manner. However, if the user of the app is able to click/touch a button on the APP, which in turn triggers the Script on the Google Sheet, then they would not need to have access to the Sheet! I need that to keep the sheet safe! As a workaround, I’ve placed an Action button on the App, which forwards one “slide” at a time upon clicking the App button in a Detail View. (But for this I need to manually “advance” all the slides on the Sheet (which the user is not aware of), but then it is not truly dynamic or random, for that matter! And I always need to stay involved!!

After I got the above error, I researched the publishing of the WebApp. There seem to be two requirements for that:

"A script can be published as a web app if it meets these requirements:

As you can see, I don’t have either doGet(e) or doPost(e) in my Script, and I don’t even know what the “objects” are in the second requirement!

So these questions:

  1. How was I able to actually publish my Script on the web (and get it’s URL) when I have neither doGet nor doPost?
  2. What is causing the error message above? How do I fix that?
  3. What you are suggesting is Behavior > Workflow > Webhook to connect the App and the Script on the Sheet. However, the App Action buttons are created from Behavior > Actions > Action Icon > Prominence. So how do I assign a Button to a Webhook?
  4. Since the whole idea of the AppSheet is a No-Code environment, and since AppSheet seems to work like a “wrapper” around a very specific “container”, in my case a Google Sheet, why can’t a specific “tunnel” be possible to trigger the specific Script on that specific Sheet from that specific App (without having to “go outside” and do the webapp publishing, linking the URL, establishing the Workflow webhook, etc.? The specific one-to-one relationship between the Sheet and the App already exists, so why complicate the matter by having to “go outside”? (The “Tunnel” would be something like, in a Sheet when you design and save a Button, place it on the Sheet, and then simply “Assign a Script” to that Button! It just works! That simple!!) Something to think about!

I’ve also CCd Praveen so that you folks can think of how to make this whole experience truly no-code, to encourage people like me!

Many thanks again!

Warm regards,

Parag Raval

1 Like
5

@Parag
Your code shall be like this. After saving the changes, re-publish the WebApp, from version dropdown choose new and press publish.

function doPost(e) {
  try {
	var ss=SpreadsheetApp.openById("Your_Sheet_ID_Here");
	var sh1=ss.getSheetByName(‘Random’);
	var sh2=ss.getSheetByName(‘Add’);
	var Range=sh1.getRange(“D2:D2”).getValues();
	sh2.appendRow(Range[0]);

    return ContentService    // return json success results
    .createTextOutput(
      JSON.stringify({"result":"success",
                      "data": JSON.stringify(data) }))
    .setMimeType(ContentService.MimeType.JSON);
  } catch (e) {
    MailApp.sendEmail("your_email_address_here", "Webhook Error Occured!", 
      "\r\nMessage: " + e.message
      + "\r\nFile: " + e.fileName
      + "\r\nLine: " + e.lineNumber);
    e = (typeof e === 'string') ? new Error(e) : e;
    Logger.severe('%s: %s (line %s, file "%s"). Stack: "%s" . While processing %s.',e.name||'', 
               e.message||'', e.lineNumber||'', e.fileName||'', e.stack||'', processingMessage||'');
    throw e;
  }
}
2 Likes
6

@Parag
Provided you only want to copy values from one sheet to another and create new record please check this where you don’t need a script at all:
Option#1: Using AppSheet Behavior Action

Option#2: Using Preset AppSheet API via Webhook Workflow

1 Like
7

Hi Levent,

Thanks for all the info and the code for the script.
I’ll check these out and try to implement them as well.
I’ll keep you posted.

Many thanks, again!

Parag

8

Hi Levent,

Pursuant to your suggestions, I was successfully able to create buttons on my app that trigger the app scripts on the underlying Google Sheet.
My follow up questions are as follows:

  1. Can ONE button trigger two different scripts? If so, how do I do that?
  2. If the above is not possible, how do I automate running of the script? I have tried the onEdit and onChange triggers, but they work ONLY if I’m interacting directly with the Google Sheet, and not if I’m in the App. onOpen and onFormSubmit are not relevant.
  3. Is there a way to have an Action or Workflow that can automatically trigger on some other event happening, without having to tap a button? If yes, how?

Many thanks for your great help!

Warm regards,

Parag

9

Parag:

Can ONE button trigger two different scripts? If so, how do I do that?

Can you please expand the idea?

Parag:

If the above is not possible, how do I automate running of the script? I have tried the onEdit and onChange triggers, but they work ONLY if I’m interacting directly with the Google Sheet, and not if I’m in the App. onOpen and onFormSubmit are not relevant.

onEdit(e) installable trigger only works if you are interacting with the spreadsheet. However, onChange(e) installable trigger works with AppSheet.

Parag:

Is there a way to have an Action or Workflow that can automatically trigger on some other event happening, without having to tap a button? If yes, how?

Regarding what you want to accomplish, you can use Scheduled Reports for that purpose. If you can expand the idea, I may advise in detail.