Search code examples
google-sheetsgoogle-apps-scriptspreadsheet

google apps script keeps throwing "(function name) is not a function" errors

Currently working through a project, and I'm trying to figure out how to write a script that would allow me to take the values from a form and paste them elsewhere on a sheet. Additionally, I attempted to make something to clear the cells afterward so that the form can be used again.

Essentially, the sheet has this form that the user fills out to calculate the cost of the project in the in-game currency. The form itself is already fully coded the way I want, I just need to get the "submit dragon" button working. For reference, this is what the form looks like filled out: (https://i.sstatic.net/DUNYO.png)

And this is what I want the input to look like after the script: (https://i.sstatic.net/YP7eO.png)

However, now that I've got the script mostly written, I keep getting "is not a function" errors for just about everything. I've checked other examples of this and they all say that it could be some kind of syntax error, but I've changed my code several times over to proposed "fixes" and the issue is persisting.

I tried to write a script based on a few other examples that I found both for the clear function, and for the copy-paste one.

This is the full code I have as-is: Note that I was in the middle of messing with the clearBox function, so it only has one of the named ranges in it that I made for that attempt (essentially they're just called clear1 through clear8). I also tried a version of the clear function that specifically targeted the cells I wanted in getRange functions, but those threw a different error about not being able to detect the range. For reference I want the button to clear JUST the content of every box in the form except the "Price" boxes. Those pull the value from another sheet based on the values next to it, and clearing those boxes just destroys the formula.

(Specific ranges to clear are B13:F13, H13:I13, B15:C15, E15:F15, H15:15, B17:D17:, and F17:J17. everything else should remain untouched.)

/**
 * @OnlyCurrentDoc
 */

function clearBox() {
  var ss = SpreadsheetApp.getActive();
  var ranges = ss.getRangeByName('clear1');
  ranges.forEach(range => range.getRange().clearContent());
}


function submitDerg() {
  const ss = SpreadsheetApp.getActive();
  const formSheet = ss.getActiveSheet();
  if (formSheet.getValue("F13") = 'price gem') {
    var breedData = concat(E13 & ' - ' & G13 & 'g')
  } else if (formSheet.getValue("F13") = 'price treasure') {
    var breedData = concat(E13 & ' - ' & G13 & 't')
  } else {
    var breedData = concat(E13 & ' - purchased')
  }
  if (formSheet.getValue("I13") = 'price gem') {
    var eyeData = concat(H13 & ' - ' & J13 & 'g')
  } else if (formSheet.getValue("I13") = 'price treasure') {
    var eyeData = concat(H13 & ' - ' & J13 & 't')
  } else {
    var eyeData = concat(H13 & ' - purchased')
  }
  if (formSheet.getValue("C15") = 'price gem') {
    var primData = concat(C15 & ' - ' & D15 & 'g')
  } else if (formSheet.getValue("C15") = 'price treasure') {
    var primData = concat(C15 & ' - ' & D15 & 't')
  }
  else {
    var primData = concat(C15 & ' - purchased')
  }
  if (formSheet.getValue("F15") = 'price gem') {
    var secData = concat(E15 & ' - ' & G15 & 'g')
  } else if (formSheet.getValue("F15") = 'price treasure') {
    var secData = concat(E15 & ' - ' & G15 & 't')
  } else {
    var secData = concat(E15 & ' - purchased')
  }
  if (formSheet.getValue("I15") = 'price gem') {
    var tertData = concat(H15 & ' - ' & J15 & 'g')
  } else if (formSheet.getValue("I15") = 'price treasure') {
    var tertData = concat(H15 & ' - ' & J15 & 't')
  } else {
    var tertData = concat(H15 & ' - purchased')
  }
  if (formSheet.getValue("D17") = 'price gem') {
    var poseData = concat(C17 & ' - ' & E17 & 'g')
  } else if (formSheet.getValue("D17") = 'price treasure') {
    var poseData = concat(C17 & ' - ' & E17 & 't')
  } else {
    var poseData = concat(C17 & ' - purchased')
  }
  var values = [[breedData,
    eyeData,
    primData,
    secData,
    tertData,
    poseData,
    formSheet.getRange("L14").getValue(),
    formSheet.getRange("L16").getValue(),
    formSheet.getRange("F17:J17").getValues()]];
  formSheet.getRange(formSheet.getLastRow() + 1, 1, 1, 15).setValues(values);
  ClearBox();
}

I'm sure there's a possibility of there being other issues in my code (and if you notice any I'd love if you could point them out), but the main one I'm having is the constant "x is not a function" errors from pretty much everything I've written. I'm unaware of any glaring issues past that as, due to the errors I'm facing, 1. I don't actually know if the code does what I want when it works, and 2. it allows me to save the project on apps script so that means that the syntax is generally correct.

For anyone who wants to take a look and troubleshoot, I've left the table with the example fill so you can make a copy without having to do anything to the form. https://docs.google.com/spreadsheets/d/1j0BOhXIysJhtuVvAmFjlj0jEhGpWaSTPD7ctmwN8fFM/edit?usp=sharing

Sorry for how long this is! The code got a bit long with all the if/else statements and it's a little hard to explain exactly what I'm trying to do. Thanks for looking!

Solution 
  • formSheet.getValue("F13")

    Sheet class doesn't have a getValue property. So,

    formSheet.getValue

    is undefined. And undefined is not a function. So, you cannot call it.

    formSheet.getValue()

    is the same as undefined(). That's the reason for your error. Did you mean getRange() on the sheet class?

    formSheet.getRange("F13")

    If so, you also need to get value from that range:

    formSheet.getRange("F13").getValue()

    Also, the operator used for comparison is ===, and not =, which is used for assignment.