Cannot "Call a Script" for a Bot's Task

[StephenHind]

I am working on an AppSheet app that uses a Google Sheet as it's data source.  I want to add a Bot to call an Apps Script but when I get to definign the Task the "Call a Script" option is disabled and the mouse-over/hover tooltip says "Google data source required to use Apps Script", but it is a Google data source as it's a Google Sheet.

Any ideas?

[WillowMobileSys]

I have not yet implemented a call to a script but it is my understanding that, at the moment, you can only call standalone scripts.  You will not be able to call scripts that are included in the same sheet as your datasources.  I have no clue why AppSheet has implemented the feature this way.  It seems counter-intuitive.  I suspect Google is trying to separate Script processing from Sheets - which does make sense in general.

Move the scripts to their own standalone component and then you should be able to access them.

[StephenHind]

@WillowMobileSys I'm not trying to add a script that's bound to a sheet: I am trying to use a standalone script; AppSheet won't let me choose the "Call a Script" (see screenshot in original post) feature as it thinks the table isn't a Google datasource, but it is (it's a Google Sheet - as shown in screenshot in orignial post).

My guess is AppSheet doesn't think the table is a Google data source, but I don't know why. 

[Koichi_Tsuji]

GAS integration is new feature, so possibly a bug. Suggest to report to official channel (not commuity, but through the support)

[StephenHind]

@Koichi_Tsuji yes I did that first but was hoing someone may have encountered it here.

I have made some progress and it may have something to do with ownership of the App: I'm not the owner but a co-author set as an admin.  I have made a copy of the app, using the same Google Sheet as the data source, and it allows me to add the script as a task, but the script never triggers as there are other issues after the copy so I may get those solved and then the script will trigger.

[Koichi_Tsuji]

Yeah, that is possibly the root cause of the issue.

We assume that the co-author of the apps will have the exactly same set of the permission over the shared app, but actually it will not.

For instance, as co-author, we are not able to add new table unless the actual owner of the app does that. 

I think your story could be the same story, due to the constrain when you are sharing the app with others, while you are not having the access to the app as pure owner.  Again, just out of my guess works.

[Steve]

I also have no experience with calling scripts in bots.

Given the error message, I suspect the script is not owned by the account that owns the app, and/or you're not logged into the app editor with the account that owns the app and script.

Attn @nico

[Koichi_Tsuji]

I have never come up with the use cases where the appsheet app creator wish to integrate with new GAS integaration. I dont have any use case before.

What do you actually want to do with GAS integration with AppSheet?

[StephenHind]

@Koichi_Tsuji the main goal is to perform functions that AppSheet cannot do, e.g. create a merged doc in a specified folder structure and sahred appropriately: we need to use Google Drive for this and Apps Script to merge and share appropriately.

[nico]

See my top level reply, standalone script and requiring the app owner to have permission are both requirements but this has to do with not having a Google data source in the current account.

[StephenHind]

@WillowMobileSys & @Koichi_Tsuji thank you for your assistance: once I had my own copy of the app so I was the owner and re-linked the data as necessary I could add the script and get it to trigger.

This suggests that only the owner can Call a Script so the it's either a bug or the hover text on the Call a Script option is misleading and shoul.d be changed.

Once official support gets back to me I can report to support more information and I'll update this thread if support gives a defnitive answer.

[Koichi_Tsuji]

Not perfectly on my expectation, but could be because of the bug (on error messages). thanks for sharing your learnings.

Again, new feature with appsheet carrys tongs of un-idenfied bugs, the example is automation. We have to bear with those......

[WillowMobileSys]

As a side note, there are a number of other features AppSheet has locked down (or at least attempted to lock down) making co-author efforts more difficult.  Most notably, adding sheets as a datatable.  Most App Owners just want the work done so it's a bit embarrassing when you have to ask them to perform some simple action such as adding a sheet as a table or selecting a table source for an API call.

There, of course, needs to be some protection for the App Owner from the co-author performing malicious activity.  Scripts would be an area of concern for sure. 

I'm not certain of best approach for security but from my point of view it seems the choice should be placed in the hands of the App Owner.  Maybe options to allow co-author permissions to perform certain secured activities?

Just wanted to make you were aware there have been other discussions surrounding this area.

[Koichi_Tsuji]

When you copy the app (owned by others, attached with GAS document), I remember the cloned app will not take over the gas document. 

@takuya_miyai  My colleague knows more than me about this, but this is possibly due to the constrains given by Google, about who are able to execute the codes.  
By the way, Appsheet is departing from no-code basement, to more getting into coding area....

I spent hours what the most and possible useful integration with AppSheet an GAS with the recently added integration. Honestly, I never come up with anything. So I m curious what you are trying to do with it.  I gave a glace at a Google;s you tube where they are adding images to Google slide from AppSheet.   My question was when and why they do that?  I neve agree with the neccessity for business reasons.

[nico]

Hi Stephen,

This disablement is meant to show to users that don't have a Google data source in the current account. For example, if you log into an account that is only logged in through a Microsoft account, you can't use Apps Script because it requires a Google data source to use.

If you have an app that was created with a Google account source and have a co-author that doesn't have a Google data source you can get into this state. There are also other rarer ways that the data source can be detected as read-only (e.g. viewing an older version of an app before making current).

In your support ticket, can you ask it to be transferred to `@nwestbury` (me) if that's not the case? I can personally investigate this ticket if the above doesn't apply in your case,

[StephenHind]

Hi @nico 

This disablement is meant to show to users that don't have a Google data source in the current account. For example, if you log into an account that is only logged in through a Microsoft account, you can't use Apps Script because it requires a Google data source to use.

It's 100% Google: created with a Google Account (not mine), uses a Google Sheet on a Shared Drive that I'm a manager of and I'm an admin on the app with my Google Account.

If you have an app that was created with a Google account source and have a co-author that doesn't have a Google data source you can get into this state

Yeah that's not my scenario: all 100% Google just I'm not the onwer of the app.  As mentnioed in an eariler comment I took a copy of the app (so I'm the owner) and linked to the original data source (the Google Sheet in the Shared Drive) and that works.

Once I get a reply/aknowledgement to my support request (not had anything yet) I'll request to pass it over to you.  I submitted the ticket from a different account than this one in the community.