Search code examples
google-apps-scriptgoogle-sheetsgoogle-formsgoogle-apps-script-addon

How to manage form responses spreadsheet from non-Apps Script code (Node.js for example)

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?

Solution

  • 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