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();

}

CodePudding user response：

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.