I want to use the range "C3:F3" (a merged field) as a keyword to search for a record in an Array. And put the result in the following 3 cells "C5", "F5","C8:F8". I name this function as retrieveBookingRecord (). This function works just fine.
However, I want to make it like, whenever I change the value in "C3:F3", it will automatically run retrieveBookingRecord (). I am trying to do it with onEdit() but it doesnt work. I got this error on execution. And nothing happen when I change the value in "C3:F3"
Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets at unknown function
const ss = SpreadsheetApp.openById("XXXXXXXXXXXXXXXXXXX")
const mainForm = ss.getSheetByName("Main")
function onEdit(e) {
if(e.range.getA1Notation() !== "C3") return
if(e.source.getSheetName() !== "Main") return
retrieveBookingRecord()
}
function retrieveBookingRecord (){
const searchCell = mainForm.getRange("C3:F3")
const searchvalue = searchCell.getValue()
var lr = getLastRowSpecial(databaseForm, "A1:K");
const allBookingData = databaseForm.getRange(2,1,lr-1,11).getValues()
const recordsFound = allBookingData.filter(r => r[10] == searchvalue)
if(recordsFound.length === 0) return
const NeededrecordsFound = [[recordsFound[0][3],recordsFound[0][4],recordsFound[0][2]]]
const retrieveBookingRange = ["C5", "F5","C8:F8"]
retrieveBookingRange.forEach((f,i) =>mainForm.getRange(f).setValue(NeededrecordsFound[0][i]))
//const searchField = mainForm.getRange("C3:F3").getValue()
}