Google sheets group script error when only 1 line

Question

I have a script that copies the data from one sheet into another which acts as an archive. I have it so when it pastes the data into the archive sheet, it will group and collapse the data. However, when it is only 1 row I don't want it to group the data. This is causing an error:

"Exception. Invalid argument"

which is in reference to this line:

ts.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

I think a solution to this might be to incorporate an if array where if the range of data copied has 1 row do nothing, 2 rows will offset by 1 row then group then collapse, and 3 or more rows will run the same code I have below.

Here is the code that selects the data, groups, and then collapse it:

 ts.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
 ts.getCurrentCell().offset(1, 0).activate(); //offset by 1 row for the header
 var currentCell = ts.getCurrentCell();
 ts.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
 currentCell.activateAsCurrentCell();
 ts.getActiveRange().shiftRowGroupDepth(1).collapseGroups();

Full script:

function CopyRangeSoldTest() {
 var sss = SpreadsheetApp.openById('1vT4R-_xPx3Z7bOYWgpOp2JCypL2yODxcJ'); //replace with source ID
 var ss = sss.getSheetByName('Sold'); //replace with source Sheet tab name
 var range = ss.getDataRange(); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('1srDDle9R81Qlh'); //replace with destination ID
 var ts = tss.getSheetByName('Sold History'); //replace with destination Sheet tab name
  
 ts.getRange(ts.getLastRow()+2,1,ss.getLastRow(),5).setValues(data)
 ts.getRange(ts.getLastRow(),1,ts.getLastRow(),5).activate();
 ts.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
 ts.getCurrentCell().offset(1, 0).activate();
 var currentCell = ts.getCurrentCell();
 ts.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
 currentCell.activateAsCurrentCell();
 ts.getActiveRange().shiftRowGroupDepth(1).collapseGroups();//.shiftRowGroupDepth(1).collapseGroups();//you will need to define the size of the copied data see getRange() 
 
 var sss = SpreadsheetApp.openById('1vT4R-_xPx3Z7bOYWgpOp2JCypL2yODx'); //replace with source ID
 var ss = sss.getSheetByName('Received'); //replace with source Sheet tab name
 var range = ss.getDataRange(); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('1srDDle9R81Qlh'); //replace with destination ID
 var ts = tss.getSheetByName('Received History'); //replace with destination Sheet tab name
  
 ts.getRange(ts.getLastRow()+2,1,ss.getLastRow(),5).setValues(data)
 ts.getRange(ts.getLastRow(),1,ts.getLastRow(),5).activate();
 ts.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
 ts.getCurrentCell().offset(1, 0).activate();
 var currentCell = ts.getCurrentCell();
 ts.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
 currentCell.activateAsCurrentCell();
 ts.getActiveRange().shiftRowGroupDepth(1).collapseGroups();//.shiftRowGroupDepth(1).collapseGroups();//you will need to define the size of the copied data see getRange() 
 }

soMario soMario · Accepted Answer · 2020-11-09T11:56:12

Explanation:

I understand you recorded a Macro to generate this code. However, if you do some research on the official documentation you will be able to write your own code and achieve the same result in way less lines but also generalize it so it could work for multiple projects.

However, a direct solution to your problem might be the following:

You can get the values of the selection and check if the length of this array is larger than 1 or in other words, the selected data contains more than 1 rows:

var nrows = ts.getSelection().getActiveRange().getValues().length;

and then use an if condition to check whether the data contains more than 1 row: if(nrows>1).

Solution:

Try this:

 ts.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
 ts.getCurrentCell().offset(1, 0).activate(); //offset by 1 row for the header
 var currentCell = ts.getCurrentCell();
 var nrows = ts.getSelection().getActiveRange().getValues().length;
 if (nrows>1){
 ts.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
 currentCell.activateAsCurrentCell();
 ts.getActiveRange().shiftRowGroupDepth(1).collapseGroups();
 }

Google sheets group script error when only 1 line

1 Answers