CODEWITHSUNDEEP
google-apps-script
Michael
Michael

Reputation: 121

How do I copy a row of data to a new sheet with a different number of columns?

I know how to copy data from one sheet to another. I'm currently trying to do it for a new work project and I'm stumped.

I keep getting an error of 'The coordinates of the target range are outside the dimensions of the sheet'

The same columns exist between all 4 sheets that I'm working with, BUT some are hidden. Is this why I am getting the message? That's the only thing I can think of. If so, how do I get around that?

Code:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var RequestSheet = ss.getSheetByName('Request');
  var InProgressSheet = ss.getSheetByName('In Progress');
  var ReviewSheet = ss.getSheetByName('Ready for Review');
  var CompletedSheet = ss.getSheetByName('Completed');
  var HoldSheet = ss.getSheetByName('On Hold');

  var status = RequestSheet.getActiveCell().getValue();
  var row = RequestSheet.getActiveCell().getRow();
  var column = RequestSheet.getActiveCell().getColumn();

  var a1notation = ("A" + row + ":" + "X" + row);
  var newRange = RequestSheet.getRange(a1notation);

  if(status == 'In Progress') {
     newRange.copyTo(InProgressSheet.getRange(InProgressSheet.getLastRow() 
 + 1, 1))
  } else if (status == 'On Hold') {
      Browser.msgBox('On Hold was selected')
  } else if (status == 'Completed') {
      Browser.msgBox('Completed was selected')
  } else
      return
}

Upvotes: 0

Views: 43

Answers (2)

Michael
Michael

Reputation: 121

Here's what worked for me. The sheets that I was copying data to had certain cells with list items built-in to them. Shown here:

screen shot for clarification

So when 

newRange.copyTo(InProgressSheet.getRange(InProgressSheet.getLastRow() 
 + 1, 1))

was called, getLastRow() + 1, 1 was out of bounds because all of the rows technically had data in them. I cleared all of those out and it worked like a charm.

Upvotes: 1

Cooper
Cooper

Reputation: 64100

Something like this might work for you:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var RequestSheet = ss.getSheetByName('Request');
  var InProgressSheet = ss.getSheetByName('In Progress');
  var ipslr=InProgressSheet.getLastRow();//modified
  var ReviewSheet = ss.getSheetByName('Ready for Review');

  var CompletedSheet = ss.getSheetByName('Completed');
  var HoldSheet = ss.getSheetByName('On Hold');

  var status = RequestSheet.getActiveCell().getValue();
  var row = RequestSheet.getActiveCell().getRow();
  var column = RequestSheet.getActiveCell().getColumn();
  var newRange = RequestSheet.getRange(row,1,1,24);//modified
  if(ipslr==InProgressSheet.getMaxRows()) {
    InProgressSheet.insertRowsAfter(ipslr,100);//modified
  }
  if(status == 'In Progress') {
     newRange.copyTo(InProgressSheet.getRange(InProgressSheet.getLastRow() 
 + 1, 1))
  } else if (status == 'On Hold') {
      Browser.msgBox('On Hold was selected')
  } else if (status == 'Completed') {
      Browser.msgBox('Completed was selected')
  } else {
     return
  }
}

Upvotes: 0

Related Questions