0

I'm running two scripts in Google Sheets. They are both intended to do the same thing, but in different columns (columnH/columnI). First one works just fine, second one returns this error

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

Could you please tell me how to fix it? Here are the scripts:

function SubmitData01() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Input"); 
  var datasheet = ss.getSheetByName("Trades");
  var freeRow;
  var columnH = datasheet.getRange("H2:H" + datasheet.getLastRow()).getDisplayValues().flat();
for(var i = 0; i < columnH.length; i++){
  if(columnH[i] == "") {
    freeRow = i + 1;
    break;
  }
}
  var values = [[formSS.getRange("I6").getValue()]];
  
  datasheet.getRange(freeRow, 8, 1, 1).setValues(values);
  
  formSS.getRange('C6:F6').clearContent();

}

function SubmitData02() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Input"); 
  var datasheet = ss.getSheetByName("Trades");
  var freeRow;
  var columnI = datasheet.getRange("I2:I" + datasheet.getLastRow()).getDisplayValues().flat();
for(var i = 0; i < columnI.length; i++){
  if(columnI[i] == "") {
    freeRow = i + 1;
    break;
  }
}
  var values = [[formSS.getRange("I6").getValue()]];
  
  datasheet.getRange(freeRow, 9, 1, 1).setValues(values);
  
  formSS.getRange('C6:F6').clearContent();

}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Lucy C
  • 111
  • 3
  • Please add a [mcve]. Tl;Dr. The code should be minimal, since it's reading data from a spreadsheet you should describe it's relevant parts, include some sample data and the expected result. – Rubén Oct 02 '22 at 00:13
  • Sorry, I am new to this. Next time I'll provide more information. Luckily, the answer below helped me big time. Have a nice day! – Lucy C Oct 02 '22 at 05:11

1 Answers1

1

From your showing error message of Exception: The parameters (null,number,number,number) don't match the method signature for SpreadsheetApp.Sheet.getRange., it is considered that in your script, the cells "I2:I" + datasheet.getLastRow() are not empty. By this, freeRow of datasheet.getRange(freeRow, 9, 1, 1).setValues(values); is null, and such an error occurs. I thought that this might be the reason for your issue.

About your question of Could you please tell me how to fix it?, if you want to avoid this error by modifying your showing script, how about the following modification?

Pattern 1:

From First one works just fine, in this pattern, when freeRow is null, the last row is put into freeRow. For this, please modify as follows.

From:

  var freeRow;
  var columnI = datasheet.getRange("I2:I" + datasheet.getLastRow()).getDisplayValues().flat();
for(var i = 0; i < columnI.length; i++){
  if(columnI[i] == "") {
    freeRow = i + 1;
    break;
  }
}

To:

var lastRow = datasheet.getLastRow();
var freeRow;
var columnI = datasheet.getRange("I2:I" + lastRow).getDisplayValues().flat();
for (var i = 0; i < columnI.length; i++) {
  if (columnI[i] == "") {
    freeRow = i + 1;
    break;
  }
}
freeRow = freeRow || lastRow;
  • Or, is your expected result freeRow = freeRow || lastRow + 1;?

Pattern 2:

In this pattern, when freeRow is null, the script is stopped. For this, please modify as follows.

From:

  var freeRow;
  var columnI = datasheet.getRange("I2:I" + datasheet.getLastRow()).getDisplayValues().flat();
for(var i = 0; i < columnI.length; i++){
  if(columnI[i] == "") {
    freeRow = i + 1;
    break;
  }
}

To:

var freeRow;
var columnI = datasheet.getRange("I2:I" + datasheet.getLastRow()).getDisplayValues().flat();
for (var i = 0; i < columnI.length; i++) {
  if (columnI[i] == "") {
    freeRow = i + 1;
    break;
  }
}
if (!freeRow) return;

Note:

  • From your question, unfortunately, I couldn't understand your expected situation. So, I proposed the above 2 patterns.

Added:

From your following reply,

My goal when running this script is that the contents of I6 from the first sheet get sent to the first empty cell in Column I in sheet 2. When I run the first script, to copy I6 from sheet 1 to the first empty cell in Column H (or any other column) in sheet 2 it works perfectly.

If you want to put the value to the 1st empty cell of the column "I", how about the following sample script? In this case, please modify SubmitData02() as follows.

Sample script:

From:

  var freeRow;
  var columnI = datasheet.getRange("I2:I" + datasheet.getLastRow()).getDisplayValues().flat();
for(var i = 0; i < columnI.length; i++){
  if(columnI[i] == "") {
    freeRow = i + 1;
    break;
  }
}

To:

// Ref: https://stackoverflow.com/a/44563639
Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
  const range = this.getRange(offsetRow, columnNumber, 2);
  const values = range.getDisplayValues();
  if (values[0][0] && values[1][0]) {
    return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
  } else if (values[0][0] && !values[1][0]) {
    return offsetRow + 1;
  }
  return offsetRow;
};
var freeRow = datasheet.get1stEmptyRowFromTop(9);
  • By this modification, freeRow is the 1st empty row.
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you so much for your answer and sorry for the late reply. My goal when running this script is that the contents of I6 from the first sheet get sent to the first empty cell in Column I in sheet 2. When I run the first script, to copy I6 from sheet 1 to the first empty cell in Column H (or any other column) in sheet 2 it works perfectly. – Lucy C Oct 02 '22 at 02:45
  • Neither Column H nor I are empty. I've tried other columns as well, and the script doesn't run into any problems. I'll try your suggestions as soon as I get home. – Lucy C Oct 02 '22 at 02:50
  • Just got home. First, I tried the second modification you suggested and I didn't get an error message but the cell content didn't get sent. Then I tried the first one and it did! Thank you so much and have a nice day. – Lucy C Oct 02 '22 at 05:09
  • Actually, I just noticed that it's replacing the contents of the last non-blank cell in the column instead of pasting in the one below. Any idea how to fix that? @Tanaike – Lucy C Oct 02 '22 at 05:44
  • @Lucy C Thank you for replying. Although I'm not sure whether I could correctly understand your reply, I added one more modification point. Could you please confirm it? If I misunderstood your reply, I apologize. – Tanaike Oct 02 '22 at 11:44
  • @Lucy C Or, for the pattern 1, when `freeRow = freeRow || lastRow;` is modified to `freeRow = freeRow + 1 || lastRow + 1;`, is that your expected result? – Tanaike Oct 02 '22 at 12:32
  • Hi! I'm not at home right now, but I'll check this out as soon as I get there and let you know. – Lucy C Oct 02 '22 at 15:51
  • Just got home! Pattern 1 with your last suggested modification did the trick :) You're a godsend. Thank you so much and have a nice day ^__^ – Lucy C Oct 02 '22 at 21:31
  • @Lucy C Thank you for replying. I'm glad your issue was resolved. Thank you, too. – Tanaike Oct 02 '22 at 22:50