Web app that talks to spreadsheet and displays formula result - open source

[--Hyde]

I have not seen too many code samples of web apps where the heavy lifting is done by spreadsheet formulas, so here's a demo:

• web app that shows a page with a form to enter parameters and show the result
  
• spreadsheet where the data is stored and results are calculated
  

To do a dry run, enter text in the web app and observe the results in the web app and the spreadsheet.

You can File > Make a copy of the spreadsheet to try it out yourself. The web app code is bound to the spreadsheet in Tools > Script editor.

Cheers --Hyde

[David Gentile]

Cool! But why...? 😁

[--Hyde]

Hi David,

With ScriptDB gone, and replacements like Firebase not suitable for all applications or people I help, I will most likely use the pattern shown in the demo for quick and simple applications where the 5-million cell limit of Google Sheets is not an issue.

There are also some things that are easy and fast to do in a spreadsheet but difficult to implement and debug in JavaScript — e.g., query() with pivot.

Cheers --Hyde

On Wednesday, January 29, 2020 at 12:14:24 PM UTC+2, David Gentile wrote:

Cool! But why...? 😁

[Yasir Karam]

😇

Yasir Karam about.me/karamyasir

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/144393d2-bbb9-45b4-866f-7be15fdff22b%40googlegroups.com.

[Kim Nilsson]

Thanks!

An example like this might have helped my development a few weeks ago.

Practical examples are great, as those in the documentation may be too standard and not actually accomplish what a new user wants.

[Yasir Karam]

Hi Hyde,

    Why does embedding the script (web app link) in a normal static page doesnt execute the script properly? even when setting public permissions access over the template, the excel sheet and script?

Yasir

Yasir Karam about.me/karamyasir

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/840b9fc5-bad1-42ff-83ed-e9f1ce1bbe00%40googlegroups.com.

[--Hyde]

Why does embedding the script (web app link) in a normal static page doesnt execute the script properly? even when setting public permissions access over the template, the excel sheet and script?

Hi Yasir,

The user does not need any rights to the Google Sheet nor the web app script. I do not know what you are referring to with the "template."

Are you using the Web App Demo link I gave in my message of 28 January, or did you make a copy of the spreadsheet and deploy your own web app?

Did you deploy it so the app runs under your (the developer's) account, or the account of the "user accessing the app"?

What did you choose under "Who has access to the app"?

Do you have a <a href> link to the app on the "normal static page", or are you embedding the app in an iframe?

Cheers --Hyde

[Yasir Karam]

Yes Hyde I guess I embedded it as code with <a href>

https://sites.google.com/view/yasir-karam/home

Yasir Karam about.me/karamyasir

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/4561c309-7698-4668-a929-8bfd9ad445b2%40googlegroups.com.

[Yasir Karam]

Here's the link of script which I can run on my account after giving needed OAuth permissions 

https://script.google.com/macros/s/AKfycbwtjLoAx3ZfTUqE2Cc-DfdWMIpvtSqmUiFSRGB8zsBT/dev

When embedding this link in a Google Static Site page, it only shows after authentication not for public user, so answering your question "Did you deploy it so the app runs under your (the developer's) account, or the account of the "user accessing the app"?" I have given permissions for public when I published it as web app.

Yasir Karam about.me/karamyasir

On Mon, Feb 3, 2020 at 12:40 AM --Hyde <ari.hy...@gmail.com> wrote:

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/4561c309-7698-4668-a929-8bfd9ad445b2%40googlegroups.com.

[--Hyde]

it only shows after authentication not for public user

This indicates that you have deployed the web app so that it runs under the account of the "user accessing the app". Since you do not want the user to go through the authorization process, that is the wrong option. You should deploy the app so that runs under your (the developer's) account.

so answering your question "Did you deploy it so the app runs under your (the developer's) account, or the account of the "user accessing the app"?" I have given permissions for public when I published it as web app.

Nope, you cannot authorize an app on behalf of someone else. Permission to access an object is not the same thing as authorizing an object to do something under your account.

Deploy the web app correctly to avoid the user having to go through the authorization flow.

When embedding this link in a Google Static Site page

The link to the web app will work the same way regardless of how the user accesses it.

Cheers --Hyde

[Yasir Karam]

Here's screenshot of the deployment of the web app under my account as a developer, the question of "Who has access of the app" is "Anyone"

Yasir Karam about.me/karamyasir

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/7cc485cd-1ae2-40eb-bf36-d0b4990ec91a%40googlegroups.com.

[--Hyde]

Yep, the screenshot confirms that you have deployed the web app so that it runs under the account of the "user accessing the app". Since you do not want the user to go through the authorization process, that is the wrong option. You should deploy the app so that runs under your (the developer's) account.

Cheers --Hyde

[Yasir Karam]

Hmm now I got it Hyde, so there might be no sort of fully anonymous access like any web app on the web. Users have to have Google account to be able to access the app or run it, or else they request rights from the object owner/developer, am I guessing right?

Yasir Karam about.me/karamyasir

On Mon, Feb 3, 2020 at 2:21 PM --Hyde <ari.hy...@gmail.com> wrote:

Yep, the screenshot confirms that you have deployed the web app so that it runs under the account of the "user accessing the app". Since you do not want the user to go through the authorization process, that is the wrong option. You should deploy the app so that runs under your (the developer's) account.

Cheers --Hyde

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/e3bb7eaa-a992-4dad-9eda-d84cdd242b2b%40googlegroups.com.

[--Hyde]

Hmm now I got it Hyde, so there might be no sort of fully anonymous access like any web app on the web. Users have to have Google account to be able to access the app or run it, or else they request rights from the object owner/developer, am I guessing right?

Hi Yasir,

If you deploy the app the right way, the user does not need to be signed in to Google, and does not need to authenticate. They can have fully anonymous access.

You may be confusing two different things: "execute the app as" and "who has access to the app." With the requirements you have mentioned, you should set the app to:

1. execute as you (the developer) — based on your screenshot, you have not set this correctly
   
2. give access to anyone — based on your screenshot, you have set this correctly
   

With these settings, the user does not need any rights to the Google Sheet nor the web app script source code file, but they can still access the app and run it.

Cheers --Hyde