My issue is returning the values saved in a database to the form in each cell in the range. The saveToDB script below is able to save the values from the form to the database, but I don't know how to retrieve those values. Hope you could help me. Thanks so much!
Form: sample form Database: database sample
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]))
}
CodePudding user response:
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])
});
}
}
CodePudding user response:
All you have to do is use the setValue() function and iterate through each cell like so:
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]);
}
}