20190102_Ăśbersichtsbild_MaintainApp_MQ.png
Introduction
Wouldn't it be great to automatically let people know which tasks have to be done at what date?
Do you want to be informed in case a task is overdue?
Do you want someone to be informed in case a response value is out of specification?
--->>> Then MaintainApp is your Google Sheet add-on to use! The MaintainApp is helping you to keep the overview of your processes and informs predefined persons when form responses violate your defined reference values.
Google Forms can be perfectly used to track and evaluate your (maintenance) tasks and to report errors. Google Forms automatically writes the responses of submitted forms into a Google Sheet. The MaintainApp uses the information given by the responses and adds functionality which is defined by you at the <settings> Sheet:
The questions you create within the Google Form are instantly and automatically creating a column at the Answer sheet, which again creates a selectable entry at the MatintainApp <settings> Sheet.
See at the following manual, how to install the Google Sheets Add-on, set up your MaintainApp and make one big step towards operational excellence.
1) create|check form & sheet
In order to set-up the MaintainApp, which means create one form and create one <settings> tab, open an empty Google Sheet and perform following actions:
Go to Menu -> "Add-ons" -> "MaintainApp" -> "Load MaintainApp Menu"
Re-open the Add-ons Menu:
A new form and <settings> tab will be created automatically by clicking Menu -> "Add-ons" -> "MaintainApp" -> "1) create|check form & sheet".
It is also possible to integrate the MaintainApp within an existing Google Form. Just go to your form response sheet and select "1) create|check form & sheet". Attention: it is only possible to evaluate one Google Form in one Google Sheet. You can't evaluate two or more forms within one Sheet!
... et voila. You're one step closer to Operational Excellence already.
<In case the drop-down menu at the <settings> sheet shows permanently "loading...", also perform funtion 1)>
2) general information
In order to get the relevant information which is linked to your MaintainApp, do the following
Go to Menu -> "Add-ons" -> "MaintainApp" -> "2) general information"
set up the Google Form
go to the form which is linked to your sheet by going to Menu -> Form -> edit Form. Then create the check list / error report / ... you want.
You can send reminders and evaluate any answer of your Google Form question towards your defined reference values.
Interval & Reference
First step is to ensure that the form questions you created are answered on a regular basis. Second step is to check the response VALUEs against given specifications:
Interval: PreReminder / Reminder / Escalation is to take place after x days after the last response to your selected question
PreReminder: how many days before the Reminder mail do you want to send a PreReminder mail?
Reminder: how many days after the last response to your question do you want to send a Reminder mail?
Escalation: defines how many days can the Reminder be ignored before an escalation mail is sent to [EMail Escalation]?
Reference: Once you have a response value to your question, which of the following checks you want to perform?
Notification: which response VALUEs should generate a notification mail?
Limit: should a numeric response VALUE generate a mail, if it is above or below a certain limit?
Threshold: should a mail be sent, when the response VALUE exceeds or falls below a certain Threshold?
How to: set up Reminders
In order to make sure, that the questions you defined in your Google Form are answered frequently, you can define following reminders:
PreReminder
The PreReminder value defines how many days before the [Reminder Interval] a mail is sent to [EMail Reminder]
___CONDITIONS:[Reminder Interval] &[EMail Reminder] defined___1 = one day before Reminder2 = two days before Reminder...
Reminder
defines how many days after the last response are reminder mails to be sent to [EMail Reminder]
___CONDITION:[EMail Reminder] defined
___1 = after one day2 = after two days...
Escalation
defines how many days after the first reminder an escalation mail is sent to [EMail Escalation]
___CONDITIONS:[Reminder Interval] &[EMail Reminder] &[EMail Escalation] defined___0 = same day as Reminder1 = one day after Reminder2 = two days after Reminder...
Reminder Example
Let's have a look at how the different settings affect the periodically sent E-Mails:
[Form question] 1) question
Reminder: Seven days after "1) question" was answered, an E-Mail will be sent to "abc1@test.com"
PreReminder: Three days before the Reminder E-Mail, the PreReminder E-Mail wil be sent to "abc1@test.com"
Escalation Reminder: One day after the Reminder E-Mail was sent and "1) question" is still not answered, an E-Mail will be sent to "abc3@test.com"
[Form question] 2) question
Reminder: Every day an E-Mail will be sent to "abc1@test.com" and "abc2@test.com".
PreReminder: NO PreReminder E-Mail wil be sent
Escalation Reminder: Seven days after the first Reminder E-Mail was sent and "2) question" is still not answered, an E-Mail will be sent to "abc3@test.com"
[Form question] 3) question
Reminder: NO Reminder E-Mail will be sent, as [Reminder Interval] is left blank
PreReminder: NO PreReminder E-Mail wil be sent, as no Reminder E-Mail is sent
Escalation Reminder: NO Escalation E-Mail wil be sent, as no Reminder E-Mail is sent
[Form question] n) question
Reminder: NO Reminder E-Mail will be sent, as [EMail Reminder] is left blank
PreReminder: NO PreReminder E-Mail wil be sent, as no Reminder E-Mail is sent
Escalation Reminder: NO Escalation E-Mail wil be sent, as no Reminder E-Mail is sent
3) get reminder summary
In order to get a summary of all status related to this form, you can simply go to Menu -> "Add-ons" -> "MaintainApp" -> "3) get Reminder summary"
An interval status E-Mail will be sent to the GMail account, which owns the Form/Sheet.
4) send reminder manually
PreReminder, Reminder and Escalation Reminder E-Mails are automatically sent once per day (by default at 08:00 am <-> changeable: see section "5) update time trigger").
It might be needed to send all PreReminder, Reminder and Escalation Reminder E-Mails manually, additionally to the daily trigger:
In order to send all Reminder messages manually, go to:
Menu -> "Add-ons" -> "MaintainApp" -> "4) send reminder manually"
after approving, that you want to send the Reminder E-Mails manually, PreReminder / Reminder / Escalation E-Mails are sent to the defined Mail addresses
5) update time trigger
Once a day the reminder status are calculated and E-Mails are send accordingly.
E-Mails are going to be sent according to the last answer DATE of the particular question and the Intervall settings done by the user. By default, the Interval E-Mails are sent at 08:00am. Though, this can be changed for each Forms/Sheets individually.
In order to update the time, when the reminder E-Mails for this form is sent, go to:
Menu -> "Add-ons" -> "MaintainApp" -> "5) update time trigger"
You're requested to insert a new trigger time: The new time trigger value must be a number between 0 and 23.
When the time trigger is updated successfully, a toast message will appear at the lower right corner of the sheet. Here: The Reminder E-Mails will be sent between 11:00am and 12:00am
How to: set up References
As you're sure now, that your questions are processed on a regular basis, it's time to evaluate the responses and trigger E-Mails to different recipients based on your set references.
You can check the values for:
Notifications
Lower and Upper Limits
Thresholds
Notification
You can check questions against specific VALUEs or if the VALUE is not empty.
You can define the Subject and/or the Text of the E-Mail if needed.
Multiple specific VALUEs can be added by seprarating the them with
";" OR ","
--> for example: yes; no OR yes, no
Notification Example
Filling out the form with following response VALUEs
<1) question> answer 1.3
<2) question> answer 2.1
<3) question> answer 3.1
<n) question> answer n.1
generates following E-Mails:
<1) question> answer 1.3
Notification will be sent, as [Notification] = "answer 1.3"
___<2) question> answer 2.1
Notification will be sent, as [Notification] = "AnyValue"
___<3) question> answer 3.1
NO Notification will be sent, as [EMail Notification] is empty
___<n) question> answer n.1
NO Notification will be sent, as [Notification] is empty
Limit
You can check questions against a numeric VALUE, whether they're above or below a reference value.
You can define the Subject and/or the Text of the E-Mail if needed.
Lower and Upper Limit works exactly the same. Therefore following example is valid for both, upper and lower Value comparison. In both cases the E-Mail is sent to [EMail Limit]
Lower Limit Example
Filling out the form with following response VALUEs
<1) question> answer 1.3
<2) question> answer 2.1
<3) question> answer 3.1
<n) question> 40
genereates following E-Mails:
<1) question> answer 1.3
NO E-Mail will be sent, as [Lower Limit Value] is not numeric
___<2) question> answer 2.1
NO E-Mail will be sent, as [Lower Limit Value] is not numeric AND [Lower Limit Value] is empty
___<3) question> answer 3.1
NO E-Mail will be sent, as [Lower Limit Value] is not numeric AND [EMail Limit] is empty
___<n) question> 40
E-Mail will be sent, as VALUE is lower than [Lower Limit Value]
Threshold
You can check questions if a numeric threshold VALUE is passed.
The threshold is zero based which means that a threshold of 50 will trigger E-Mails in case the VALUE exceeds or falls below 0, 50, 100, 150...
You can define the Subject and/or the Text of the E-Mail if needed.
Threshold Example
Filling out the form with following response VALUEs
<1) question> answer 1.3
<2) question> answer 2.1
<3) question> answer 3.1
<n) question> 60
genereates following E-Mails:
<1) question> answer 1.3
NO E-Mail will be sent, as [Threshold] is not numeric
___<2) question> answer 2.1
NO E-Mail will be sent, as [Threshold] is not numeric AND [Threshold] is empty
___<3) question> answer 3.1
NO E-Mail will be sent, as Threshold] is not numeric AND [EMail Threshold] is empty
___<n) question> 60
E-Mail will be sent, as VALUE exceeds the set [Threshold] of 50. An E-Mail will also be generated if the VALUE exceeds 100, 150, etc. or falls below one of the Thresholds
6) add questions
By default you are able to process five question out of your Google Form according to the rules shown here. If you need to process more question, please get in contact with us: maintain.application@gmail.com
The procedure to release more questions with the help of the Lisence Key is as the following:
Navigate to Menu --> "Add-ons" --> "MaintainApp" --> "6) add questions"
Enter the Lisence Key provided by us.
After the lisence key is approved, you'll see a toast message at the bottom right corner of the sheet.
From now on, you'll be able to process your new number of questions at all your Google Sheets.