英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论