0

I wrote a script that compares rows between two sheets and deletes all matching rows on the second sheet (named 'temp'). I set the loop to start at the end of temp and decrement, working toward the top. The script works but it ignores the bottom two rows on 'temp'...how can I fix this? I want to ensure it will delete the bottom two rows on temp when they match the data set from the other sheet.

I have confirmed that the bottom two rows are in fact duplicates and should be caught by the script and deleted.

Script:

function trimTempSheet() {

  var ss, s, s1, dt;
  var dirname='X DIR'
  var fs, f, fls, fl, name;
  var ncols=1,i, newRows, rw;

  ss=SpreadsheetApp.getActiveSpreadsheet();
  s=ss.getSheetByName('Report Results');

  name = 'temp';

  //Load current sheet to compare  
  var currentDataSet = s.getRange("A:S").getValues(); //Ignore final columns
  var newSheet = ss.getSheetByName(name);

 //Load imported data to compare
  var newData = newSheet.getRange("A:S").getValues();
  var headers = newData.shift();

//Create empty array to store data to be written [to add later]
    newRows=[];

//Compare data from newData with current data
  for(var i = newData.length-1; i > 0; --i) 
       { 

for(var j in currentDataSet)
  {
         if(newData[i].join() == currentDataSet[j].join()  )
    {
        newSheet.deleteRow(i);
     }
  }
          }
SancheyV
  • 1
  • If `currentDataSet` is an array, you should use a normal `for` loop, rather than `for..in`. [See this question and its answers](https://stackoverflow.com/q/500504/215552) for more. – Heretic Monkey Jan 22 '18 at 20:34
  • I switched the inner loop to: for(var j = 0; j < currentDataSet.length; j++), but it still ignores the bottom two rows of 'temp'. – SancheyV Jan 22 '18 at 21:38

2 Answers2

0

How about this modification?

From :

for(var i = newData.length-1; i > 0; --i)

To :

for(var i = newData.length-1; i >= 0; --i)

Note :

  • About "the bottom two rows on 'temp'",
    • By above modification, one loop is added to the for loop.
    • In your script, the first element of newData is removed by var headers = newData.shift();. By this, newData decreases one element.
      • For example, how about also removing this line?

If this modification was not useful for you, I'm sorry. At that time, can you show us your sample spreadsheet?

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • When I remove the header shift, my script now deletes duplicates except in the final (bottom) row of the sheet. So thank you, I am halfway to a solution. However, the script is still ignoring the bottom row--even when I change i to greater than or equal to zero. I tried changing to "newData.length", without subtracting one, but that produces an error "cannot call method 'join' of undefined"....why does length-1 work but length is undefined here for this array? – SancheyV Jan 23 '18 at 14:51
  • @SancheyV I'm sorry. I couldn't understand your current situation. If your problem was solved, I'm glad. If your problem was not solved, can you show us your sample spreadsheet and your current script? – Tanaike Jan 23 '18 at 22:10
0

I do not know the optimal way to force the script to look at and delete the last row first, without skipping it. So I changed the delete line to "newSheet.deleteRow(i+1);"

Will this produce any unintended row deletes based on my iteration loops? I am not an expert at how google scripts handles arrays. I assume the loops will examine rows in newData (the sheet named 'temp) sequentially, from the last row to the first. In which case my solution would be acceptable. But I am not certain of this.

SancheyV
  • 1