Exception: The number of rows in the range must be at least 1-CodePudding

I'm trying to run this code that will get the first 7 columns from sheet1 in spreadsheet1 and paste them in sheet2 in spreadsheet2, but im keep getting this error which I dont know why.

Exception: The number of rows in the range must be at least 1. 6th line

function MoveCode(){
  const ss1 = SpreadsheetApp.getActive();
  const ss2 = SpreadsheetApp.openById("1zU__ccPIMst54whmyrbmRnDRRjOtQBFPzXhw6NsFqpU");//or url whatever
  const ssh = ss1.getSheetByName('Sheet1');
  const dsh = ss2.getSheetByName('Sourcing');
  const vs = ssh.getRange(2,1,ssh.getLastRow() - 1,7).getValues();
  dsh.getRange(2,1,vs.length,vs[0].length).setValues(vs);
}

test3 is the spreadsheet that i want to paste the data to from.

If you need anymore explanation please let me know

CodePudding user response：

I think that in your script when ssh.getLastRow() is less than 2, such an error occurs. Because ssh.getLastRow() - 1 is 0 and -1. For example, I think that when the sheet "Sheet1" has only the header row and no values, such an error occurs. In order to remove this issue, how about the following modification?

Modified script:

function MoveCode(){
  const ss1 = SpreadsheetApp.getActive();
  const ss2 = SpreadsheetApp.openById("###");//or url whatever
  const ssh = ss1.getSheetByName('Sheet1');
  const dsh = ss2.getSheetByName('Sourcing');
  const lastRow = ssh.getLastRow();
  if (lastRow < 2) return;
  const vs = ssh.getRange(2, 1, lastRow - 1, 7).getValues();
  dsh.getRange(2, 1, vs.length, vs[0].length).setValues(vs);
}

By this modification, when the sheet "Sheet1" has only the header row and no values, the script is finished.

Added:

From your replying and adding the sample images, when I saw your images, I thought that in your script, ssh and dsh are used as the source sheet and destination sheet, respectively. But in your sample image, I thought that you might want to use ssh and dsh as the destination sheet and the source sheet, respectively. If my understanding is correct, how about the following sample script?

Modified script:

In this case, please be careful about SpreadsheetApp.getActive() and SpreadsheetApp.openById("###") for each sheet.

function MoveCode(){
  const ss1 = SpreadsheetApp.getActive();
  const ss2 = SpreadsheetApp.openById("###");//or url whatever
  const ssh = ss1.getSheetByName('Sheet1');
  const dsh = ss2.getSheetByName('Sourcing');
  const lastRow = dsh.getLastRow();
  if (lastRow < 2) return;
  const vs = dsh.getRange(2, 1, lastRow - 1, 7).getValues();
  ssh.getRange(2, 1, vs.length, vs[0].length).setValues(vs);
}