How to search for values of an array in a Google Sheet column and insert array value if not found?

Question

I have an Array of data in Google Script(around 10 items in the array). How can I search for those array items in column A? (for example: search for items starting from cell a12 till end) and if array values not found in column A then add them to end of column A?

I tried to use

data[n][0].toString().match(values_array[i])==values_array[i]

and the second next time i run the function i see repeated data get entered!

For example the data from A1 to A10 are as follows:(1,2,3,4,5,6,7,8,9,10) and first time i run the function it adds all data to column A(starting a12)

And the second time i run the function with same data from A1 till A10 ,it should not enter anything but it adds 1,2,3,4,5,6,7,8,9,10 again!I don't know why this code is acting like this!

I would appreciate if an expert tell me how to fix this code.Thanks in advance.

function  find2(){
  var sheet = SpreadsheetApp.getActiveSheet(),
      range,
      values_array; 

  range = sheet.getRange('a1:a10');
  values_array = range.getValues();

  var sh = SpreadsheetApp.getActiveSheet();
  var range2 = sh.getRange('a12:a')
  data = range2.getValues(); // read all data in the sheet

  var lastRowIndex=getLastColumnRow(0)

  for (var i = 0; i < values_array.length; i++) {
    for(n=0;n<data.length;++n){ // iterate row by row and examine data in column A
      if(data[n][0].toString().match(values_array[i])==values_array[i]){
      } else {
        var lastEmptyRowIndex=1+lastRowIndex+i
        SpreadsheetApp.getActiveSheet().getRange('a'+lastEmptyRowIndex).setValue(values_array[i]);
      };
    }//end of innter for loop
  }// end of outer for loop
}


function setUp() {
  ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
}

function getLastColumnRow(RowNumber) {
  var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('active'));
  var sheet = ss.getSheetByName("Sheet1");

  var data = sheet.getDataRange().getValues();
  var numRows = data.length;

  // loop from bottom to top for last row in given row "RowNumber"
  while( data[numRows - 1][RowNumber] == "" && numRows > 0 ) {
    numRows--;
  }
  return numRows
}

Robin Gertenbach · Accepted Answer · 2015-10-19 21:30:46Z

1

You are doing a lot of unnecessary and confusing steps in this script.

Declaring intermediate variables that are only used once
Misnaming of variables (RowNumber in getLastColumnRow is actually the column)
Doing a partial string search for a full match
Setting numbers to be appended individually instead of at once which is slower

This is why I chose not to try to modify your function, I hope you understand. The following function will do what you need

It could be optimized with a binary search for the last row if there are a lot of rows or just use SpreadsheetApp.getActiveSheet().getLastRow() if the active sheet if this is the only range but it should be fast enough for most applications.

function appender(){
  var values_array = SpreadsheetApp.getActiveSheet().getRange('A1:A10').getValues();
  var data = SpreadsheetApp.getActiveSheet().getRange('A12:A').getValues();

  var toAdd = [];
  for(var value in values_array){
    for(var i = 0; i < data.length; ++i){
      if(values_array[value][0] == data[i][0]){break;}
      if(data[i][0] == ""){
        toAdd.push(values_array[value]);
        break;
      }
    }
  }

  if(toAdd.length > 0){
    for(i = 0; i < data.length; ++i){
      if(data[i][0] == ""){break;}
    }
    SpreadsheetApp.getActiveSheet().getRange(12 + i, 1, toAdd.length, 1).setValues(toAdd);
  }
}

edited Oct 19, 2015 at 21:30

answered Oct 19, 2015 at 21:23

Robin Gertenbach

10.9k3 gold badges30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1788736 Over a year ago

Many thanks. I am fetching some data from an api so the rows increase . Do you think your method of avoiding duplicate data will be fast and efficient in number of rows increase by 10 each time? Does your code compare for full match ?Finally could you explain this part data[i][0]=" " ? then you add the value to array and at end you insert the full array to end of column.

Collectives™ on Stack Overflow

How to search for values of an array in a Google Sheet column and insert array value if not found?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related