jeudi 11 novembre 2021

Fetch data from if statement in an object

I have a Script which is triggered by onFormSubmit().

Once a form has been submitted, it should check if the respondent email address is already listed in the linked google sheet. In terms of content, a mail address can only occur a maximum of twice - the one which has been added moments ago and the one which has been added some time before.

If the email address already exists in the google sheet, then there are two steps to do:

First, the script should find out the row indices in which the email addresses occur. Then it should find out which of these two email addresses was the one that has been added first/longer time ago. Second the script should delete this row and only keep the newest row.

I was already able to write the script until finding out the row indices in which there are the same email addresses. But now, I am struggling on how I can evaluate which row number is the smallest and therefore the one longer time ago to delete this row afterwards.

Is anyone able to help me? I would be really really grateful!

Thanks in advance :)

Script:

  function onFormSubmit(){
    var form = FormApp.openById("xxx");
    var formResponses = form.getResponses();
    var formCount = formResponses.length;

    var formResponse = formResponses[formCount - 1];
    var itemResponses = formResponse.getItemResponses();

    var answers = {}; 
    var att = [];

      itemResponses.forEach(
        function (r){
        answers[r.getItem().getTitle()] = r.getResponse()})

      var emailForm = formResponse.getRespondentEmail();

  var url = "xxx"
  var sh = SpreadsheetApp.openByUrl(url).getSheetByName("Form Responses 1");

   var lastRow = sh.getRange("B:B").getValues().filter(String).length;

   var rg = sh.getRange(2, 2, lastRow, 1);
   var vA = rg.getValues();


   for(var i = 0; i < lastRow; i++){
     if(vA[i][0] == emailForm) {
       var rowIndex = sh.getRange(i,2).getRowIndex()+2;
       Logger.log(rowIndex)
     }
   }
 }

Aucun commentaire:

Enregistrer un commentaire