Google Apps Script – If Last Column in table, then paste data with new row

My information is set in columns (A to E), info paste in 2 rows (A2:A3, B2:B3…). What I want to happen is when info set in last column E (E2:E3, E5:E6 and more), new info paste with a new row in column A (A5:A6, B5:B6).

My info is in the spreadsheet:

This code paste info doesn’t work correctly:

function submitData1() {
  
    
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); //Form Sheet
  var datasheet = ss.getSheetByName("Job"); //Data Sheet
  
  //Input Values
  var values1 = [[formSS.getRange("B6").getValue()], [formSS.getRange("B7").getValue()]];
  var sr = 1
  var sc = 1
  var nr = 2
  var nc = 1

  for (var i=1;i<1;i++)
  var workingCell = datasheet.getRange(i,7);
  {
    if (workingCell ==null) 
    {datasheet.getRange(sr,datasheet.getLastColumn()+1,nr,nc).setValues(values1)
    } 
    else {datasheet.getRange(datasheet.getLastRow()+1, sc, nr, nc).setValues(values1)
    sc= sc+1}
      
  }
}

Answer

Here is a sample replication code:

function submitData1() {
  
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var datasheet = ss.getSheetByName("Job"); //Data Sheet
  
  //Input Values
  var values1 = [["Name X"], ["Job X"]];
  var lastRow = datasheet.getLastRow();
  var lastRow_ColCount = datasheet.getRange(lastRow,1,1,5).getValues().flat().filter(String).length;
  Logger.log(lastRow);
  Logger.log(lastRow_ColCount);

  if(lastRow_ColCount==5){
    //Add data on a new row, column A
    datasheet.getRange(lastRow+2, 1,2,1).setValues(values1);
  }
  else{
    //Add data on the next column in the same row
    datasheet.getRange(lastRow-1, lastRow_ColCount+1,2,1).setValues(values1);
  }

}

• I used a fixed value for the input data during this replication.
• Just replace the values from your original code that will be read from your form sheet
• You can also remove the logs in the script, I just used them to debug the code

What it does?

1. Get the last row of the sheet, It will determine the last row that has a valid data
2. Get the data/values of the last row from column A to column E. Use Array.flat() to change 2-d array to 1-d array. Use Array.filter() to get non empty cells then count the data available in the last row.
3. If the current data available is 5, then the new data will be added on the new row, else the new data will be added on the next column

Output: