Not clear what is the best way to publish my Google Apps Script

[Tuan Do]

So my Google Apps Script is slightly different from other Sheets add-on.

Right now I have a spreadsheet and an Apps Script made specifically for this spreadsheet (the Apps Script populates data based on the structure of this spreadsheet). There are 2 functions in that Apps Script. One will run when users click a button and one will run when a specific range of cells are changed.

My plan is to have my customers make a copy of that spreadsheet and be able to use my Apps Script by either clicking the button or changing the values of some cells.

Since my Apps Script requires some sensitive scopes, I have submitted an OAuth App verification request and my request has been granted. However I am not sure how to make my Apps Script available for other users. I have tried 2 things but none of them works:

1/ I make a copy of the above spreadsheet using another Google account. However, when I click the button, it still says the app is unverified and shows a warning. I think the reason is that when I click that button in the copied spreadsheet, it does not run my verified original Apps Script but a copied version of the original script which is linked to my original spreadsheet.

2/ I tried to submit my Apps Script as a Sheets add-on in Google Workplace Marketplace but since my add-on can only work on my particular spreadsheet, they say that: "Add-on provides all the tools needed to complete its workflow and proposed functionality. For example: if the Add-on needs an specific format or design of the sheet then the Add-on should provide a function to create it." 

Really appreciate any advice on how to make my Apps Script available to everyone. Thanks

[Alan Wells]

If the user copies a Sheet template of yours, then don't have an Apps Script project bound to the template, or else when they make a copy it will also copy the code file, which is not approved.  Or you'd need to write code that builds the Sheet format.  If your Sheet formatting is difficult to reproduce from code, then obviously it would be a lot of work writing the code, and you might not want to do that.  When a user installs your add-on it is available to all Sheet files, so the add-on will be available to the template file that they copy (without the code)  You'll need to explain that there is "set up" to be done, and that the add-on won't run from the Sheet that they initially installed your add-on from.  There is an "install tip" plus you can have an On Open function test whether that Sheet is a valid template or not.  So, either remove the project file from the template, or don't use a template at all, but build the Sheet from code.  

[Do Quoc Tuan]

Hi Alan,

Thank you for your answer. The template is fairly complicated so I dont think I want to code to reproduce it. I don't quite understand your last part which is: either remove the project file from the template. Could you explain a little bit more on this point? Thanks a lot

Tuan

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/dXytr09z3uk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/22e05b89-e495-4c2a-81e4-83ab2094a527n%40googlegroups.com.

[Alan Wells]

The 1st situation that you described is making a copy of the Sheet and when you click the button it shows the unverified warning.

I'm assuming that the code for your add-on in in an Apps Script project that is bound to a Sheet rather than "stand-alone."

You need the user to copy the template Sheet file, but whatever Sheet file they make a copy of shouldn't have a script file bound to it.

If the approved project file is bound to the template Sheet, then you'll need to make a copy of that Sheet, then remove the bound file,

and have the user's use that as the Sheet file to copy.

You don't want them copying the Sheet file that you are using as the basis for the add-on.

[Do Quoc Tuan]

Hi Alan,

I have created a copy of my original spreadsheet and delete the linked App Script in that copied spreadsheet.

A rather basic question: Now how can I use my verified Apps Script when I click a button in that copied spreadsheet? FYI my verified Apps Script is bounded to the original spreadsheet and I believe we cannot unbound a bounded script as in https://stackoverflow.com/questions/48949542/how-to-change-a-bound-script-to-a-standalone-script.

Thanks for your help.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5266c18d-ed2f-4165-a1b7-29c8a8fb2320n%40googlegroups.com.

[Alan Wells]

You are correct that you can't make the bound script a stand alone script file.

And since that's the one that you submitted for verification, that's what you'll need to use for publishing unless you wanted to go through the verification process again.

When someone installs your add-on, you'll need to somehow prompt the user with instructions about what to do.

You could have the On Open function set some values in the Sheet that tell the user what to do.

The install tip should tell the user what to do to get started.

For example,

Click on Add-ons menu.

Choose "The name of your add-on"

Click on "Set-up Instructions" menu item.

then the Set-up instructions in a dialog box or sidebar tells the user what to do.

Have a link to the Sheet file that needs to be copied.

Once they make a copy of the template file, that file will have the button in it.

You wouldn't want to put a button in just any Sheet file that they happen to have open.

So, that's why I'd use the add-on menu as the starting point for them to get started.

Your add-on doesn't need to build an add-on menu, and if you want to avoid creating an add-on menu

then you could probably not create it if your code detected that the active Sheet already is the correct template file.

There is probably something in your Sheet that needs to always be there in order to work correctly,

so you could have the code check if it exists before building the add-on menu.

But when the user first installs the add-on, or if the Sheet is not the correct template then I'd create an add-on menu for them to use to do the set up.

[Tuan Do]

Hi Alan,

I spent the last 2 days set up instructions for user like you mentioned. Luckily I found out about the sidebar which makes things easier. However I am still having troubles with triggers. I am testing my add-on by: in the Legacy Script Editor I click Run > Test as add-on > Follow the instructions...

As I mentioned in previous posts, there are 2 functions in my Apps Script. One will run when users click a button (a drawing) and one will run when a specific range of cells are changed (installable trigger for edit).

1/ It seems like using the add-on I cannot initially assign a function to that button anymore. So I have to settle for an alternative which is to have a button in the side bar and when users click that button the first function will be call. Do you know of any way to assign a function to a drawing (button)?

2/ I follow the answer here to add an installable trigger but it did not work. First time I click on the dropdown menu there was no error but when I tried to edit cells, the execution log does not show anything (the 2nd functtion corresponding to the installable trigger was not called). However if I do Run > Test as add-on > and start the test again, when I click the dropdown menu I see the error: `Exception: This add-on has created too many edit triggers in this document for this Google user account.`

To be honest I have no idea how Google can make the process this complicated. It should be much easier and cleaner than this. Really appreciate if you could help me with this issue. Thanks a lot.

[Alan Wells]

You don't need to use the Run -> "Test as add-on" feature if your project is bound to the Sheet.

It's pointless to use the "Test as add-on" feature when the script is bound to the Sheet.

And "Test as add-on" feature has restrictions with triggers. 

There is a quota limit for creating triggers.  You hit the limit, which will happen very quickly when doing testing.

Test you code that will be attached to the trigger by running it some other way if you need to,

then when the code is working, install the trigger.

If you are testing the code that installs the trigger, then after 20 times, you'll hit the quota limit.

You can insert an image with code:

https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#insertimageblobsource,-column,-row

Then you can assign a script with code on the OverGridImage class.

https://developers.google.com/apps-script/reference/spreadsheet/over-grid-image?hl=en#assignScript(String)

[Do Quoc Tuan]

Hi Alan,

Thank you for your response. Actually I am testing my App Script on a different spreadsheet (the one that is not bounded by my app). All errors I mentioned earlier happened in a different spreadsheet.

The reason I did not test on the bounded spreadsheet is: 1/ I already authorized the installable trigger a long time ago and 2/ I was thinking the purpose of an add-on is to be able to use in any spreadsheet.

To be clear I could run onOpen (adding the dropdown menu) and show the sidebar (when clicking the dropdown menu option) in a different spreadsheet. I only saw errors when I try to install the installable triggers.

Am I missing something? Thanks.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/798f9068-f5e2-4539-905b-c498116297can%40googlegroups.com.

[Alan Wells]

If you hit the limit for the number of triggers that you can install in one day, then the code to create a trigger wouldn't work anymore.

If a trigger already exists, then you wouldn't want to create another one.

If the problem is a quota limit, then you'd need to wait until the quota is reset sometime the next day.

But, I don't know what the problem is.

I'm just guessing with general knowledge.

[Boris Baublys]

Hi. I also ran into problems with triggers when testing as an add-on. 

Unfortunately, "Installable triggers aren't supported when testing. Functionality that depends on installable triggers are not testable", as written here.

When the user runs the Set triggers command from the menu, the triggers appear in the Current project's triggers window. 

However, when you execute an action that should trigger the trigger (in this example, OnEdit ()), the trigger code is not executed. Also in the "Triggers" window you can see that the trigger is disabled:

"Disable reason: This trigger has been disabled for an unknown reason."

In this regard, it is impossible to show the functionality of the onEdit trigger in the Test as add-on mode.

A similar situation with a clock trigger - this trigger appears in the Triggers window, but does not work.

After this point in time, you can also see in the Triggers window:

"Disable reason: This trigger has been disabled for an unknown reason."

Therefore I simulated the clock trigger by loading it manually by the Download menu.

You can do the same too. Make sure the trigger set function works in the bound script and then, if there are no errors in the code, it will work correctly in the add-on.

воскресенье, 14 марта 2021 г. в 03:25:34 UTC+3, aj.a...@gmail.com:

[Do Quoc Tuan]

Thanks Boris. My trigger works in the bound script so I will believe it will work in the add-on as well.

@Alan: Sorry to bother you again. I try using insertImage and assignScript but I encountered 2 problems.

1/ The image does not show up unless I refresh the page of the spreadsheet.

2/ When I click on the image it says: "There was a problem. Script function function tempEdit(){

Logger.log('abc')
} could not be found"

I attach a screenshot of my script to this email. Do you know the problems for 1 and 2? Thanks a lot for your help

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/fbb0ed1e-c578-43de-a9e0-5454db18cbe5n%40googlegroups.com.

[Alan Wells]

Try using:

SpreadsheetApp.flush();

At the end of the function.

The function name must be a string:

img.assignScript( "tempEdit" );

[Do Quoc Tuan]

Hi Alan,

Thank you for your response. I added `SpreadsheetApp.flush(); sheet.activate();` and the image shows immediately.

I also changed the function name to a string and the trigger is called successfully in the bounded spreadsheet. However when I try to test as add-on in another unbounded spreadsheet, when I click the image I receive this error: There was a problem. Script function tempEdit could not be found.

To be clear my function `insertImageOnSpreadsheet` works in unbounded spreadsheets (image is shown), the error message only appears when I click the image in an unbounded spreadsheet.

I attach a screenshot of my script to this email. Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/dXytr09z3uk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/47c6130c-5a65-46a2-a5d3-76b16abd84f2n%40googlegroups.com.

[Alan Wells]

It's probably an issue with the "Test as add-on" feature.

As long as it works in a bound script, then it will work in the add-on.

That's why I just use bound scripts to develop editor add-ons.

I never use the "Test as add-on" feature.

As far as I'm concerned it's an absolutely useless feature.

I do publish my add-ons from a stand alone project file, but to do that I use the Apps Script API

to copy the bound script over to a stand alone script.

[Do Quoc Tuan]

Thanks Alan for your response. About the standalone script, I read from here it says that standalone scripts cannot do `activate` or `getActive`. Does it limit the functionalities of your scripts? And I believe we can still publish container-bound scripts to be add-ons, which can be used in any spreadsheet right? Thanks 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/997af4ff-9897-4ab9-a370-e1fc01441ea0n%40googlegroups.com.

[Do Quoc Tuan]

Forgot to include the link: https://stackoverflow.com/questions/33553380/standalone-script-for-google-sheets

[Alan Wells]

A bound script can be published as an add-on.

You don't need to use a stand-alone script for an add-on.

A stand-alone project that is installed as an add-on behaves the same as a bound script.

The only time that a  stand-alone project will operate as a bound script is when it's installed as an add-on.

[Lissa Klein]

Is there a good write up on why, when, how to create add-ons. How are they shared when not submitted to the Google store? 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f33e4dfc-757e-4d9c-a765-86c0e4ffef7en%40googlegroups.com.

--

L.Klein

CONFIDENTIALITY NOTICE: Any information contained in or attached to this message is intended solely for the use of the intended recipient(s). Any review, retransmission, dissemination of, or taking of any action in reliance upon, this communication by persons other than the intended recipient(s) is prohibited.  If you are not the intended recipient or a person responsible for delivering this message to the intended recipient(s), reply to sender to inform us that you have received this message in error and destroy all copies of the original message. Please do not copy, distribute or use this email or the information contained in it for any purpose without permission of the sender.

