getRange takes only one value - Google Sheets-CodePudding

I want to copy the range a21:ay21 to a new sheet but the formula only copies the first value (a21).

I'm stuck with it. Thanks!

function submitToDataBase() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("form"); //Form Sheet
  
  var datasheet = ss.getSheetByName("Data_Base"); //Data Sheet
  
  //Input Values
  var values = [[formSS.getRange("form!a21:ay21").getValues()]];
         
  datasheet.getRange(datasheet.getLastRow() 1, 1, 1, 50).setValues(values);

}

CodePudding user response：

I think that in your script, values is a 4-dimensional array. And, a21:ay21 has 51 columns. If you want to use getValues and setValues, how about the following modification?

From:

var values = [[formSS.getRange("form!a21:ay21").getValues()]];
       
datasheet.getRange(datasheet.getLastRow() 1, 1, 1, 50).setValues(values);

To:

var values = formSS.getRange("form!a21:ay21").getValues(); // formSS.getRange("a21:ay21").getValues();
datasheet.getRange(datasheet.getLastRow()   1, 1, 1, 51).setValues(values);

As another method, I think that in your situation, you can use appendRow as follows.

  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("form"); //Form Sheet
  var datasheet = ss.getSheetByName("Data_Base"); //Data Sheet
  var values = formSS.getRange("form!a21:ay21").getValues(); // formSS.getRange("a21:ay21").getValues();
  datasheet.appendRow(values[0]);

As another method, I think that in your situation, you can use copyTo of Class Range as follows.

  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("form"); //Form Sheet
  var datasheet = ss.getSheetByName("Data_Base"); //Data Sheet
  formSS.getRange("a21:ay21").copyTo(datasheet.getRange(datasheet.getLastRow()   1, 1), { contentsOnly: true });

References:

CodePudding user response：

Is this a named range?

var values = [[formSS.getRange("form!a21:ay21").getValues()]];

I would try deleting form from "form!a21:ay21", or simply using the named range writing the following in one of two ways

var values = [[formSS.getRange("form").getValues()]];

var values = [[formSS.getRange("!a21:ay21").getValues()]];