Can you help me create a Google Apps Script that will return values from a database to an on-sheet form?

user_17305821 0
Viewed 62

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]))
}
userform google-apps-script google-sheets arrays
2 Answers
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])
    });
  }
}
user_111736540

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]);
  }
}
user_121605560
Related