[Kim Nilsson]

To share an addon not in the Marketplace, you need to share your Sheet with read-only to anyone with link and /copy at the end of the URL.

So users create an exact copy of your script and Sheets.

[Do Quoc Tuan]

But when users try to run the script it will show the app is not verified (even though the app in the original sheet is verified). I don't want user to see that my app is unverified so publishing the add-on seems to be the only option

On Fri, Mar 19, 2021 at 9:29 AM 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

To share an addon not in the Marketplace, you need to share your Sheet with read-only to anyone with link and /copy at the end of the URL.

So users create an exact copy of your script and Sheets.

--

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/dXytr09z3uk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/64ee2bc8-1481-436a-91b5-5f2fc343eb3dn%40googlegroups.com.

[Alan Wells]

There is a difference between getting your script project verified and publishing to the Google Workspace Marketplace.

Typically, you'd want to get your script project verified before publishing anyway.

It is possible to get verified, in order to avoid the warning message, and not publish to the Google Workspace Marketplace.
Unless something has changed.

[Do Quoc Tuan]

But if my goal is to have users make a copy of my sheet and be able to use my verified script in the copied sheet without seeing the "unverified app" message, then is publishing my app as an add-on in Google Workspace Marketplace the only option? Is there any way to achieve that goal without publishing?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/83664c16-01bf-4f53-800f-c38e6e5c2c10n%40googlegroups.com.

[Kim Nilsson]

You can also in your documentation mention that each customer Workspace admin can change a simple setting to allow local domain scripts to be allowed, without warning.

When they copy your sheet, and get your script inside it, it is no longer your script. So they will not receive updates, of course.

So, I'd call that a last resort solution. Also, they have all your code, so if you plan on getting paid for it, that's going to be hard, as the customer can change anything in the code as they like.

Kim

[Do Quoc Tuan]

My users are normal users who probably have no idea what Google Apps Script is. My goal is to find a way for them to click a button or a dropdown and my script will be run. It is fine if they can see my script but I don't want them to have to change some settings before being able to use my script.

--

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/dXytr09z3uk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a7RJ58KF6L37pQcDQpGqV9BnmP54fN-GsjFaZgbWeHCjw%40mail.gmail.com.

[Alan Wells]

If you add a library to the bound script, then the library setting will also be in the bound script attached to the copy of the Sheet.

If the user copies your Sheet, then the end result will be that the user will have a copy of the template Sheet, and that Sheet will have a script bound to it, and that bound script project will have the library already added to it.

So, your users will automatically be using the library, and the library file will always be the same.

Because the library file is always the same, then when that library file is verified by Google, the user won't see the warning message.

And the script bound to the Sheet is owned by them, so that doesn't need to be verified by Google.

A problem that people have with libraries, is how to update the users to a new version of the library.

But if you add the library to be used as the "head" version and not a numbered version then it will always be using the latest code.

Just don't do any development in the library file.  Do your development in a different Apps Script file.

If you don't want to publish to the Google Workspace Marketplace as an add-on, then what I described above is your only other option.

You could ask your users to add the library themselves, but from how you describe your users, that's not an option.

So, the strategy that I described above avoids the need for the user to add the library themselves because when they copy

the Sheet, BOTH the bound script, and the library added to the bound script get transferred to the copied Sheet file.

I did a test with copying a Sheet file with a bound script that had a library added, and the library was added to the new bound script.

I don't use libraries, so I'm not sure if there is something important that I'm missing,

but I think that it might be worth it to you to try.

Test it yourself before you inform your users.

[Do Quoc Tuan]

Thanks Alan I will give it a try

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9620770f-eecb-451c-8365-71dbb8de704cn%40googlegroups.com.

[Boris Baublys]

Maybe this 'll be helpful for u: https://groups.google.com/g/google-apps-script-community/c/s-Vd1oSDFyQ/m/Xu8q9uT4CAAJ

суббота, 20 марта 2021 г. в 01:49:30 UTC+3, doquoct...@gmail.com:

[kle...@ritenourschools.org]

Are you saying / implying that a library is a GCP project?