google-apps-scriptgoogle-sheetssourceforge-appscript

Google Apps Scripts send 2 form input to 2 difference column but in same Row

Hello there actually im new using this Apps scripts and trying to build something that can be achieve with spreadsheet. The problem is when i submit new data to a second form the output is below the new data.,

1

here the code: Code.gs

function processForm(input1, input2, input3) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("B4:D4");
  range.setValues([[input1, input2, input3]]);
  var values = range.getValues();
  var lastRow = sheet.getLastRow();
  var nextRow = lastRow + 1;
  sheet.insertRowAfter(lastRow);
  sheet.getRange(nextRow, range.getColumn(), 1, 3).setValues([[input1, input2, input3]]);
  return true;
}
function processForm1(input4, input5, input6) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("G4:I4");
  range.setValues([[input4, input5, input6]]);
  var values = range.getValues();
  var lastRow = sheet.getLastRow();
  var nextRow = lastRow + 1;
  sheet.insertRowAfter(lastRow);
  sheet.getRange(nextRow, 7, 1, 3).setValues([[input4, input5, input6]]);
  return true;
}

i have try many option but still cant figure it out., the row is still follow the lastrow of processform.

2

refer the image what im trying to achieve

Solution

  • How about modifying your script using this sample script?

    Modified script:

    // Ref: https://stackoverflow.com/a/44563639
Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
  const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
  return search ? search.getRow() : offsetRow;
};

function processForm(input1, input2, input3) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.get1stNonEmptyRowFromBottom(2) + 1; // Last row of column "B".
  // sheet.insertRowAfter(row); // If you want to use this line, please enable this.
  sheet.getRange(row, 2, 1, 3).setValues([[input1, input2, input3]]);
  return true;
}

function processForm1(input4, input5, input6) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.get1stNonEmptyRowFromBottom(7) + 1; // Last row of column "G".
  // sheet.insertRowAfter(row); // If you want to use this line, please enable this.
  sheet.getRange(row, 7, 1, 3).setValues([[input4, input5, input6]]);
  return true;
}

    Reference: