How to match two submitted values with google sheet cells and return html templates based on that

Question

In the below sheet:

https://docs.google.com/spreadsheets/d/15aBW7eoddWj_AiA032pMuUk7_GvI-xG6siVF5wJuh8g/edit#gid=2104954217

In Sheet1: I have some names in column C and their codes in column B

And Sheet2 where users submit their names and codes

I need to compare the new submitted name in Sheet2 with the names in Sheet1 and whenever we have a match, compare the newly submitted code with the relative code in Sheet1 at which;

If the new submitted name matches with any name in Sheet1, proceed to match the code at which;
- If the code matches with the code next to the name in Sheet1, return HTML template that says confirmed
- If the code does not match with the code next to the name in Sheet1, return HTML template that says wrong code
If the name does not match with any name in Sheet1, return HTML template that says the wrong name

I have tried the below code.gs:

function doGet() {
  return HtmlService.createTemplateFromFile("Form.html")
    .evaluate()
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function doPost(e) {
  var lock = LockService.getScriptLock();
  lock.tryLock(10 * 1000);

  try {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName("Sheet2");
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1;
    var newRow = headers.map(function(header) {
      return header === "Timestamp" ? new Date() : e.parameter[header];
    });
    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    var name = newRow[3];
    var code = newRow[2];

    var sh = doc.getSheetByName("Sheet1");
    var names = sh.getRange(2, 1, sh.getLastRow() - 1, 1).getValues();

    for (var i = 0; i < names.length; i++) {
      if (names[i] == name) {
        var row = i;
        var existingCode = sh.getRange(row, 2).getValue();
        if (existingCode == code) {
          return HtmlService.createTemplateFromFile("Confirmation.html")
            .evaluate()
            .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
        } else {
          return HtmlService.createTemplateFromFile("codeError.html")
            .evaluate()
            .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
        }
      } else {
        return HtmlService.createTemplateFromFile("nameError.html")
          .evaluate()
          .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
      }
    }
  } catch (e) {
    return ContentService.createTextOutput(
      JSON.stringify({ result: "error", error: e })
    ).setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

But it gives wrong names all the time. The sheet is editable so feel free to use it and thanks in advance for helping

ziganotschka ziganotschka · Accepted Answer · 2020-07-03T14:46:57

Two things:

row[3] does not exist - the values in column D are populated by an Array formula in your sheet - not by your posted values. Also, if you want to compare the lower case versions of the names, you need to use column C in Sheet1 instead of column A
If you use a for loop the way you do - for each loop iteration where the name does not correpond with the row entry you will run into the name error situation and the else statement will directly call 'nameError.html'.

I recommend you to use instead indexOf() that not only retrieves either the name is preent in the sheet, but also returns you the position - so no need for loops.

Sample:

function doGet() {
  return HtmlService.createTemplateFromFile('Form.html').evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function doPost (e) {
  var lock = LockService.getScriptLock();
  lock.tryLock(10 * 1000);  
  try {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName("Sheet2");
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1
    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header]
    })
    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])    
    var name = sheet.getRange(nextRow, 4).getValue();
    var code = newRow[2];    
    var sh = doc.getSheetByName("Sheet1");
    var names = sh.getRange(2, 3, sh.getLastRow()-1, 1).getValues();
    if (names.flat().indexOf(name) != -1) {     
      var existingCode = sh.getRange(names.flat().indexOf(name)+2, 2).getValue();
      if (existingCode == code){
        return HtmlService.createTemplateFromFile('Confirmation.html').evaluate()
        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
      } else {
        return HtmlService.createTemplateFromFile('codeError.html').evaluate()
        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
      }              
    }      
    else {
      return HtmlService.createTemplateFromFile('nameError.html').evaluate()
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
    } 
  }  
  catch (e) {
    return ContentService
    .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
    .setMimeType(ContentService.MimeType.JSON)
  }  
  finally { lock.releaseLock() }
}

How to match two submitted values with google sheet cells and return html templates based on that

1 Answers