Home > Software engineering >  Trying setValue from an array of variables to multiple cells in Google Sheets
Trying setValue from an array of variables to multiple cells in Google Sheets

Time:10-28

I'm trying to get multiple values from different cells in a sheet in Google Sheets and add in another sheet, but I couldn't figure out how to do it with setValue and getValue functions.

function Formulario() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var formCadastro = ss.getSheetByName("Cadastro2");
    var dataS = ss.getSheetByName("Base1");
    
      var cellNome = formCadastro.getRange("B4");
      var cellSobrenome = formCadastro.getRange("B5");

      var arrayCells = [
        cellNome,
        cellSobrenome
      ];
    
  dataS.getRange(dataS.getLastRow() 1,1,1,2).setValue(arrayCells.getValue());
}

And if I use, I don't get any value from the variables added to Base1, only a new row:

   dataS.getRange(dataS.getLastRow() 1,1,1,2).setValue(arrayCells);

Before I was using appendRow() and added .getValue() after the variables in the array, but I was getting problems with the other function I create to clean all the cells after saving, using for and getRange.

Was something like:

      var arrayCells = [
        cellNome.getValue(),
        cellSobrenome.getValue()
      ];

    dataS.appendRow(arrayCells);

And the function to clean the cells, but it doesn't work with variable.getValue():

    for ( var i = 0 ; i < arrayCells.length ; i   ) {
      arrayCells[i].clearContent();
    }

CodePudding user response:

Try this:

function Formulario() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Cadastro2");
  const vs1 = sh1.getRange("B4:B5").getValues();
  const sh2 = ss.getSheetByName("Base1");
  sh2.getRange(sh2.getLastRow()   1, 1, vs1.length, vs1[0].length).setValue(vs1);
}

CodePudding user response:

Try this

var cellNome = formCadastro.getRange("B4").getValue();
var cellSobrenome = formCadastro.getRange("B5").getValue();

var arrayCells = [
  cellNome,
  cellSobrenome
];
    
dataS.getRange(dataS.getLastRow() 1,1,1,2).setValues([arrayCells]);
  • Related