CODEWITHSUNDEEP
google-apps-script
Nino Dokoskiot
Nino Dokoskiot

Reputation: 27

Google App script - setValues() doesn't work

So, I'm trying to write a script using the onEdit() event, which will basically remove links that are duplicates (technically, it removes everything, and only puts back things which aren't duplicates).

My code works fine all the way until it's time to write back non-duplicates. Namely, the line in which I use range.setValues(). I understand that it needs an array of arrays of cells which to edit, and that said array needs to fit in the range.

So far, I have : 

      if (unique)
  {
    newData.push(editedRow[0]);
    Browser.msgBox(newData); 
  }

Unique is a variable I use that is false if an exact entry was found. With the msgBox command, I can verify that newData contains what it needs to contain. Further down, I have :

newDataFinal = [newData];
Browser.msgBox('Put values '+newDataFinal+' in range ' +range.getA1Notation());
range.setValues(newDataFinal);

To my knowledge, this should make NewDataFinal an array of arrays, which I can verify if I change setValues() to setValue(), which writes [[22.0, 13.0, 23.0]] (for my example) in the spreadsheet, which looks like an array of arrays to me.

The range should also match, since for this example, I get a prompt along the lines of "Put values 22,13,23 in range B2:B4" from the msgBox, which seems as a fitting range. So, what am I doing wrong?

Here's the rest of the code (please excuse the abundancy of comments/msgboxes and lack of elegancy, the priority is to get it to work, I can probably optimize it and clean it up a bunch afterwards) : 

function onEdit(e)
{
  var range = e.range;
  var values = range.getValues();                      
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() != 'testiranje') return;
  newData = new Array();
//  Browser.msgBox(range.getA1Notation()); 
  range.clear();
  var data = sheet.getDataRange().getValues();
  var counter = 0;
  for (editedRowIndex in values)
  {
    unique = true;
    editedRow = values[editedRowIndex];
//    Browser.msgBox('Edited Row ' +editedRow);
    for(i in data)
    {
      var row = data[i];
 //     Browser.msgBox('Old Row '+row);
      for (j in row)
      {
  //      Browser.msgBox(row[j] + ' vs ' + editedRow[0])
        if (editedRow[0] == row[j])
        {
          Browser.msgBox('Hit! '+editedRow[0]);
          unique = false; 
        }
      }
    }
      if (unique)
      {
     //   Browser.msgBox('Pushing '+editedRow[0]+' in newdata');
        newData.push(editedRow[0]);
        Browser.msgBox(newData);
      }
  }
  newDataFinal = [newData];
  Browser.msgBox('Put values '+newDataFinal+' in range ' +range.getA1Notation());
  range.setValues(newDataFinal);


 // range.setNote('SCIENCE');
 }

Upvotes: 1

Views: 3270

Answers (2)

Zig Mandel
Zig Mandel

Reputation: 19835

the problem is that you cant change cells from an onEdit handler. see the docs. instead install your own onEditCustom handler.

Upvotes: 0

Serge insas
Serge insas

Reputation: 46794

I didn't test your code because I didn't feel like creating a sheet for it but what I can suggest (that should solve this issue in any case) is to replace your range.setValues(newDataFinal); with this :

sheet.getRange(range.getRowIndex(),range.getColumnIndex(),newDataFinal.length,newDataFinal[0].length).setValues(newDataFinal);

And if you want to know why the range and array didn't fit you can use this code :

(I used Browser because you seem to like it... I prefer Logger.log)

Browser.msgBox('data height = '+newDataFinal.length+', data width = '+newDataFinal[0].length+' and range height is '+range.getHeight()+', range width is '+range.getWidth()+' ... does it fit ?');

Note : I'm almost sure that your initial range is bigger than the newData array since you remove elements from the initial data... My best guess would be that heights don't fit. (but that's only a guess ;-) since you didn't mention the error message you get...)

Upvotes: 1

Related Questions