0

I'm trying to create a Google Forms add-on that manages approval workflows

Here is the idea:

  • User A installs the add-on and sets up the approval workflows in the add-on configuration.

User A will be the owner of the Form Responses spreadsheet.

  • User X is a recipient in that approval workflow

(there might be other recipients such as Y, Z, ... in the workflow, but for simplicity, let's assume that there is only 1 recipient X)

  • User B is the respondent, he goes to the form URL and submits a response (this is a request that will be approved/rejected by user X)

  • After user B submits a request, an email will be sent to user X's Gmail

  • In this email, there are 1 button: Approve/Reject this request

  • After user X clicks this button, a new tab is opened. And in this new page, user X can click 2 buttons Approve or Reject to give his feedback on the request of user B

This page is a normal webpage that is built with non-Apps Script code (for example Nodejs + React)

  • An email will be sent to both users B and X to tell them that the request is approved/rejected

  • Finally, in the Form Responses spreadsheet of user A (who installed and configured the add-on), the Approve/Reject status of the request will be updated accordingly

The question is: How a non-Apps Script code can modify the Form Responses spreadsheet?

From my understanding, in this case, there are only 2 things that can modify the Form Responses spreadsheet:

  1. The Forms add-on code
  2. The bounded script of the Forms Responses spreadsheet (the destination spreadsheet)

But this is a Forms add-on, so there is no bounded script for the destination spreadsheet, so only option 1. The Forms add-on code is feasible

=> There must be a way so that non-Apps Script code can notify The Forms add-on code to update the spreadsheet

Here is my solution at the moment (still not good enough):

  1. I will store the workflow data (configs, responses) on Firebase Cloud Firestore (a real-time document database, it's like the combination of MongoDB and Firebase Realtime Database)

  2. Each time a request is approved/rejected by the recipient, my non-Apps Script code will update the data on Firestore

  3. My Forms add-on code will listen to the changes on Firestore and will update the spreadsheet accordingly

I said that this solution is still not good enough because as you can check my another question here Google Apps Script - How to listen for realtime updates in Firebase Cloud Firestore?

=> At the moment, there might be no way for Apps Script to listen to realtime update on Firestore

=> The workaround is to use a time-based trigger to periodically check for new data on Firestore (but can only do once per hour at most because of the Apps Script quotas)

=> Once per hour to see new updates in the destination spreadsheet is not a good UX for users, it should be realtime)

Any idea how to solve this problem without the once per hour limit?

Rubén
  • 34,714
  • 9
  • 70
  • 166
0xh8h
  • 3,271
  • 4
  • 34
  • 55
  • 2
    You can try [tag:google-sheets-api] – TheMaster Jul 27 '19 at 15:46
  • @TheMaster: The problem is, my non-Apps Script code won't know which spreadsheet to be updated using google-sheets-api. The destination spreadsheet is generated per user, so if my addon has 100 users, there will be 100 destination spreadsheets. And in that case, using google-sheets-api in my non-Apps Script code is not feasible. – 0xh8h Jul 28 '19 at 01:46
  • @TheMaster: please check my replies on Rubén answer to see if I understand it correctly. Thank you very much – 0xh8h Jul 28 '19 at 05:28
  • 1
    Send the spreadsheet id as a url parameter in the Approve/reject buttons in the email. You can also provide a Google picker in your react front end. – TheMaster Jul 28 '19 at 06:47
  • In that case, the owner of the destination spreadsheet (who installs the addon) needs to give my non-Apps Script the permission to edit his file, right? – 0xh8h Jul 28 '19 at 07:08
  • 1
    Yes. No getting around authorization(Although You can try sharing the add-on cloud project with the non-apps script web app, if it is within Google's TOS). – TheMaster Jul 28 '19 at 07:19
  • Is it real-time though? If it is they might be simply polling their server every minute or so from the side bar html/js. – TheMaster Jul 28 '19 at 08:20
  • I'm not sure if it's 100% realtime, but it should work without forcing the user to open the add-on sidebar all the time, he can turn off his PC and everything should work normally. So adding the polling code in the sidebar html/js is not the answer :D – 0xh8h Jul 28 '19 at 12:04
  • 1
    I'm guessing then they must be using the same cloud project or authorization provided by the user oninstall of add-on to use the sheets api. Other than the api/web-app there are no other routes in from the external server. – TheMaster Jul 28 '19 at 12:51
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/197115/discussion-between-hoang-trinh-and-themaster). – 0xh8h Jul 28 '19 at 13:36

1 Answers1

2

The question is: How a non-Apps Script code can modify the Form Responses spreadsheet?

(As already mentioned on a comment by TheMaster) use Google Sheets API.

=> There must be a way so that non-Apps Script code can notify The Forms add-on code to update the spreadsheet

Yes there is, actually, there are. You could:

  1. create a web application that listed to an HTTP POST request as part of you Form Add-on for details see https://developers.google.com/apps-script/guides/web
  2. use the Apps Script API to execute functions from your Form Add-on. For details see https://developers.google.com/apps-script/api/how-tos/execute

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Also, here is what I replied to TheMaster comment "The problem is, my non-Apps Script code won't know which spreadsheet to be updated using google-sheets-api. The destination spreadsheet is generated per user, so if my addon has 100 users, there will be 100 destination spreadsheets. And in that case, using google-sheets-api in my non-Apps Script code is not feasible." – 0xh8h Jul 28 '19 at 02:40
  • About your 2nd solution, because my script is an addon, so if I "create a web application that listen to an HTTP POST request as part of your Form Add-on", after that if a user installs my addon, does it create a new web app URL for each user? In that case, how my `non-Apps Script` code knows which url to send? – 0xh8h Jul 28 '19 at 03:05
  • In case it creates only 1 URL for the web app (doPost) that I manage, how this web app know which destination spreadsheet to write on? Because there can be 100 destination spreadsheets for 100 users? – 0xh8h Jul 28 '19 at 03:07
  • so as I understand, I need to have 3 things: 1. The Forms add-on code that must enable the API to update the spreadsheet, 2. The Apps script web app that has doPost trigger to accept POST request, 3. The Node.js/React web application that will send POST request to the Apps script web app in 2 ? – 0xh8h Jul 28 '19 at 05:27
  • @Ruben Can a add-on publish a web-app? Will including a doGet/doPost in the source code automatically transform it into a web-app? – TheMaster Jul 28 '19 at 06:56
  • @TheMaster: yes, you need to include a doGet/doPost in the add-on source code AND in the script editor, choose Publish -> Deploy as web app. Then you will have an URL to access the web app. But still, I don't understand how the flow with these web apps work *per user* – 0xh8h Jul 28 '19 at 07:56
  • 1
    @Hoang If you're ok with that manual step (choose Publish -> Deploy as web app) and it works from the add-on, it should be easy to post the approve to your apps script web-app from react. Each user will have a unique url and that can be programmatically obtained by `ScriptApp.getService().getUrl()`, which can be sent as a url parameter in the approve email. Your react will simply parse the url and post to the user's GAS web-app – TheMaster Jul 28 '19 at 08:06
  • @TheMaster: Are you sure that "Each user will have a unique url" ? I tested it, after I clicked Deploy as web app, it shows a fixed URL of the web app (/exec for deployed version and /dev for latest code). I don't know what is the behavior when my user installs the add-on because I haven't published any add-on yet. – 0xh8h Jul 28 '19 at 12:01
  • @Hoang No. I'm not even sure users can deploy the web-app from the add on code. But if that is the case, even if it is the same url, all you have to do is post identifying data such as spreadsheet id/user id. – TheMaster Jul 28 '19 at 12:48
  • 2
    @HoangTrinh Lets take some steps back. An Add-on uses code that is hosted on a Google Apps Script project which can be used to publish a web app. A Google Apps Script's web app has a unique web address, not an web address for each user, but you could pass an URL parameter as a user token. Anyway, if you need further help by means of [so]'s basic model (questions and answers) please post a follow up (new) question. – Rubén Jul 28 '19 at 15:02
  • 1
    @Rubén: thank you. I think I found what I want. I can use the same OAuth credentials that Apps Script generated automatically (for Standard Cloud Platform projects) for my Node.js application to access Sheet API prnt.sc/ol3ft3 https://developers.google.com/apps-script/guides/cloud-platform-projects#standard_cloud_platform_projects – 0xh8h Jul 28 '19 at 15:35
  • Not sure if this way is actually working but it looks promising and I will try it tomorrow morning – 0xh8h Jul 28 '19 at 15:36
  • @HoangTrinh Once you tested it consider to share your findings / conclusion as an answer. – Rubén Jul 28 '19 at 15:36
  • Sure I will post the answer here once I find it working :D – 0xh8h Jul 28 '19 at 15:38
  • @Rubén: well, it's not working as I expected. We CAN'T use the generated Apps Script OAuth credentials that way. We must create a new one. – 0xh8h Jul 29 '19 at 08:39
  • So I think using web app/api solution is the only way so that user don't have to accept permissions twice – 0xh8h Jul 29 '19 at 08:47
  • @Rubén: if you have time, please check my other question here, I found something very interesting that might be related to this question: https://stackoverflow.com/questions/57386389/google-apps-script-how-to-use-scope-https-www-googleapis-com-auth-script-we – 0xh8h Aug 07 '19 at 03:00