Google Apps脚本将两个表单输入发送到同一行的两个不同列

huangapple go评论65阅读模式
英文:

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

问题

你好,实际上我是一个新手使用这个应用程序脚本,尝试构建一些可以使用电子表格完成的东西。问题是,当我提交新数据到第二个表单时,输出位于新数据下方。

Google Apps脚本将两个表单输入发送到同一行的两个不同列

这里是代码:
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;
}

我尝试了很多选项,但仍然无法解决问题,行仍然遵循processformlastrow

Google Apps脚本将两个表单输入发送到同一行的两个不同列

请参考图像,我正在尝试实现的目标。

英文:

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.,

Google Apps脚本将两个表单输入发送到同一行的两个不同列

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.

Google Apps脚本将两个表单输入发送到同一行的两个不同列

refer the image what im trying to achieve

答案1

得分: 0

如何修改您的脚本,使用此示例脚本

修改后的脚本:

// 参考: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; // 列“B”的最后一行。
  // sheet.insertRowAfter(row); // 如果您想使用此行,请启用它。
  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; // 列“G”的最后一行。
  // sheet.insertRowAfter(row); // 如果您想使用此行,请启用它。
  sheet.getRange(row, 7, 1, 3).setValues([[input4, input5, input6]];
  return true;
}

通过这种修改,在processForm中,值将放在“B”列的最后一行的下一行。在processForm1中,值将放在“G”列的最后一行的下一行。

参考:

英文:

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;
}
  • By this modification, at processForm, the values are put to the next row of the last row of column "B". At processForm1, the values are put to the next row of the last row of column "G".

Reference:

huangapple
  • 本文由 发表于 2023年2月19日 12:24:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/75497977.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定