英文:
Google Apps Scripts send 2 form input to 2 difference column but in same Row
问题
你好,实际上我是一个新手使用这个应用程序脚本,尝试构建一些可以使用电子表格完成的东西。问题是,当我提交新数据到第二个表单时,输出位于新数据下方。

这里是代码:
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;
}
我尝试了很多选项,但仍然无法解决问题,行仍然遵循processform的lastrow。

请参考图像,我正在尝试实现的目标。
英文:
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.,

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.

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". AtprocessForm1, the values are put to the next row of the last row of column "G".
Reference:
- Related thread.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论