Google forms approval workflow

Question

I'm trying to set up a workflow using Google Forms, Sheets and Apps Script. For a simple example lets say I am setting up a leave form for a company. I have a form for the employee enters their leave details. On submit the details are populated into a sheet and an email is sent to an someone for approval.

This is where I am getting stuck. What is the best way to approve the leave requests? I can just edit the sheet and once the column has been changed from unapproved to approved and email could be sent but I was hoping to avoid having to edit the sheet at all.

Is there a better way to handle this workflow?

The email sent to the reviewer could have a link to a second Google Form for the approval. Another option would be to have some HTML in the email that would trigger a stand alone apps script file that finishes the process. A link in an email can make a GET request to a doGet() function. An HTML form can make a POST request to doPost() — Alan Wells

Alan Wells Alan Wells · Accepted Answer · 2019-07-23T16:53:42

Assume that 3 people need to approve the request. The approvers that need to review the request are approvers X,Y,Z.

User X gets an Email requesting that they review the request
The email that user X gets will use HTML
The HTML will have a link in it
A link in HTML submits a GET request
Whenever you click a link, or enter a url into the browser address bar, a GET request is made to the url
An Apps Script Web App is an Apps Script project that is published as a Web App
A Web App has a url that can be used to make a request to it
When the url (link) is used, a GET request is made
A GET request made to an Apps Script Web App will run the doGet() function
Once that doGet() function run, you can have your Apps Script code do anything that you want
It can get information sent in to it from a search string
Then send out another email to user Y
So, when user X clicks the link, the url needs to have information appended to the url in the form of a search string

httpz://the link?whichUser=X&approved=true

You can get the user and whether it was approved or not from the "event object" often designated by the letter "e"

function doGet(e) {
  var whatUserJustReviewed,isItApproved;

  //Get the values passed in from "e" which is the event object
  eventParam = e.parameter;
   whatUserJustReviewed  =  eventParam.whichUser;
  Logger.log(' whatUserJustReviewed    : ' +  whatUserJustReviewed    )

  isItApproved = eventParam. approved;


  switch (whatUserJustReviewed) {
    case "X"
    if (isItApproved  === 'true') {
      //    //Send an email to the next user which is Y
    }
    break;
    case "Y"
    if (isItApproved  === 'true') {
      //    //Send an email to the next user which is Z
    }
    break;
    case "Z"
    if (isItApproved  === 'true') {
      //Send an email to all involved who need to know that it was approved.
    }
    break;
   default:
      console.error('There was an error getting user or status');
  };


}

Google forms approval workflow

1 Answers