0

I'm trying to check whether there are duplicate phone numbers in a column and to then return the first id number associated with the phone match in google apps script.

I created an if statement, according to every reference I've checked I've coded it right. The script runs but nothing populates??

I defined the ranges for the count that has the phone number and id number associated with it. I ensured its in the correct array my using map and used a basic if else statement.

Everything seems to run but the statement doesn't throw out the id numbers. In fact nothing happens.

Please help...

function myFunction() {

 var sss = SpreadsheetApp.openById('spreadsheetid'); // sss = source spreadsheet
 var ss = sss.getSheetByName('spreadsheetname'); // ss = source sheet

 var lr = ss.getLastRow();

  for (var i=3;i<lr;i++) {

   var phone = ss.getRange(i,4,lr).getValues();  
    var id = ss.getRange(i,1,lr).getValues();

   var phonearray = phone.map(function(r){return r[0]});

    if(phonearray == phone[i]){

    //emailarray.indexOf(email[i]) &&

    ss.getRange(i,30,lr).setValue(id[i]);


    } else

       ss.getRange(i,30,lr).setValue("");

   }
}
littledreamer
  • 3
  • 1
  • 1
    `if(phonearray == phone[i]){` what is this supposed to check? `phonearray` is an *array* and thus [it will not be equal to anything but itself](https://stackoverflow.com/questions/30820611/why-doesnt-equality-check-work-with-arrays). `phone[i]` is part of the original array that was used to create `phonearray` through `phone.map()`. These two can never be equal but I don't know what should happen. – VLAZ Jan 21 '20 at 11:50
  • Thanks for your reply. I want to check the column for any duplicates. was thinking of changing if(phonearray == phone[i]){ to if(phonearray.indexOf(phone[i])){ – littledreamer Jan 21 '20 at 11:56

1 Answers1

0

There are some things you need to consider

  1. A valuerange is a 2-dimensional array, thus to access the value of an individual cell you need to define phone[i][0] instead of phone[i]
  2. The method getRange requires the syntax getRange(startRow, startColumn, numRows) rather than getRange(startRow, startColumn, lastRow). Keep this in mind when you retrieve your range of interest.
  3. Searching for duplicates will require in your case two (nested) for loops.

Please find below a sample of how you can modify your code to achieve the desired functionality:

function myFunction() {
 var sss = SpreadsheetApp.openById('spreadsheetid'); // sss = source spreadsheet
 var ss = sss.getSheetByName('spreadsheetname'); // ss = source sheet
 var lr = ss.getLastRow();
 var phones = ss.getRange(3,4,lr-3+1).getValues();  
 var ids = ss.getRange(3,1,lr-3+1).getValues();
 var phonearray = phones.map(function(r){return r[0]});
  for (var i=0;i<=(lr-3);i++) {
        var val = phones[i][0];
        var duplicates = [];
    for(var j = 0; j < phonearray.length; j++){
      if (phonearray[j] == val){
            duplicates.push(j);
      }
    }    
    if(duplicates.length>1){            
       Logger.log("duplicate");
      //emailarray.indexOf(email[i]) &&
      ss.getRange((i+3),30).setValue(ids[i]);
    } else{
       Logger.log("no duplicate");
       ss.getRange((i+3),30).setValue("");
    }
  }
}
ziganotschka
  • 25,866
  • 2
  • 16
  • 33