0

I used my code for about a week (which was already very slow) but kept going. Today, while trying to run it, I get the message "ReferenceError: Function function getValues() {/* */} can not be used as the left-hand side of assignment or as an operand of ++ or -- operator."

Here's the code:

    function PreenchePlanilha()
    {
      var App = SpreadsheetApp;
      App.getActiveSpreadsheet().getSheetByName('MacroHelp').getRange(1,1).activate();
      var helpMacro = App.getActiveSpreadsheet().getActiveSheet();
      var i = 250;
      var j = 1;
      var k = 1;
      while (helpMacro.getRange(i,5).getValue() != "")
      {
      if(helpMacro.getRange(i,17).getValue() == "")
      {
        while (helpMacro.getRange(j,20).getValues().toString() != helpMacro.getRange(i,5).getValues().toString())
        {
          j = j+1;
        }

        var aba = helpMacro.getRange(j,21).getValue();
        var valores = helpMacro.getRange(i,6, 1, 11);
        var email = helpMacro.getRange(i,1).getValue();

        App.getActiveSpreadsheet().getSheetByName(aba).getRange(1,1).activate();
        var cols = contalinha();

        while (App.getActiveSpreadsheet().getActiveSheet().getRange(k,8).getValue() != email && k <= cols)
        {
          k = k + 1;
        } 

        App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).getValues() = helpMacro.getRange(i,6, 1, 11);
        App.getActiveSpreadsheet().getSheetByName("MacroHelp").getRange(i,17).activate();
        App.getActive().getCurrentCell().setValue('Feito');
        }
        i++;
        j = 1;
        k = 1;

      }



}


function contalinha() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  var cols = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange().getRow();
  return cols;
};

I tried replacing the line

App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).getValues() = helpMacro.getRange(i,6, 1, 11);

I tried both this codes, none working:

App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).setValues() = valores;
App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).setValues(valores);

What am I doing wrong?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Lucas Martinho Furtado
  • 1
  • 1
  • You're getting the values then setting a range over the top, then trying to set the values with a range... – ross Nov 01 '19 at 16:05
  • `App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).getValues() = helpMacro.getRange(i,6, 1, 11);` I would hazard that this is the line causing the problem. The first half of this(`App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).getValues()`) is a valid statement (though it is useless without declaring a variable to hold the values). The second half of this statement `helpMacro.getRange(i,6, 1, 11);` merely declares a range, and it is nonsensical to make `getValues()` equal to anything, let alone a range. – Tedinoz Nov 02 '19 at 09:53
  • Please share a copy of your spreadsheet. – Tedinoz Nov 02 '19 at 21:31
  • Regarding `App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).getValues() = helpMacro.getRange(i,6, 1, 11);` and the alternatives that you tried. Leaving aside syntax issues, would you please explain what outcome you are trying to achieve in this line of code. – Tedinoz Nov 02 '19 at 21:57

1 Answers1

0

I managed to get it done! I had to change this line

App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).getValues() = helpMacro.getRange(i,6, 1, 11)

and changed it into this:

App.getActiveSpreadsheet().getActiveSheet().getRange(k,31,1,11).setValues(helpMacro.getRange(i,6, 1, 11).getValues());

Thanks for the help!

Lucas Martinho Furtado
  • 1
  • 1