Google Apps Script "SetFormula" is not a function-CodePudding

I am attempting to write a formula to give a particular cell a formla If it is blank when a button is pressed.

The below is what I have so far:

function CustomCoords() {
  var SS = SpreadsheetApp.getActive().getSheetByName('FORM');
  var Lat = ('C20');  
  var Long = ('C21');
  var LatBlank = SS.getRange(Lat).isBlank()
  var LongBlank = SS.getRange(Long).isBlank()

  if (LatBlank == "1"){
    Lat.SetFormula("=IF(C9>500000,(LEFT(C9,2)*10000) (MID(C9,4,2)*100) MID(C9,7,4)")
  } else {
    Lat.Setvalues("")
  }

  if (LongBlank == "1"){
    SS.getRange(Lat).SetValues("=IF(C9>500000,(LEFT($C$9,2)*10000) (MID($C$9,4,2)*100) MID($C$9,7,4)")
  } else {
    SS.getRange(Lat).SetValues("")
  }
}

I have referenced a couple of forums to put the script together and as far as I am aware it should work, however I keep receiving the error in the title. That said, I am still a beginner when it comes to such things so I could easily have missed something!

Any help is greatly appreciated

CodePudding user response：

You've so many syntax error in your code:-

var Lat = ('C20'); is a string and not a range and you need range to use .setValues("").
It's .setValues not .Setvalues, uppercase-lowercase matters, same with SetFormula it's lowercase s.

There are many more errors, I would suggest you to try this sample script and learn the differences.

function CustomCoords() {
  var SS = SpreadsheetApp.getActive().getSheetByName('FORM');
  var Lat = SS.getRange('C20');  
  var Long = SS.getRange('C21');
  var LatBlank = Lat.isBlank()
  var LongBlank = Long.isBlank()
  if (LatBlank){
    Lat.setFormula("=IF(C9>500000,(LEFT(C9,2)*10000) (MID(C9,4,2)*100) MID(C9,7,4)") 
  } else {
    Lat.setValue("") // not setValues becasue you're updating single cell(C20)
  }

  if (LongBlank){
    Lat.setValue("=IF(C9>500000,(LEFT($C$9,2)*10000) (MID($C$9,4,2)*100) MID($C$9,7,4)")
  } else {
    Long.setValue("")
  }
}

Reference:-

setValue

setFormula