As long as you don't need to parse the contents of the subject line, you can do this with If This Then That.
You'll need the Gmail and Google Drive channels. Specifically:
Trigger
Depending on how you're going to identify the incoming message, you'll use one of the following triggers:
- Any new email in inbox
- New email in inbox from
- New starred email in inbox
- New email in inbox labeled
- New email in inbox from search
"Subject" is one of the ingredients available, which you'll obviously need. There's other useful things, like "ReceivedAt".
Action
You'll want Add row to spreadsheet where you'll defined the name of the spreadsheet, what goes in the row, and the folder path in Google Drive where it's located.
There are several recipes already shared that might service. This recipe, for instance, saves a new row for every received message.
I'd use a script in the spreadsheet, see this example.
The script:
function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet(); //Get the active sheet
var sourceRange = e.source.getActiveRange(); //Get currently edited cell
var sourceRow = sourceRange.getRow(); //Get row of currently edited cell
var sourceColumn = sourceRange.getColumn(); //Get column of currently edited cell
var sourceValue = sourceRange.getValue(); //Get value of currently edited cell
if(sourceColumn == 1 && sourceValue != ''){ // If column 1 is edited and has a value (change number if you have name in another column)
var name = sourceValue; // Name is value of edited cell (this is not necessary, only used to show name in questions below)
var age = setData(name, 'How old is ', '? (only numbers)', '^[0-9]*$', true); // Run function setData() (see below) and save return to variable 'age'
if(!age){
return false;
}
sheet.getRange(sourceRow, 2).setValue(age); //set value in currently edited row, column 2 to inserted age
// Do the same as above for title and other (you can of course replace these with any columns you have
var title = setData(name, 'What is ', '\'s title?', false, true);
if(!title){ // if user canceled question (cross in top right corner), stop execution
return false;
}
sheet.getRange(sourceRow, 3).setValue(title);
var other = setData(name, 'Other info about ', '?', false, false);
if(!other){
return false;
}
sheet.getRange(sourceRow, 4).setValue(other);
var otherNum = setData(name, 'Other info about ', '? (only numbers)', '^[0-9]*$', false);
if(!otherNum){
return false;
}
sheet.getRange(sourceRow, 5).setValue(otherNum);
}
}
//Function used for each column
// Parameters:
// Name: name of person edited, only used to show name in quesion
// question1: Question text shown before name (set to '' if none)
// question2: Question text shown after name (set to '' if none)
// format: regular expression for what the text can contain
// required: set to true if user has to enter data, and false if nu input is necessary. When set to true, the popup will just return if you try to pass it without data
function setData(name, question1, question2, format, required){
var data = false;
if(required){ // If current data is required:
while(!data){ // If nothing is entered, or question is canceled (cross in top right corner), keep looping
data = Browser.inputBox(question1 + name + question2); // Show input popup asking for current data
if(data == 'cancel'){ // if user canceled question (cross in top right corner), stop execution
return false;
}
if(format){ // If a format rule was passed, check it
var regEx = new RegExp(format); //Define regex criteria
if(!regEx.test(data)) { // Check if string matches regex criteria
data = false;
}
}
}
// If current data isn't required
}else{
data = Browser.inputBox(question1 + name + question2); // Popup input
if(!data){ // If nothing was entered, set data to empty string
data = '';
if(data == 'cancel'){
return false;
}
}else{ // Something was entered
if(data == 'cancel'){
return false;
}
if(format){ //If format rule was entered, check it
var regEx = new RegExp(format);
do{ // If something is entered, and regex test didn't pass, keep looping
if(!regEx.test(data) && data) {
data = Browser.inputBox(question1 + name + question2); // Popup input
}
}while(!data)
if(!data){
data = '';
}
}
}
}
return data;
}
Hopefully my comments in the code describes most of it. And you can test it in the example sheet.
The main thing is the setData
function, you use it for every column. It displays a prompt where you can enter data. As described in the comments, you have parameters for if the field is required (which wont let the user pass an empty field) and if the field has any formatting requirements, (using regex), pass the regexpresion you want to use to check the formats.
Best Answer
The closest thing to a Boxplot Chart is a Candlestick Chart.
You could calculate all the data needed to plot a box chart: The Five Number Summary and plot each serie individually. Apparently, the width of the box isn't important.
I've prepared an example file: Box Plot example (sample data).
Screenshots
In Google Apps Script, the box charts are un-supported.
Example
I've created an example file for you: Box Plot
References