CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Omar
Omar

Reputation: 181

Exception: The parameters (String,number,String) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange

Im trying to copy the first 7 columns from spreadsheet1(369) and spreadsheet2(370) and paste them in spreadsheet3 with the number 369 and 370 next to the data, but im keep getting this error which I don't know why.

function MoveCode(){
  wn1=369;
  wn2=370;
  wn3=371;
  wn4=372;
  const ssActive = SpreadsheetApp.getActive();
  const sshActive = ssActive.getSheetByName('Sheet1');

  const ss369 = SpreadsheetApp.openById("1A3tJiIEkDP_5R6mEY25IeAsO2XV_oveJwnUovxRd-1U");//or url whatever
  const dsh369 = ss369.getSheetByName('Sourcing');
  const lastRow369 = dsh369.getLastRow();//getting the last row
  //Adding the week number to the first row
  sshActive.getRange("A2").setValue("Week Number")
  var number369 = (dsh369.getLastRow()-2).toString();
  number369 = number369.replace(".0","");
  sshActive.getRange(3,1,number369).setValue(wn1);
  Logger.log(number369);
  
  const vs369 = dsh369.getRange(2, 1, lastRow369 - 1, 7).getValues();//we are getting the data and storing it in vs
  sshActive.getRange(2, 2, vs369.length, vs369[0].length).setValues(vs369);//we are pasting the code to the new sheet from dsh
  
  
  const ssActive370 = SpreadsheetApp.getActive();
  const sshActive370 = ssActive370.getSheetByName('Sheet1');

  const ss370 = SpreadsheetApp.openById("1HL1OH0ghlQCS7fMZsiVKI9ELab8HRVXW6QtruPiKedY");
  const dsh370 = ss370.getSheetByName('Sourcing');
  const lastRow370 = dsh370.getLastRow();//getting the last row

  var number370 = (dsh370.getLastRow()-2).toString();
  number370 = number370.replace(".0","");
Logger.log(number369);
  Logger.log(number370);
  ssActive370.getRange(number369+3,1,number370).setValue(wn2);
  Logger.log(number370);

  const vs370 = dsh370.getRange(2, 1, lastRow370 - 1, 7).getValues();//we are getting the data and storing it in vs
  ssActive370.getRange(number369+3, 2, vs370.length, vs370[0].length).setValues(vs370);//we are pasting the code to the new sheet from dsh
  
}

I,m not relly sure why its giving me that error, because im using (number,number,number) I believe.

The error is on line 34

If you need more info please let me know

Upvotes: 0

Views: 1563

Answers (2)

Tanaike
Tanaike

Reputation: 201613

Issue and solution:

I thought that in your script, such the error occurs at ssActive370.getRange(number369+3,1,number370).setValue(wn2); and ssActive370.getRange(number369+3, 2, vs370.length, vs370[0].length).setValues(vs370);. Because ssActive370 is the object of Class Spreadsheet from const ssActive370 = SpreadsheetApp.getActive();. The method of "getRange" in Class Spreadsheet is only getRange(a1Notation). I thought that this is the reason of your issue of Exception: The parameters (String,number,String) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange. In order to use getRange(row, column, numRows) and getRange(row, column, numRows, numColumns), it is required to use the method of Class Sheet.

When you want to remove this issue, how about the following modification?

Modified script:

From:

const ssActive370 = SpreadsheetApp.getActive();
const sshActive370 = ssActive370.getSheetByName('Sheet1');

To:

const ssActive370 = SpreadsheetApp.getActive().getSheetByName('Sheet1');

References:

Upvotes: 1

Cooper
Cooper

Reputation: 64100

Try:

ssActive370.getRange(parseInt(number369+3),1,parseInt(number370)).setValue(wn2);

Try doing the copy this way:

function MoveCode() {
  const ss369 = SpreadsheetApp.openById("1A3tJiIEkDP_5R6mEY25IeAsO2XV_oveJwnUovxRd-1U");
  const ss370 = SpreadsheetApp.openById("1HL1OH0ghlQCS7fMZsiVKI9ELab8HRVXW6QtruPiKedY");
  const ssActive = SpreadsheetApp.getActive();

  const ssh = ssActive.getSheetByName('Sheet1');
  const dsh369 = ss369.getSheetByName('Sourcing');
  const dsh370 = ss370.getSheetByName('Sourcing');
 
  const vs369 = dsh369.getRange(2, 1, dsh369.getLastRow() - 1, 7).getValues();
  const vs370 = dsh370.getRange(2, 1, dsh370.getLastRow() - 1, 7).getValues();

  ssh.getRange(2, 2, vs369.length, vs369[0].length).setValues(vs369);
  //SpreadsheetApp.flush();//you might want this 
  ssh.getRange(ssh.getLastRow() + 1, 2, vs370.length, vs370[0].length).setValues(vs370);//you may have to reposition the starting row here
}

I left a couple of things out like putting the "Week Number" header in column A and something you were doing with the wns at the top which seemed unnecessary. But the basic copying should be relative close to what you want.

Upvotes: 2

Related Questions