你能帮我创建一个谷歌应用程序脚本，将数据库中的值返回到表单上吗

Question

我的问题是将保存在数据库中的值返回到范围中每个单元格的表单中。下面的saveToDB脚本可以将表单中的值保存到数据库中，但我不知道如何检索这些值。希望你能帮助我。非常感谢！

表格：样本表格数据库：数据库样本

function saveToDB(){
range = ["C2","C4","C6"]
var newRange = range.map(f => formSheet.getRange(f).getValue())
dbSheet.appendRow(newRange)
}
function loadToForm(){
range = ["C2","C4","C6"]
var dbArray = dbSheet.getRange(2,1,dbSheet.getLastRow(),dbSheet.getLastColumn()).getValues()
var newArray = dbArray.filter(function(row){
if(row[0] === "Fred" && row[0] !== -1){
return row !== ""
}
})
//Don't know how to return each value to each cell in the range
//Update - this is the code that did it
range.map((f,i) => formSheet.getRange(f).setValue(newArray[0][i]))
}

Answer 1

function saveToDB() {
const ss = SpreadsheetApp.getActive();
const fsh = ss.getSheetByName('Form Sheet Name');
const dsh = ss.getSheetByName('Database Sheet Name')
let arr = ["C2", "C4", "C6"].map(e => fsh.getRange(e).getValue());
dsh.appendRow(arr);
}
function loadToForm() {
const range = ["C2", "C4", "C6"];
const ss = SpreadsheetApp.getActive();
const dsh = ss.getSheetByName('Database Sheet Name');
const fsh = ss.getSheetByName('Form Sheet Name');
let r = SpreadsheetApp.getUi().prompt('Row Number','Enter Row Number', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
if(r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
let vs = dsh.getRange(row, 1, 1, dsh.getLastColumn()).getValues()[0];
range.forEach((e,i) => { fsh.getRange(e).setValue(vs[i])
});
}
}

Answer 2

您所要做的就是使用setValue((函数并迭代每个单元格，如下所示：

function loadToForm(){
range = ["C2","C4","C6"]
var dbArray = dbSheet.getRange(2,1,dbSheet.getLastRow(),dbSheet.getLastColumn()).getValues()
var newArray = dbArray.filter(function(row){
if(row[0] === "Fred" && row[0] !== -1){
return row !== ""
}
})
//Remove useless extra dimension from array
newArray = newArray[0];
//Solution 1 using RangeList
var ranges = formSheet.getRangeList(range).getRanges();
for(var i in newArray){
ranges[i].setValue(newArray[i]);
}
//Solution 2 not using RangeList
for(var i in newArray){
formSheet.getRange(range[i]).setValue(newArray[i]);
}
}