Google Form answers validation with sheet and send email to users based on it

Question

Data SheetRequirement :-

I am trying to write an Google App script to send email based on their answers while form submission for travel expenses. i.e if form users choose x as their city y as their per diem rate(allowance per day) script should check the google sheet attached for predefined values and if the user had entered the value more than allocated for a city then it should email admins to review the request else email users that is auto approved.

I know this can be done via if loop but pretty new to coding struggling to pull this condition any help would be great

function onformsubmit(e){   
  var townCity = e.values[11];
  var currency = e.values[12];
.... // other lines of function
}

function sendEmails(townCity,currency){//var imported for other function for validation
  var ss = SpreadsheetApp.openById("ssid");
  var sheet = ss.getSheetByName("Rates for validation check");
  var lastRow = sheet.getLastRow();
  var city= sheet.getRange(2, 2, 141); // Fetch the range, including the minimums column 2 for city list in data sheet
  var budget= sheet.getRange(2, 3, 141); // Fetch the range, including the minimums column 3 for allocated budget for each city in dat sheet
  var location = city.getValues(); // Fetch values for each row in the Range.
  var location2 = budget.getValues();
  Logger.log(townCity); // log test 
  if (townCity == location && currency == location2 ) {
    Logger.log('sendemail');}
  else if(townCity == location && currency != location2) {
    Logger.log('dontsendemail');
  } 
}

latest form sheet

Full Code

// When Form Gets submitted
function onFormSubmit(e) {
//Get information from form and set as variables
var emailAddress = e.values[1];
var clientName = e.values[4];
var StartDate = e.values[6];
var EndDate = e.values[7];
var country = e.values[9];
var townCity = e.values[8];//this value here is not been picked correctly as the contents in the column were retried by formula from all the other city columns
var amount = e.values[12];
var currency = e.values[11];
var engagementLeaderName = e.values[2];
//Logger.log('Lets see if its here'+ townCity);
}
  //funtion to validate with data sheet
function sendEmails(amount,townCity){
  var ss = SpreadsheetApp.openById("ssid");
  var sheet = ss.getSheetByName("Rates for validation check");
  var firstRow = 2;
  var firstCol = 2;
  var numRows = sheet.getLastRow() - firstRow + 1;
  var numCols = 2;
  var allocated = sheet.getRange(firstRow, firstCol, numRows, numCols).getValues();
  var index = allocated.map(function(value) {
    return value[0];
  }).indexOf(townCity);
  if (amount > allocated[index][1]) {
   // Send email:
   Logger.log('send email')
  }
}

form screencap

Answer 1

If I understood you correctly, you want to do the following:

• When the user submits the form, grab the values from the response (retrieved with the event object): townCity and currency.
• Retrieve the data from another sheet, which contains a list of cities (column B) and the allocated budget for each city (column C).
• Find the city the user submitted, and check its allocated budget.
• If the currency that was submitted by the user is higher than the allocated budget, send an email.

If that's correct, you can change your function sendEmails to this one:

function sendEmails(townCity, currency){
  var ss = SpreadsheetApp.openById("ssid");
  var sheet = ss.getSheetByName("Rates for validation check");
  var firstRow = 2;
  var firstCol = 2;
  var numRows = sheet.getLastRow() - firstRow + 1;
  var numCols = 2;
  var allocated = sheet.getRange(firstRow, firstCol, numRows, numCols).getValues();
  var index = allocated.map(function(value) {
    return value[0];
  }).indexOf(townCity);
  if (currency > allocated[index][1]) {
    // Send email:
    // MailApp.sendEmail(recipient, subject, body)
  }
}

In this function, predefined cities and budgets are retrieved in a single 2D array, and indexOf is used to find the index of the submitted city in this array. Once this index is retrieved, it is used to find the corresponding budget allocated[index][1] and compare it to the submitted currency.

UPDATE:

In the form, you have several questions called Town/City for {country}, one for each country. To know city to look for, you need to first check the response from question Country. If that's the case, you can use the event property namedValues, an object that contains the question names and values from the form submission.

It could be something like this (I'm assuming you have a question called Currency:

function onFormSubmit(e) {
  var country = e.namedValues["Country"][0]; // Retrieve country from form submission
  var currency = e.namedValues["Amount"][0]; // Retrieve currency from form submission
  country = country.charAt(0).toUpperCase() + country.slice(1).toLowerCase(); // Transform country from all capitalized to first character capitalized
  var townCity = e.namedValues["Town/City for " + country][0]; // Look for value corresponding to this country town/city
  sendEmails(townCity, currency);
}

UPDATE 2:

The problem you were facing is that the question names are a bit different than expected:

• In the country town/city sections, all question names should have the format Town/City for {countryName}, while in many cases, in your form, it's Town/City {countryName}, without the for. You have to fix this for all your countries in your form (or change the script correspondingly).
• Because the format of the countries are sometimes all capitalized, toLowerCase has to be used for the country name to have the same format as in the following section (only first character capitalized). I added toLowerCase in the script to avoid this issue.
• The property you want to track for, I assume, is Amount, not Currency. I edited that in the script.

Notes:

• I am assuming that you already installed the form submit trigger. If that's not the case, you should install it, either manually or programmatically.
• I am assuming you already know how to send the email. If that's not the case, you can start by checking MailApp.sendEmail(recipient, subject, body).

Reference:

• Array.prototype.indexOf()