'I couldn't update user form with data from a spreadsheet when I do search
I can't seem to populate user form when I do search. Search field can populate the data from the spreadsheet but the rest of the fields remain blank. I am not very good with coding and had to watch a tutorial to do this.
const ss = SpreadsheetApp.getActiveSpreadsheet()
const formWS = ss.getSheetByName("User Form")
const settingsWS = ss.getSheetByName("Settings")
const databaseWS = ss.getSheetByName("Database")
const idCell = formWS.getRange("C3")
const searchCell = formWS.getRange("C4")
const fieldRange = ["C6", "C7","C8","C9","C10","C11","C12","C13","C14",
"C15","C16","C17","C18","C19","C20","C21","C22",
"C23","C24","C25","C26","C27","C28","C29","C30","C31",
"C32","C33","C34","C35","C36","C37","C38"]
function saveRecord() {
const id = idCell.getValue()
if(id == ""){
createNewRecord()
return
}
const cellFound = databaseWS.getRange("A:A")
.createTextFinder(id)
.matchCase(true)
.matchEntireCell(true)
.findNext()
if(!cellfound) return
const row = cellFound.getRow()
const fieldValues = fieldRange.map(f => formWS.getRange(f).getValue())
fieldValues.unshift(id)
databaseWS.getRange(row,1,1,fieldValues.length).setValues([fieldValues])
searchCell.clearContent()
ss.toast("id:" + id, "Changes Saved!")
}
function createNewRecord(){
const fieldValues = fieldRange.map(f => formWS.getRange(f).getValue())
const nextIDCell = settingsWS.getRange("A2")
const nextID = nextIDCell.getValue()
fieldValues.unshift(nextID)
databaseWS.appendRow(fieldValues)
idCell.setValue(nextID)
nextIDCell.setValue(nextID+1)
ss.toast("id:" + nextID, "New Record Created")
}
function newRecord(){
fieldValues = fieldRange.forEach(f => formWS.getRange(f).clearContent())
idCell.clearContent()
searchCell.clearContent()
}
function deleteRecord (){
const id = idCell.getValue()
if(id == ""){
return
}
const cellfound = databaseWS.getRange("A:A")
.createTextFinder(id)
.matchCase(true)
.matchEntireCell(true)
.findNext()
if(!cellfound) return
const row = cellFound.getRow()
databaseWS.deleteRow(row)
newRecord()
ss.toast("id:" + id, "Record Deleted!")
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
