Fail to execute onEdit script on google spreadsheet

Viewed 16

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()



}
0 Answers
Related