Copy value from one row to another if string matchs

Question

I need to copy the entire row of Google Sheets if mail set in Form matches with the one stored in a data row. This is an example of my code, but the problem is that when it's "true" and the mail matches with the one on Sheets, it copies the last row and not the one with the mail matched.

Example:

|Nombre y apellido completo:|   DNI:  | Correo electrónico |
| ------------------------- |-------- |--------------------|
|sdasdas                    | 3213213 | test1@gmail.com    |
|sdasdas                    | 3213213 | test2@gmail.com    |

test1@gmail.com is valid so it should copy test1 but in my case is copying test2 data row.

|Nombre y apellido completo:|   DNI:  | Correo electrónico |
| ------------------------- |-------- |--------------------|
|sdasdas                    | 3213213 | test1@gmail.com    |
|sdasdas                    | 3213213 | test2@gmail.com    |
|sdasdas                    | 3213213 | test2@gmail.com    |

it should say test1@gmail.com

deleteTriggers()

var form = FormApp.getActiveForm();
ScriptApp.newTrigger('selectedItems')
    .forForm(form)
    .onFormSubmit()
    .create();

function selectedItems() {

    var form = FormApp.getActiveForm();
    var formResponses = form.getResponses();
    var formResponse = formResponses[formResponses.length - 1];
    var itemResponses = formResponse.getItemResponses();

    var name = "";
    var email = "";
    var gender = "";
    var choice = "";
    var usedEmail = "";

    for (var i = 0; i < itemResponses.length; i++) {
        switch (itemResponses[i].getItem().getTitle()) {
            case "Correo electrónico":
                email = itemResponses[i].getResponse();
                break;
            case "Sexo:":
                gender = itemResponses[i].getResponse();
                break;
            case "Nombre completo:":
                name = itemResponses[i].getResponse();
                break;
            case "¿Estuviste antes en una reunión?":
                choice = itemResponses[i].getResponse();
            default:
            case "Correo electrónico usado:":
                usedEmail = itemResponses[i].getResponse();
                break;
        }
    }


    getItems(email, usedEmail, choice, name, gender)

}


function getItems(email, usedEmail, choice, name, gender) {
    var spreadSheet = SpreadsheetApp.openById(form.getDestinationId());
    var spreadSheetData = spreadSheet.getDataRange()
    var rowValue = spreadSheetData.getValues()
    Logger.log(rowValue)
    var bool

    if (choice == "Sí") {
        for (var i = 0; i < rowValue.length; i++) {
            for (var j = 0; j < rowValue[i].length; j++) {
                if (usedEmail.indexOf(rowValue[i][j]) != -1) {
                    rowValue[i][0] = new Date()
                    var rowValues = rowValue[i]

                    Logger.log(rowValue[i])
                    bool = true
                } else {
                    bool = false
                }
            }
        }
    }

    Logger.log(usedEmail)
    Logger.log(bool)
    if (bool == true) {
        setItems(spreadSheet, rowValues)
        //RespuestaAutomatica(name, email, gender)
        Logger.log("Inscripción válida")
    } else {
        Logger.log("Inscripción fallida, email no registrado")
    }

}

function setItems(spreadSheet, rowValues) {
    spreadSheet.appendRow(rowValues)
}

function deleteTriggers() {
    var allTriggers = ScriptApp.getProjectTriggers();
    for (var i = 0; i < allTriggers.length; i++) {
        ScriptApp.deleteTrigger(allTriggers[i]);
    }
}

user_16118686 · Answer

Try this modification on your script. See comments for details.

Modification:

function getItems(email, usedEmail, choice, name, gender) {
  var spreadSheet = SpreadsheetApp.openById(form.getDestinationId());
  var spreadSheetData = spreadSheet.getDataRange()
  var rowValue = spreadSheetData.getValues()

  // CHANGES START HERE:
  // find what column is your emails located, I assume the header is 'Correo electrónico'
  var emailHeaderCol = rowValue[0].indexOf('Correo electrónico')
  // create array for email column
  var existingEmails = rowValue.map(x => x[emailHeaderCol])
  
  if (choice == "Sí") {
    var index = existingEmails.indexOf(usedEmail)
    if (index > -1) {
      rowValue[index][0] = new Date()
      var rowValues = rowValue[index]
      bool = true
    }
    else {
      bool = false
    }
  }
  // CHANGES END HERE:

  if (bool == true) {
      setItems(spreadSheet, rowValues)
      //RespuestaAutomatica(name, email, gender)
      Logger.log("Inscripción válida")
  } else {
      Logger.log("Inscripción fallida, email no registrado")
  }
}

Copy value from one row to another if string matchs

1 Answers

Modification:

Output: